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