Run-time error '3078': can’t find table when it does exist! (1 Viewer)

Inspired · Jun 5, 2011

“Run-time error '3078': The Microsoft Jet database engine can't find the input table or query TableName”

After linking to a table in an external db, I am attempting to open a recordset using the simple code shown below:

Code:

[COLOR=black][COLOR=black]Set rst = currentdb.openrecordset(“SELECT * FROM TableName”)[/COLOR][/COLOR]

When executing this statement I get the error message as shown above, even though the table DOES exist. I simply press F5 etc and then the code continues running, bizarrely!

The module (subroutine) where this problem occurs is part of a larger process (several subroutines), and when ran as part of this larger process, the error occurs. However, if I run the sub separately, it works perfectly. In the preceding sub, the table in question is being linked to, updated and then the link is being deleted as my code moves on to the next table. The problem must be caused by something in this preceding subroutine but I just can’t figure out what, as all this preceding sub is doing is linking to tables, updating them and deleting the links.

Any ideas?

This is slightly similar to my last query in that I’m encountering some weird and inexplicable issues, which is rather irritating to say the least!

spikepl · Jun 5, 2011

Maybe it is in the preceding routine and maybe not. I woould start commenting out code in your preceding routine, or short-circuit the logic and bypass some code, until the error goes away. If it does, then at least you know where to look.

Update: My guess is that the logic in your code is not the intended one, and so your table is unknown, just as Access says. Not sure exactly what F5 does, but apparently some code on the form is rerun, and provides the link to the table. So eg. check if your table is known at the end of the SUB that creates the links

gemma-the-husky · Jun 5, 2011

if you are getting the tablename in CODE, it needs to be

"SELECT * FROM " & chr(34) & "[" & tablename & "]" & chr(34)

Inspired · Jun 7, 2011

gemma-the-husky said:
if you are getting the tablename in CODE, it needs to be

"SELECT * FROM " & chr(34) & "[" & tablename & "]" & chr(34)

Thanks but I wish it were as simple as that.

Inspired · Jun 7, 2011

spikepl said:
Maybe it is in the preceding routine and maybe not. I woould start commenting out code in your preceding routine, or short-circuit the logic and bypass some code, until the error goes away. If it does, then at least you know where to look.

Update: My guess is that the logic in your code is not the intended one, and so your table is unknown, just as Access says. Not sure exactly what F5 does, but apparently some code on the form is rerun, and provides the link to the table. So eg. check if your table is known at the end of the SUB that creates the links

Thanks for this, had everything else failed I would have tried this. In fact, if I have time I still might as it would be interesting to see what could be causing this issue.

Thankfully, I've managed to get round this (touch wood!) by actually opening the external db and opening the recordset in this external db.

E.g.

Code:

set Externaldb = opendatabase(strExternalDBPath)
 
set rst = Externaldb.openrecordset(strSQL)

I might also try opening the recordsets with ADO as another alternative.

boblarson · Jun 7, 2011

Inspired said:
by actually opening the external db and opening the recordset in this external db.

Umm, I think your problem was that you

1. Didn't let anyone know you were trying to connect with a database that was NOT the one you had the code in. This is like going to the doctor and you asking for help because there is a hand that is hurting really really bad and so he looks at your hand and says he doesn't see anything. And then you call him back later and tell him that your wife's hand is better because you soaked it. So the doctor was not looking at the right hand. He wasn't even looking at the right person. So, while you talked about linking of tables, it was not clear exactly what you were doing and the code you talked about would have been good to provide.

2. You can't use CurrentDb if you are wanting something outside of the current database. If the table is linked, then you can but it has to be linked properly to work.

gemma-the-husky · Jun 7, 2011

your original post said

.... can't find the input table or query TableName

are you saying you DO have a table called tablename?

boblarson · Jun 7, 2011

gemma-the-husky said:
your original post said

are you saying you DO have a table called tablename?

Good point -

Inspired · Jun 7, 2011

boblarson said:
Umm, I think your problem was that you

1. Didn't let anyone know you were trying to connect with a database that was NOT the one you had the code in. This is like going to the doctor and you asking for help because there is a hand that is hurting really really bad and so he looks at your hand and says he doesn't see anything. And then you call him back later and tell him that your wife's hand is better because you soaked it. So the doctor was not looking at the right hand. He wasn't even looking at the right person. So, while you talked about linking of tables, it was not clear exactly what you were doing and the code you talked about would have been good to provide.

2. You can't use CurrentDb if you are wanting something outside of the current database. If the table is linked, then you can but it has to be linked properly to work.

From my initial post:

"After linking to a table in an external db, I am attempting to open a recordset using the simple code shown below:

Code:
Set rst = currentdb.openrecordset(“SELECT * FROM TableName”)
When executing this statement I get the error message as shown above, even though the table DOES exist. I simply press F5 etc and then the code continues running, bizarrely!"

The problem was the link, but as already detailed, it only worked when that specific subroutine (containing the code) was ran by itself, strangely enough.

Inspired · Jun 7, 2011

gemma-the-husky said:
your original post said

are you saying you DO have a table called tablename?

The actual table name in my code AND database was “tblVolumes”, I was using TableName purely as an example.

boblarson · Jun 7, 2011

Inspired said:
The actual table name in my code AND database was “tblVolumes”, I was using TableName purely as an example.

It's best to use the real names when posting for help. Changing the names of items to be examples instead of the real thing can sometimes obscure the real problem.

gemma-the-husky · Jun 7, 2011

it might be using currentdb that is the problem. It can "lose scope"

I tend to set currentdb to an object instance, then use that.

dim db as database
set db = currentdb

boblarson · Jun 7, 2011

gemma-the-husky said:
it might be using currentdb that is the problem. It can "lose scope"

I tend to set currentdb to an object instance, then use that.

dim db as database
set db = currentdb

No, for an outside database you don't use currentdb. You set db to

Set db = OpenDatabase("C:\Folder\My.accdb")

Run-time error '3078': can’t find table when it does exist! (1 Viewer)

Inspired

Registered User.

spikepl

Eledittingent Beliped

gemma-the-husky

Super Moderator

Inspired

Registered User.

Inspired

Registered User.

boblarson

Smeghead

gemma-the-husky

Super Moderator

boblarson

Smeghead

Inspired

Registered User.

Inspired

Registered User.

boblarson

Smeghead

gemma-the-husky

Super Moderator

boblarson

Smeghead

Similar threads

Users who are viewing this thread