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
Category Archives: MS Access
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:
- A good book that I bought on Kindle on report services is Microsoft® SQL Server® 2008 Reporting Services Step by Step (Step by Step (Microsoft))
- If you are feeling like you need to brush up on T-SQL (Microsoft’s SQL) then the book is Beginning T-SQL with Microsoft SQL Server 2005 and 2008
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]