Microsoft Access

Archived Topics
1

Which connection type would I use to interface PMI with an Access database?

2

Thats what we made the JDBC-ODBC bridge connection for. Unfortunately, there isn’t a pure JDBC driver for MS Access. Certain things [probably] won’t work, such as automatic table browsing, etc. You’ve been warned.

Normal queries should work fine though.

3

I also tried this once, then slapped myself and ran into a corner and cried

1 Like
4

Would highly recommend against using Access to drive your project. Use this if you need to access data against an existing source that gets updated (ie can’t copy static data over to a real DB).

I’ll take another look for Access JDBC drivers - it’s been awhile. There are probably native drivers that you can purchase. It isn’t too bad to configure FPMI to use any JDBC driver. Post if you have questions, we’ll help you with it.

5

As much as I hate to revive a topic on MS Access I have no choice.
I have project built using MSSQL storing all of my machine conditions. However, all of the product scheduling programs are handled by the IT department and in Access. Currently whenever a product changeover occurrs the database is supposed to "push" the data to SQL. Sometimes this does not happen and I end up storing data with the wrong order information.

Since I cannot change the system in use by production I would like to be able to query the Access table and store the data to SQL on a data change. I cannot get a working connection. I have tried both system and user DSN. Testing the connection works but when I test it in the query browser I get this ...

Any ideas?

6

For the sake of clarity, it seems that TimE’s error is occuring in FactorySQL instead of PMI, which this thread was started on.

On to the task at hand…

If you go to Help->Log Viewer and look through the errors there, is there anything that seems relevant that could provide more info?

What happens when you create a simple group, drag one item into it, and hit start? If it says “Table not available”, click “No” to indicate that you don’t want to continue, and then double click the message that appears. If it seems to have more info, post it up here.

Also, what version of FSQL are you using?

Regards,

7

Thanks for the reply Colby,
The only error in the log was the one in my first post.

Here are some more test results
FSQL Data Connection name = ‘Access_RollPGM’
FSQL Group Name = ‘L3OrderNumTest’

I created an OPC item and set the group to write to table name: ‘test’

[quote]08/25/08 8:51:35
L3OrderNumTest
Connection not available, user canceled.[/quote]

Here I created an action item to query an existing table: ‘CurrentRollDetail’

[quote]Error executing item Item: Invalid object name ‘Access_RollPGM.CurrentRollDetail’.
at FactorySQL.Items.AccessoryActionItem.Execute(EvaluationToken Token)
at FactorySQL.Groups.FSQLGroup.EvaluateActionItems(Boolean Triggered, EvaluationToken EvalToken)
at FactorySQL.Groups.FSQLGroup.InternalExecute(EvaluationToken EvalToken)
Invalid object name ‘Access_RollPGM.CurrentRollDetail’.
at FactorySQL.Items.AccessoryActionItem.Execute(EvaluationToken Token)
at FactorySQL.Groups.FSQLGroup.EvaluateActionItems(Boolean Triggered, EvaluationToken EvalToken)
at FactorySQL.Groups.FSQLGroup.InternalExecute(EvaluationToken EvalToken)</[/quote]

I am using version 4.2.5

8

I created a datasource connection on the gateway (3.2.3) and have this error
My connect URL is: jdbc:odbc:Access_RollPGM

[quote]Message: (hide details)

[Microsoft][ODBC Microsoft Access Driver] ‘(unknown)’ is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

Details:
SQLException
sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
sun.jdbc.odbc.JdbcOdbc.SQLDriverConnect(Unknown Source)
sun.jdbc.odbc.JdbcOdbcConnection.initialize(Unknown Source)
sun.jdbc.odbc.JdbcOdbcDriver.connect(Unknown Source)
org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294)
org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1247)
org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
com.inductiveautomation.factorypmi.gateway.db.DatasourceManager$DSInfo.runTest(DatasourceManager.java:578)
com.inductiveautomation.factorypmi.gateway.db.DatasourceManager$FaultedDatasourceRetryer.run(DatasourceManager.java:441)
java.lang.Thread.run(Unknown Source)[/quote]

9

Are you sure the ODBC connection is configured correctly in windows, and that it’s pointed to a valid access database file?

Just a guess, but it seems that the error is saying that the file path it’s trying to use is “(unknown)”… and the file path is part of the odbc configuration, so I’d double check that.

Regards,

10

You need to create a “System DSN” ODBC connection in Windows named: Access_RollPGM

To create that MS Windows ODBC connection for the FactoryPMI ODBC<–>JDBC bridge:

  1. control panel->administrative tools->Data Sources (ODBC)
  2. Click “System DSN” tab
  3. Click “Add…” and configure a connection to your Access database. Make sure to name it: Access_RollPGM

I’ll make a training video on this soon.

11

It is configured as a “System DSN”. I can browse the connection with Kepware’s data logger and view the tables.

12

I just installed OpenOffice and I am able to open the database using the same ODBC connection.

13

In your connection configuration in FactorySQL, is the “Translator” setting set to “Automatic” or “Access”? If automatic, try setting it explicitly to access.

14

I tried both settings and still no joy.

15

I’m confused - are you having trouble with Access in FactorySQL or FactoryPMI?

16

Both.

To summarize:

In FactorySQL:
If I try to write to access I get a “Connection not available”
If I try to query I get “Invalid Object Name”

In FactoryPMI:
Tyring to setup a new datasource connection:
“[Microsoft][ODBC Microsoft Access Driver] ‘(unknown)’ is not a valid path.”

NOTE:
The ODBC connection works with other programs.

17

Silly question, but is the FactoryPMI Gateway on the same machine as the System DSN?

18

Yes

19

Maybe a a GoToMeeting would help clear this up, feel free to give us a call.

20

Solved! Turns out his mdb file was sitting on a shared fileserver, and the “Local System Account” that the FactoryPMI and FactorySQL services were set to log onto didn’t have access to that shared drive. We switched the “Log On As” settings on both the services to proper domain accounts, and the connections started working.