4 Replies Latest reply on May 13, 2015 3:53 AM by Simon Turner

    Writing to access database

    Jay Donihoo

      Hello All,

       

           I am currently exploring options on how to write values from Solidworks directly to an Access DB. I have zero knowledge of Access or how to communicate with it. Any help or direction would be greatly appreciated. Below is the code I found for connecting with Access but writing to it is beyond me.

       

      Dim conn As ADODB.Connection

        Set conn = New ADODB.Connection

      With conn

          .Provider = "Microsoft.ACE.OLEDB.12.0"

          .Mode = adModeReadWrite

          .ConnectionString = "Data Source=<filename>.mdb"

          .Open

      End With

        • Re: Writing to access database
          Josh Brady

          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:

           

          VBA7 (64 bit) - Getting data from a database

           

          However, you can use code running inside Access or Excel etc. to get data from SolidWorks and write to Access.

          • Re: Writing to access database
            Jay Donihoo

            Figured this out. Pretty simple stuff really. After installing 64bit office.

             

            Dim conn As New ADODB.Connection

             

            With conn

                .Provider = "Microsoft.ACE.OLEDB.12.0"

                .Mode = adModeReadWrite

                .ConnectionString = <"database dir">

                .Open

            End With

             

            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 & "'"

              • Re: Writing to access database
                Josh Brady

                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:

                 

                64-bit editions of Office 2013

                 

                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.

                  • Re: Writing to access database
                    Simon Turner

                    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.