Getting info from MS Access

This is a note to keep myself clear on what technology is preferred and when to use it. I understand why programmers use explitives and the F-bomb- MS makes things SOOOO complicated (uninstall and reinstall the 2010 distributable – no matter how ‘installed’ you think it is 🙂 ). Basically Microsoft Access has flip flopped on whether or not ADO or DAO is the preferred choice – here is a history article for interest sake. I used this article to make my choice and they mention lots of other options and details that are good to know. The project is to get MS Access records into AutoCAD tables. It is important to note that ADO and ADO.net are not the same- except the 3 letters. ACEDAO is the latest ADO and DAO is preferred after MS Access 2007 and is re-written from this version onwards. What references to set? I had troubles because my Excel test envuronmnent gave error 429: could not created an active x component. Here is what references worked Continue reading

Report Services & SQL Server install and set up for use with Bentley AutoPLANT

I am using report services to publish my reports instead of using Crystal Reports for many Bentley AutoPLANT items. Here is my journey over the past couple of weeks:

Install the Tools

If you set up SQL Express with Advanced Services – this is 2008 (download link) – not 2008 R2 (download link). If you are going to back up a Bentley AutoPLANT database – you can NOT restore a 2008 R2 database to a 2008 server, you have to use another method.

  • To make reports, you can use Business Intelligence which comes with SQL 2008 Express with Advanced Services (thanks Geordie Guenther!)
  • A co-worker recommended a really easy tool to make reports that got me 90% of the way (but not advanced) called Microsoft Report Builder 2.0 (there is a 3.0 but we are still in a 2008 SQL Server world). I really like Bus. Intell because it is like Visual Studio and you can do much more.

Get Familiar with things:

One Report, Multiple Queries

I am a die hard Filemaker user/designer. For MS Access – building ONE report that can be “fed” with many different SQL or queries seems like a straight forward idea – but it isn’t. Here is how to get around it … kind of…

This is the site that gave me the goods – there is another, but that was too long ago.

Basically – you have buttons NOT on the current form (this is unfortunate)

  • close the form of interest
  • reopen the form with the DoCmd but with arguments
  • The value of the argument is then used in the Report_Open event – THIS is where you set your query or record source SQL.

Example: A navigation form contains 2 buttons – one for query A and another for query B. We are trying to open a Report called “Questions Test”

[vb]

Private Sub NavigationButton24_Click()
DoCmd.Close acReport, "Questions Test", acSaveYes
DoCmd.OpenReport "Questions Test", acViewReport, , , , ""
End Sub

Private Sub NavigationButton30_Click()
DoCmd.Close acReport, "Questions Test", acSaveYes
DoCmd.OpenReport "Questions Test", acViewReport, , , , "Not Answered"
End Sub

[/vb]
[vb]

Private Sub Report_Open(Cancel As Integer)

If IsNull(Me.OpenArgs) Then
‘Me.RecordSource = Me.OpenArgs
    Else
        If Me.OpenArgs = "Not Answered" Then
            Me.RecordSource = "qryQuestionsNotAnswered"
        Else
            Me.RecordSource = "qryQuestionsAll"
        End If
    End If

End Sub

[/vb]