Getting info from MS Access

Posted on by

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(I am using Office 2010 and chose DAO):

  • Microsoft Office Access 14.0 database engine object
  • Microsoft Office 14.0 Object Library (not sure if that is ciritical for AutoCAD – I will update this post if it is required)
  • DO NOT use DAO 3.6 or DAO 2.5

OK (next day) – no go – … I really dislike getting data from MS Access…. I have Office 2010 64bit, AutoCAD ?? bit and Windows 7 64bit. Here are some links so I don’t forget

  • Making a proxy
    • a couple lines wrap in the CDATA section – works but provides the error on the next line.
  • Making VBA DLL’s in Visual Studio (C#) (it worked – see my comment in that article – and I am planning on putting proxy code in the stuff below!)
    • If you are doing this in c# include these lines
      • using System.Diagnostics;
        using RGiesecke.DllExport; (from here)
      • Ensure that anyCPU is your compile setting as x86 does NOT WORK. remember….
        • AutoCAD 64bit
        • Office 64bit
        • Windows 7 64bit
  • This bullet went no where – even when my good buddy from  geekinc came to ensure I wasn’t doing some bonehead move. One thing is to UNinstall and REinstall the MS 2010 Access Redistributable and restart your machine – then you can install the 2007 drivers ALONGSIDE this which theoetically solve people’s issues (except mine as it were) . MSDN “How to: fix error ‘the microsoft.ace.12.0’ provider is not registered on this local machine
    • I long ago down loaded the _64 version of the driver. I tried to install the other without the _64 but I already knew the answer from long ago. Sure enough – if you have 64 bit office – download the _64 version of the driver. Other wise you will be asked to uninstall the 32 bit version of office and reinstall the 64 bit (don’t)

The “making a proxy” works and using a dos/wscript type link to running it through the Windows Cmd Line would work.

[dos]

Sub TryCL()
Dim ObjExec
Dim strFromProc

Set objShell = CreateObject(“WScript.Shell”)
Set ObjExec = objShell.Exec(“cmd.exe /c dir”)
Do
strFromProc = ObjExec.Stdout.ReadLine()
Debug.Print “ABC ” & strFromProc & ” DEF”
Loop While Not ObjExec.Stdout.atEndOfStream
End Sub

[/dos]

However, I might try a .net approach. Here are some links I have found