Solved QueryDefs

[spaLOGICng]

Thanks for the explanation Doc Man. I tried to do a compile on the F/E but it is too big. Also the documenter will not work.
No matter it will give me a task to do tonight.

Hi,

A lot of times this error is generated by broken links to external sources. If the source table has been renamed, moved, or deleted, this particular error will occur.

I have a toolbox of code I have written over the many years that helps me analyze the integrity of an access application. One in particular is a simple loop mechanism that will open each table as a record set using a select top 1 * from tdf.name. The Tables that do not open are assumed broken.

The broken table links can be by design or just bad dbms management. Once you have identified and deleted the links, you will likely be able to use the built-in Access tools. A caveat to deleting broken tables is when it's by design, which is elementary. A developer may have created a linked table, makes use of the tdf in VBA, but does not test for existence and assumes it is there to interact with. It is my opinion that all temporary tdf's should be deleted when the processing procedure ends. I typically use exit_handler to clean up variables and temporary objects.

 

[HealthyB1]

Hi,

A lot of times this error is generated by broken links to external sources. If the source table has been renamed, moved, or deleted, this particular error will occur.

I have a toolbox of code I have written over the many years that helps me analyze the integrity of an access application. One in particular is a simple loop mechanism that will open each table as a record set using a select top 1 * from tdf.name. The Tables that do not open are assumed broken.

The broken table links can be by design or just bad dbms management. Once you have identified and deleted the links, you will likely be able to use the built-in Access tools. A caveat to deleting broken tables is when it's by design, which is elementary. A developer may have created a linked table, makes use of the tdf in VBA, but does not test for existence and assumes it is there to interact with. It is my opinion that all temporary tdf's should be deleted when the processing procedure ends. I typically use exit_handler to clean up variables and temporary objects.

Thanks for your suggestion. I have been busy over the past few days so only just saw your message / suggestion. I would be extremely grateful if you could share your routine with me.
Thanks in advance.

 

[spaLOGICng]

Thanks for your suggestion. I have been busy over the past few days so only just saw your message / suggestion. I would be extremely grateful if you could share your routine with me.
Thanks in advance.

No Worries, I had just commented yesterday.

Here is my test script [...], give a whirl and let us know if you discovered the table issues.



Sub DAO_Test_Links()

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rs As DAO.Recordset

Dim strTDF As String
Dim strSQL As String

Set db = CurrentDb

For Each tdf In db.TableDefs
strTDF = tdf.Name
strSQL = "SELECT TOP 1 * FROM [" & strTDF & "];"
Set rs = db.OpenRecordset(strSQL)
rs.Close
Set rs = Nothing
Next tdf


exit_handler:
On Error Resume Next
Set tdf = Nothing
Set rs = Nothing
Set db = Nothing
Exit Sub

Err_Handler:
Select Case Err.Number
Case 3622
'You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table
'that has an IDENTITY column.
'If you use the dbOpenSanpshot and there are no records, an error will occur
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
Resume Next
Case 3184
'Could not execute query; could not find linked table.
Debug.Print "Linked Table is Missing: " & strTDF
Resume Next
Case 3078
'For Access DB and Excel WB TXT and CSV Links
'The Microsoft Access database engine cannot find the input table or query
'Name of TDF'. Make sure it exists and that its name is spelled correctly.
Debug.Print "Linked Table is Missing: " & strTDF
Resume Next
Case 91
'Object variable or With block variable not set
'Could not set RS because of 3184
Resume Next
Case 3146
'ODBC--call failed.
'Could be the Server Table or View was altered and not refresh.
Debug.Print "Linked Table is not Congruent with TDF and needs refreshed: " & strTDF
Resume Next
Case Else
'just in case there are other unexpected errors
Debug.Print strTDF, Err.Number, Err.Description
Resume Next

End Select



End Sub