Access wants deleted linked table

Access wants deleted linked table

Post by Miles Thom » Thu, 19 Aug 2004 00:19:56


I have been developing a database update procedure, and the first table I worked with was named BCRDates; the linked table (to BCRDates in the source database) was named BCRDateslnk.

The links are created as temporary QueryDefs and deleted as execution progresses through an array of table names.

When execution moves to another table, and attempts to execute a different SQL statement to set another Recordsource, Run time error 3078 results.

Heres the code fragment:
rs_src.Close 'in this instance the only table I as working with is SITES.
Set rs_src = Nothing

strSQL = " SELECT * from SITESlnk as src, SITES as tgt WHERE tgt.ProfacNumber not in (Select src.ProfacNumber from src where tgt.ProfacNumber = src.ProfacNumber)" 'note no ref to BCRDateslnk and SQL may not be exactly correct
Set rs_Src = dbTgt.OpenRecordset(strSQL)

When execution hits the "Set rs_Src ..." statement, execution halts with Run Time error 3078 as follows:
"The Microsoft Jet database engine cannot find the input table or query 'BCRDateslnk'. Make sure it exists and that its name is spelled correctly."

First question: "Why does Jet want this table? Why should Jet care?" - but that can probably never be answered. <frown>

Second question: "The linked table has been deleted, both the source and target tables have been checked and it has been confirmed that BCRDateslnk does not exist. Why or how is JET picking up on BCRDateslnk?"

Note to second question: Both source and target databses have been run through Access's "Repair Database" procedure; VB has been closed and the computer shut down and cold booted. The same error message reappears. There is NO PLACE in the code where BCRDateslnk could be created.

Third question: "Is there a work around?" This procedure has to go to a customer, and there is no way permanent linked tables can be allowed.

This VB6, Access 97, DAO.

Any suggestions / SWAGs will be welcomed - Miles Thompson
 
 
 

Access wants deleted linked table

Post by Steve Gerr » Thu, 19 Aug 2004 10:04:56


| When execution hits the "Set rs_Src ..." statement, execution halts
with Run Time error 3078 as follows:

| "The Microsoft Jet database engine cannot find the input table or
query 'BCRDateslnk'. Make sure it exists and that its name is spelled
correctly."
|
| First question: "Why does Jet want this table? Why should Jet care?" -
but that can probably never be answered. <frown>
|
| Second question: "The linked table has been deleted, both the source
and target tables have been checked and it has been confirmed that
BCRDateslnk does not exist. Why or how is JET picking up on
BCRDateslnk?"
|
| Note to second question: Both source and target databses have been run
through Access's "Repair Database" procedure; VB has been closed and the
computer shut down and cold booted. The same error message reappears.
There is NO PLACE in the code where BCRDateslnk could be created.
|

SWAG#1:
SITESlnk is actually a link to BCRDateslnk. It might have gotten
modified by an earlier version of your code, which somehow changed an
existing querydef instead of creating a new one.


SWAG #2:
There is a query defined in the Access file that still references
BCRDateslnk, and SITESlnk or something is using the query.

 
 
 

Access wants deleted linked table

Post by Miles Thom » Thu, 19 Aug 2004 21:44:43


Steve,

SWAG#2 got me close to resolution.

As this thing is under development there have been a number of times when I've terminated execution. One of the artifacts left behind was a query named "src". When I looked at it in Access one of its source tables was missing ... possibly BCRDateslnk?

Anyway it is now cleaned up so that I can start fixing my SQL SELECT.

Thanks for providng another pair of eyes.

Cheers - Miles