Originally Posted by
orange
Well this line Tdx.Attributes = 0 is attempting to set an Attribute to 0, and that could be the issue with the data type error.
Since you're just reading TableDefs, there is no need to have that line.
I'd put the DoEvents in so it didn't lock up whilst it was working. However... brand new module...
Code:
Option Compare Database
Option Explicit
Public Function ListTables()
On Error GoTo Error_Trap
Dim lIDX As Long
Dim lMAX As Long
lMAX = CurrentDb.TableDefs.Count
Dim Tdx As TableDef
lIDX = 0
For Each Tdx In CurrentDb.TableDefs
lIDX = lIDX + 1
Debug.Print Tdx.Name
Next Tdx
MsgBox "complete"
Exit Function
Error_Trap:
Debug.Print "Managed to run through " & lIDX & " records before breaking it!"
Debug.Print "It claims there are " & lMAX & " tables..."
Debug.Print Err.Number & ": " & Err.Description
Err.Clear
End Function
When I run it I get...
Managed to run through 32768 records before breaking it!
It claims there are -30909 tables...
3421: Data type conversion error.
... and that's on the Next Tdx line...
Amended the code thus:
Code:
Public Function ListTables()
On Error GoTo Error_Trap
Dim T
T = Timer
Dim lIDX As Long
Dim lMAX As Long
lMAX = CurrentDb.TableDefs.Count
Dim Tdx As TableDef
lIDX = 0
For Each Tdx In CurrentDb.TableDefs
lIDX = lIDX + 1
Next Tdx
MsgBox "complete"
Exit Function
Error_Trap:
Debug.Print Timer - T
Debug.Print "Managed to run through " & lIDX & " records before breaking it!"
Debug.Print "It claims there are " & lMAX & " tables..."
Debug.Print Err.Number & ": " & Err.Description
Err.Clear
Same error...
So, question still stands I'm afraid - what causes a tabledef to have Data type conversion error, and how do I fix it?
As the process I have written is just to get a list of the table names and loop though them, I think I'll may use this sql instead to create a recordset and loop through it:
Code:
SELECT MSysObjects.Name, MSysObjects.Id, LCase(Left([Name],4)) AS Expr1
FROM MSysObjects
WHERE (((MSysObjects.Id) Between 0 And 1000000) AND ((LCase(Left([Name],4)))<>"msys"));