Creating a connection to and Access Database

I am trying to create a connection to an Access database and having no luck. Can you provide a little guidance and perhaps an example.


I was able to setup an Access database to FactorySQL and FactoryPMI tonight.

On the FactorySQL end you need to setup a DSN. Do this by clicking the icon next to the “Database connection” field on the “Action” tab (far right side of application) for a group you’ve setup with active OPC items. Click the link with an orange flash on it and you will get a configuration area to the right. Name your connection, select “DSN connection” for the connection type and next to the DSN dropdown click the icon. This will bring you to the ODBC Data Source Administrator. Select the “System DSN” tab. Click the “Add” button to the right. Select the “Microsoft Access Driver (*.mdb)” be careful to not select the “Driver do Microsoft Access …” because it won’t work. Then a configuration box pops up. Create a DSN name and use the database "Select " button to select your *.mdb file. Select “OK”. Then “OK” again. That brings you back to FactorySQL. Select the DSN you just created. Test the connection with the “Test Connection” button. Mine said “Test Successful!” in response. Your’s should too.

Now here’s the catch… When I first did this I selected the wrong ODBC driver (the other Microsoft Access Driver) and when started my group logging it gave an error stating it couldn’t find the driver definition file. Subsequently I corrected to the correct driver but it still gave the same error. I solved this by going to Windows services, stopping and then starting the FactorySQL service. This is bug - something seems to be cached and I have entered a bug report to correct this. This may be the problem you were having. As soon as I did that FactorySQL created an Access table, the fields and started logging perfectly.

On the FactoryPMI side it was simple. I went into the “FactoryPMI Gateway Setup” and added a new connection. I selected the ODBC_bridge driver and then in the “connect URL” filled in the name of my DSN where it said to in the area. I saved, restarted the Gateway as requested and bingo! I had a good connection to the Access database as confirmed by clicking on the “live gateway status” link on the Datasources -> Connections page. I created a simple project with a table in it in 30 seconds and and saw my live data being logged.

It is a lot easier to show than to write. If there is any confusion at all or if this doesn’t work please email or call 800-266-7798 to setup a gotomeeting connection and the good folks there can show you live how to do it. Its actually simple when you see it.

Eventually you might want to consider going to an actual SQL database server such as MySQL 5.0. If you plan on logging a lot of data this would be the only way to go.

We virtually always use some sort of SQL database but I really had a lot of fun making this work with Access. Good Luck.