Solved QueryDefs

[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