VBA link between drawing and MS Access

[Anonymous]

First of all, I'm no wizzard at programming. However I have managed to piece together this macro that allows me to populate drawing attributes in my drawing. This macro also exports all attribute data to my MS Acces drawing database.

Until our most recent version of AutoCAD (2013) all was well. Now we are upgrading to AutoCAD 2017 and I cannot get it to work anymore. Nothing has changed. Both AutoCAd versions use the same .dvb file. I have set the same references and (off course) both (should) write to the same database.

We are also in the process of purchasing a PDM / PLM solution for our files. But until that is up and running, I'd like to continue using my macro.

What does work; The macro itself runs. It reads attributes in the titleblock, and if I change, or add information, it writes the info back to the titleblock.

If I choose to export data to Access, I get a Runtime error 429 ÄctiveX component can't create object. Debug keeps pointing me to the same line: 

Set dbInfo = OpenDatabase("K:\Techniek\CAD Tekeningen\Database\tekeningendatabase.mdb")

I'm starting to believe that this may be as simple as a syntax problem.

Below the (relevant) parts of code.

---------------------

Public dbInfo As Database
Public rsInfo As Recordset


-------------

Private Sub CommandButton3_Click()
Dim Tekeningnummer As String
Dim Msg As String
Dim Msg1 As String
Dim Msg2 As String
Dim Msg3 As String
Dim Style As String
Dim Title As String
Dim Response As String
Dim MyDate
MyDate = Format(Date, "dd-mm-yy")
Tekeningnummer = UserForm1.TextBox2.Text
Set dbInfo = OpenDatabase("K:\Techniek\CAD Tekeningen\Database\tekeningendatabase.mdb")
Set rsInfo = dbInfo.OpenRecordset("SELECT * FROM TEKENINGEN WHERE Tekeningnummer = '" & Tekeningnummer & "'", dbOpenDynaset)
Set rsInfo2 = dbInfo.OpenRecordset("SELECT * FROM TEKENINGEN WHERE Artikelcode = '" & Artikelcode & "'", dbOpenDynaset)
UserForm1.Hide

Can anybody help me out on this one. Macro is derived from http://www.afralisp.net/archive/vbaa/vba6.htm

[norman.yuan]

From the code, it looks like you used MS DAO 3.x (latest is 3.6) to connect to MS Access. Because MS DAO only support 32-bit and AutoCAD 2013 or older uses 32-bit VBA (regardless AutoAD itself being 32 or 64-bit), thus the code works previously with AutoCAD 2013

You did not mention, but it is very likely that your AutoCAD2017 is 64-bit version, meaning its VBA is also 64-bit, thus, your code cannot run there. If you have to use MS Access for the data storage, you can update your code to ADO (ActiveX Data Object), which support 64-bit. However, you also need to install 64-bit MC Access Database Engine (free download from MS site, or if the computer has 64-bit MS Access installed). This basically means that you need to re-write the code.

IMO, if possible, I'd avoid to use MS Access for AutoAD data storage, and also move the app to AutoCAD .NET API because of the re-writing.

[Anonymous]

Thanks for pointing me in the right direction.

As I unterstand, this probably means that part of my code must be rewritten. That's a pitty since I have little to no time to find things out on this. I'll probably stick to using 2013 until our PDM/PLM system is implemented.