Basically.... You pretty much can't use a SolidWorks macro running in SolidWorks 2013 or later to communicate with an Access database. SolidWorks uses VBA 7, which is 64 bit. The ADODB driver library for .mdb is 32 bit, and will not work in a SolidWorks macro running on a 64 bit machine. I have a macro that we use every day which used to get parameters from an Access database. After quite a bit of digging around, the best thing I came up with was to move our data from an MDB file to our SQL Server server. You can see what I found here:
However, you can use code running inside Access or Excel etc. to get data from SolidWorks and write to Access.
Figured this out. Pretty simple stuff really. After installing 64bit office.
Dim conn As New ADODB.Connection
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Mode = adModeReadWrite
.ConnectionString = <"database dir">
conn.Execute (UPDATE <"table"> SET <"field"> = "value in solidworks" WHERE <"field to match"> = "value to match data")
In my instance it searches "field to match" and compares it against my actual model name ("value to match") to know where to populate the specific fields with values.
Don't forget, for those that don't know, when passing strings there will be lots of "&" and " ' " to pass them correctly for instance, below is what my actual conn.execute statement looks like:
conn.Execute ("UPDATE tblDesigns SET dieWidth = " & "'" & <form label> & "'" & " WHERE design_name = " & "'" & ModelName2 & "'"
True... I had forgotten that 64 bit office is an option for some people. In our corporate environment it was not. It should be noted that Microsoft recommends 32 bit Office for most users, as per the following link:
Basically, with 64 bit Office, you will be able to read/write to/from an MDB, but a lot of other things will break. Also, your code will not work for anyone who is using 32 bit Office.
Microsoft now supply 64 bit drivers for Access, so you can continue to use 32 bit Office, but be able to connect to Access databases through 64 bit programs.
Search for AccessDatabaseEngine2010_x64.exe
You must install this with the /passive switch to avoid interfering with a 32 bit Office installation.