Can't access table Error Message from Microsoft Query

  • Thread starter Thread starter Franklin Smith
  • Start date Start date
F

Franklin Smith

I have an Access db with many different queries. I tried connecting to one
of the queries from within Excel using Microsoft Query and, although I can
see the query in the table listing, when I select it to try and link to it, I
receive the "Can't access table" error message.

Has anyone come across this??
 
Hello Steve,

Thanks for your email response. Regardless of whether the db is open or
not, I still get the same Microsoft Query error message, "Can't access table
<query name>". Any other suggestions?

Regards,

Franklin
 
Hello Steve,

Thanks for your reply. I tried linking to the query with the db both open
and closed, and get the same result, the Microsoft Query error message "Can't
access table <query name>".

Any other suggestions?

REgards,

Franklin
 
You can fixed it by modifiying field names in Access (remove space in field
names) then the export Access table. In your case, you are trying to connect
to the Access table directly, so you should try to modify the Access table's
field name structure to eliminate any spaces, and also make sure there is no
"redundancy" in field names. I hope you will not get this error again. If you
still get this error it means your database is damaged. In this case you can
use compact and repair utility or Stellar Phoenix access recovery software.

Download it:http://www.repair-access-file.com

Hope it helps..
 
You can fixed it by modifiying field names in Access (remove space i
field names) then the export Access table. In your case, you are tryin
to connect to the Access table directly, so you should try to modify th
Access table's field name structure to eliminate any spaces, and als
make sure there is no "redundancy" in field names. I hope you will no
get this error again. If you still get this error it means you
database is damaged.

Hope it helps..
 
I rather doubt this means the MDB is corrupt.
If you
still get this error it means your database is damaged. In this case you can
use compact and repair utility or Stellar Phoenix access recovery software.
Click to expand...

You also work for that company. Posting without disclosing any
connection is highly unethical and sleazy.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Thanks to everyone for the suggestions.

The issue had to do with the "dreaded" Nz function, which Access of course
likes, but Excel "chokes" on. It was the presence of that function within a
calculated field in a subquery, that made the entire query unable to be
accessed via Microsoft Query from within Excel.

Franklin
 
Franklin said:
Thanks to everyone for the suggestions.

The issue had to do with the "dreaded" Nz function, which Access of
course likes, but Excel "chokes" on.
Click to expand...

Not just Excel, of course: any application using ODBC or ADO to connect to a
..mdb file will fail to execute a query containing that function. And it's
not just Nz: do a search at support.microsoft.com using the keywords jet and
sandbox to see the other affected VBA functions.

In addition, user-defined functions are barred as well ... grr.
 
Back
Top