Carl,
It didn't take much time but I broke it already. I attempted to install the HASP driver and had this error[quote]The NTVDM CPU has encountered an illegal instruction.
CS:054d IP:01a1 OP:63 74 6f 74 79 Choose 'Close' to terminate the application.[/quote]
After this the connection stopped working in PMI
It still works in FSQL fine. I checked the logon properties of the FPMI service and it is identical to the FSQL service.
TimE,
That was the same error I encountered a while ago - see the following post: http://www.inductiveautomation.com/forum/viewtopic.php?f=26&t=3597
Whether installing the HASP driver is going to sort your Access problems I have no idea 
Yeah, the HASP driver issue is easy to fix - just download the full HASP installer from their website.
I doubt that has anything to do with the access database not working anymore… I’m not sure why it would have stopped working though, did anything change in the System DSN settings?
I installed the HASP driver and everything loads MUCH faster! You were right that it was unrelated to the ODBC connection.
I need to make a correction. It no longer works in either FPMI or FSQL
I did some experimenting:
Reboot: still invalid path
Stop PMI and FSQL service
Delete logon and reconfigure
Start PMI and FSQL service: still invalid path
Change ODBC to local copy of database: It works
Change back to network file: It works
Disconnect and reconnect to service (FSQL): Invalid path
Well it is definately still a network share permissions issue. Maybe the user that those services are logging on as doesn’t have access to the share, but when you log on as that user and browse the share, you are being granted temporary authentication during your session? Sounds like you need to talk to IT to see how permissions are set up for that share.
IT looked into this but said everything is setup correctly.
This is my current scenario
The access database is on the network
The FactoryPMI computer running both FPMI and FSQL has an ODBC connection to the above file.
I can connect to the database running OpenOffice Base on the FactoryPMI computer using the same ODBC connection.
I can connect to the database running Kepware Logger on the FactoryPMI computer using the same ODBC connection.
Why would it work with OpenOffice and Kepware and not with FPMI or FSQL? Is there a difference in how each program implements the ODBC connection?
I looked at the permissions myself and BPIHQ\FactoryPMI is not specifically listed. FactoryPMI is a member of BPIHQ\Engineering which is listed.
humm....? From what I understand a DSN is a set of registry values that tells the application what driver and other pieces of info (connection path, optional username/pass, etc). It's important to determine if the authentication works from those settings or the currently logged on user in Windows.
Domain Name: BPIHQ.com
Full Computer Name: factorypmi.BPIHQ.com
User ID: FactoryPMI
Service Account name: factorypmi@BPIHQ.com
factorypmi@BPIHQ.com is a member of BPIHQ\Engineering
BPIHQ\Engineering is the required permission for the database directory
The database itself does not require a logon ID or password.
FSQL Configuration:
Name: Access_RollPGM
Type: DSN
DSN: Access_RollPGM
Translator: Access
Test is Successful with and without authentication. Even with a bogus ID and password.
The following is an error message I get when trying to run a group with 1 OPC item
Mode: OPC to DB
Connection: Access_RollPGM
Table Name: test
[quote]08/27/08 13:36:48
Connection Status
ERROR [HY024] [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.
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed
ERROR [HY024] [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.[/quote]
Here is the Error from the gateway with a password.
[quote]FAIL (next test in 1 second)
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]
Here is the Error from the gateway without a password.
[quote]FAIL (next test in 3 seconds)
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]
Clearly, they are wrong.
No, there is no difference in how they 'implement the ODBC' connection. They don't do any such thing - they are just asking Windows for the ODBC connection. The difference is that those applications, as wellas the "test connection" button in the FactorySQL frontend, are running under the permissions of the currently logged in user session, while FactoryPMI and FactorySQL are running as Windows services. This difference is clearly causing the permissions foul-up that is causing the connection to fail.
It worked after we changed the "log on as" settings of the service, but maybe that was only because we had obtained temporary permissions in the currently logged on user session of the same username. I think they need to log on as a user who has explicit access to the shared resource in question.
Actually we were all wrong.
This is what we did when we thought it worked.
Changed the ODBC connection to point to a local file
Looked at status: No errors
Changed the logon as settings.
Changed the ODBC connection point to a network file
Looked at status: No errors
Later when I Installed the HASP driver the service was restarted and the connection failed. I did some experimenting and found that the ODBC connection settings are not changed in the service unless it is restarted. We did this at first but not after we changed the logon settings
It was difficult to resolve when you have experts on both sides with opposing views and I am the dummy in the middle. I spent the last few hours searching for ODBC errors of this type and found that if I change the ODBC file path to use a universal naming convention as opposed to a mapped drive then it works.
I am now back to logging on as a local service and it is working. Evidently a local file can use a mapped drive but a service cannot. Maybe this is characteristic of the ODBC driver version I am using.
Ah, sneaky...
Glad its working! Nice job getting to the bottom of the problem.