Searching Access Database from Solidworks VBA 1

[kabluzy]

Good Day,
Does anyone know the best way to pull information from an Access Database file. The values from the database are used as the input in a VBA macro. I can do it in Excel, but how do you set up a query in the VBA code to search for the record that matches specific criteria in the Access file.

Best Wishes,
Suzy

 

[PDMAdmin]

I've never used Access, but there's an API example on the SW website that talks about how to communicate with Excel. It's the one that starts with "Automate Interaction with Microsoft Excel."

I hope that helps.

 

[rocheey]

Its not quite that simple.

You have to know the DataBase table set up, and the individual record/field data types.

You can then set up queries to return recordsets.

The good news is that with VBA, you can often just import a module written in another Office app (Excel, Access) to your SWX app, add an early binding reference, and run the code from there.

 

[Pake]

step one make sure that you add a reference to the microsoft DAO 3.6 Object Library.
Dim db as object
Dim swRecord as Recordset
set db = opendatabase(string representing location of database)
set swRecord = db.openRecordset(string representing table or query name, dbOpenDynaset)
swrecord.MoveFirst
do until swRecord.EOF
something = swRecord![Column Name]
swRecord.MoveNext
Loop
swRecord.Close
db.Close
set swRecord = Nothing
Set db = Nothing

 

[kabluzy]

Thank you very much Pake. That code worked on the first try. However I am trying to use SQL to do the searching. It has been rough going so far, but I thought I should give it my best before running back for help. I know it can be done. It is just a little tough.