Why do I get a table read error in DB Tools Execute Query.vi

[lmtis]

Hello all,

I am using LV8 and the database connectivity toolkit.  I can connect to my server database and use the "DB Tools List Tables.vi" to pull the list of tables in the database, but when I try to use an sql command into "DB Tools Execute Query.vi" I get a : Error -2147217911 occurred at Conn Execute.vi->DatabaseTest2.vi message.  In the possible reasons it says : Possible reason(s):
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Microsoft Access Driver] Record(s) cannot be read; no read permission on 'tblShopOrders'. in Conn Execute.vi->DatabaseTest2.vi

I can open and edit this table with MSAccess.

Any ideas as to the cause of this error?

Jim

[Andy F.]

Hey Jim,

Most of the time that error refers to problems connecting to the database.  When you list the databases available, do you then connect to the database you are trying to run the query on, or are you trying to run a query on the server database?  What query command are you using?  Do you have any permissions set up on this database that would cause this error?  Please repost with some more information and I will try to help you out as much as possible.

Thanks!
Andy F.

[lmtis]

Hey Andy,

Since my first post I have discovered a bit more (not enough yet).  Firstly, I can access all of the tables using MSAccess (causing me to assume that I have the necessary permissions).  I am using the "DB Tools Execute Query.vi" with an SQL query and "DB Tools Fetch Recordset Data.vi" objects to try to get data.  I don't get an error when connecting to the database, but the error occurs at the execute query vi.  The database has a large number of tables, and I discovered that I can retrieve data from some of them using the above method.  I looked into the user and group permissions in the MS access security settings and found that the permissions were checked for all of the tables that I could open, and not for the rest.  I checked them on the ones I want to open, but this didn't help.  I went back into Access and made sure that they were still checked and they were.  And that's as far as I have gotten so far.

Thank you for any help that you can provide.

Jim

[Andy F.]

Jim,

I checked into that error some more and was able to find something very similar happening with another Access DB.  It does seem like file permissions are causing problems in Access.  Have you checked the permissions for each DB table in Access?  I know you mention that you checked on the tables that you can't open, but I just want to make sure that we are on the same page.  Are you able to create a database and tables from scratch in Access and open those in LabVIEW? 

Please let me know if you make any progress.
Andy F.

[lmtis]

Andy,

Yes, I have another database that I created in MSAccess, and I can open all of the tables in it with LV.  I checked the permissions for all of the tables in the DB I am having problems with.  I can open all of the tables with default permissions granted with LV.  All of the tables with permissions not checked by default, I am unable to open.  I can check the permissions on the tables, but this does not allow me to open them with LV.  The creator of the DB suggested that this problem might have something to due with LV not using the correct workgroup file.  I cannot find any mention of workgroup files in the LV documentation.

Thanks for your interest.

Jim

[Dennis_Knutson]

Workgroup files are documented in Access. It is an external security file. I try to avoid using Access like the plague but I'm pretty sure that it's a file with a .mdw extension. In ODBC Administrator, you can choose this file in the Access Setup by selecting the Database option in System Database, clicking the System Database button and browsing to the correct .mdw file.

[lmtis]

Thank you Dennis.  That was exactly the problem.  I have very limited MSAccess experience.  Once I added that in the ODBC Administrator, then got the database admin to enter my username correctly, all was fine.

Thanks you both for your assistance.

Jim