DB Tools List Columns.vi Error code -2147217865

[ajh]

Using DB Tools List Tables.vi I can sucessfully display the tables in the database. However when I attempt to read the colums from a existing table I get the following error -2147217865.

Conn Execute.vi->DB Tools List Columns.vi->Untitled 1<ERR>Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'Department'.  Make sure it exists and that its name is spelled correctly. in Conn Execute.vi->DB Tools List Columns.vi->Untitled 1

What I found was that if a space was used in the name of the table I get the error. example "Forms List". If I changed the name of the table to "Forms_List", I am able to read the columns.  My problem is that the database already exists with many table names that include spaces. If I rename a table the relationships are all deleted.

Anybody know a easier way than to redo the access database?

[Dennis_Knutson]

Try putting a double quote mark (") around the table name.

And find the designer of the database and slap him or her up side the head.

[Peter_Bham]

Hi Dennis.  I did what you recommended and it both worked and hurt.  What is it about the database that requires quotes?  I am designing both the LabVIEW and SQL Express sections.  Thanks.

[mikeporter]

The problem is that names in databases should not have spaces. Table names, column names - no spaces... To make matters worse, different DBMS use different delimiters. Some use double quotes, some (like Jet) use square brackets. It can get messy very fast. How many tables do you have that have "bad" names in them? If you created the databases originally, you might be money ahead to recreate the database with good names and write a small program to transfer all the data to the new tables.

Mike...

PS: This sort of situation is why I always recommend that you don't create tables interactively. Take the time to write the SQL scripts to allow you to edit and recreate the entire database if necesary. Alternately, Oracle distributes (for free) a tool called Data Modeler that has a nice interface for defining database structure and can then generate the SQL scripts for you in versions that are compatable with several major DBMS.