ODBC – Tables not available (Access 2000)

Topic

New Reply

I’ve imported a large text file and created a table that will be the source file (via ODBC) for a data import job.
I’ve used the default Access ODBC driver and pointed it to the database I’ve created. Then, as a test, I’ve attempted an import (get external data/query) into excel.
Now here’s the problem, I can see the database but when I attempt to expand (click the + sign) the database to view the available tables, nothing appears.
One possible clue. The main table I need to use contains approx. 300,000 rows

Thanks for the help!!

Skip

Reply | Quote

Replies

With a table that large, Excel is going to choke on it – if I recall, 2000 is limited to 32K rows, while 2002 will handle 65K rows. Just out of curiosity, why are you attempting to export it to Excel after you’ve gotten it into Access?

Reply | Quote

Sorry for the confusion. I realize the limitations of Excel. My test was only to verify that there was nothing wrong with the ODBC connection. Attempting to tap the data into Crystal also failed. BTW, Excel will import 65,000 lines then stop if it functions correctly.
Other databases (.mdb) on my system function properly. Could it be a permissions error?

Reply | Quote

Try creating a mail merge in Word that uses the table – you don’t need to actually do the merge, just set up Access as the data source and specify ODBC as the data method. If you can’t do that, then there are problems of some sort. Unless you have activated User Security for Access, I doubt if it’s a permissions problem – if you can open and edit it from Access, then you have the necessary file permissions. It is possible that it is corrupt in some way that blocks ODBC access, but that would be quite unusual.

Reply | Quote

Try creating a mail merge in Word that uses the table – you don’t need to actually do the merge, just set up Access as the data source and specify ODBC as the data method. If you can’t do that, then there are problems of some sort. Unless you have activated User Security for Access, I doubt if it’s a permissions problem – if you can open and edit it from Access, then you have the necessary file permissions. It is possible that it is corrupt in some way that blocks ODBC access, but that would be quite unusual.

Reply | Quote

A couple of other suggestions:

Permissions – as Wendell said this could be a permissions problem. If the ODBC connection is setup properly and you do not have the correct permissions for where the data is stored, you won’t be able to access it. I have gone as far as seeing the tables via odbc but get no results when selecting them for input. This could also be a server problem if you are using a terminal server and the session is “hung” which will also prevent you from getting data even through everything else is fine.

ODBC – Is the ODBC connection setup properly? Go to control panel, select ODBC Drivers, Configure, and press the TEST button to see if it is working properly.

Run a test. Try moving the raw data file to your local c drive and create a system or machine dsn to connect to it and then re-try the ODBC import. If it runs locally, then the problem is with your access to the network folder.

HTH

Reply | Quote

A couple of other suggestions:

Permissions – as Wendell said this could be a permissions problem. If the ODBC connection is setup properly and you do not have the correct permissions for where the data is stored, you won’t be able to access it. I have gone as far as seeing the tables via odbc but get no results when selecting them for input. This could also be a server problem if you are using a terminal server and the session is “hung” which will also prevent you from getting data even through everything else is fine.

ODBC – Is the ODBC connection setup properly? Go to control panel, select ODBC Drivers, Configure, and press the TEST button to see if it is working properly.

Run a test. Try moving the raw data file to your local c drive and create a system or machine dsn to connect to it and then re-try the ODBC import. If it runs locally, then the problem is with your access to the network folder.

HTH

Reply | Quote

… one other item to think about. Is the excel file protected?

Reply | Quote

… one other item to think about. Is the excel file protected?

Reply | Quote

Sorry for the confusion. I realize the limitations of Excel. My test was only to verify that there was nothing wrong with the ODBC connection. Attempting to tap the data into Crystal also failed. BTW, Excel will import 65,000 lines then stop if it functions correctly.
Other databases (.mdb) on my system function properly. Could it be a permissions error?

Reply | Quote

With a table that large, Excel is going to choke on it – if I recall, 2000 is limited to 32K rows, while 2002 will handle 65K rows. Just out of curiosity, why are you attempting to export it to Excel after you’ve gotten it into Access?

Reply | Quote