Strange TableDef behaviour

[tym]

Got a very strange one here...

I've had a VB6 app for the last 6 or 7 years that uses a DAO 3.51 database to store the data. No problems at all in that time... (I use Access 97 to manually edit a few things when I'm testing)

Now I've got a couple of databases that are giving a strange error. Doesn't matter if I use my VB6 app, or if I do this in Access 97 (but this is the Access version):
Public Sub ListTables()
Dim Tdx As TableDef
For Each Tdx In CurrentDB.TableDefs
Tdx.Attributes = 0


Debug.Print (Tdx.Name)
DoEvents
Next Tdx
Debug.Print "done"
End Sub

It ticks through nicely for about 95+% of the tables then all of a sudden at Next Tdf, I get "3421: Data Type Conversion Error" - after it's printed the table name. It's as if the next table, isn't a table...

Odd thing is, I'm currently re-writing this in vb2010 and written a convertion program to migrate the data to an access 2007 format file. That program, using ADOX and ADO sails through the SAME tabledefs absolutely FINE without any errors!!!

The database size is 746,744 so is comfortably inside the 1Gb limit.

What on earth is going, and more importantly, HOW DO I FIX IT!! :-)

Oh. Something that may or may not help...

Currentdb.tabledefs.count = -30912

Thanks for reading...


Tym.

[orange]

Why are you doing this ?

Tdx.Attributes = 0

Suggest you remove that line and the DoEvents, and you don't need the brackets in the debug.print line

[tym]

In one part, I've got all the tables hidden. Attributes = 0 unhides them all; it's code copied from another part of the program.

In this particular piece of code, neither the line quoted, nor the debug.print are actually used, except to show me where it falls over.

There's a section of code that looks through the list of tables and does something to each one, and it kept falling over. I've written this simple function to see where and why it does that.

It fails on "Next Tdf" with the data type conversion error, and despite spending 3 days solid on this, I can't a) find a solution on the web or b) work out what on earth is going on.

I could take out everything in the for/next, and it would still fall over.

how can a tabledef have a data type conversion error?

[orange]

I ran your code on my database with the changes I suggested. And it worked fine. I have access 2003.

Where do you have "tdf"? You use Tdx in your code.
What exactly does " it falls over" mean?

You do not have to worry about hidden objects, if you just want a list of TableDefs.

[tym]

Sorry - I meant Tdx.

I have applied this to a number of databases here and a lot of them work fine. There's just one or two that this happens on and they're bother large databases.

by" falls over" I mean that after a while, when it gets to Next Tdx, it comes to a grinding halt with "Run time error 3421: Data Type Conversion Error" at the "next Tdx" line, despite running through the loop several hundred times previously.

[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.

[tym]

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...

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:

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:

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"));

[orange]

I think you may have some sort of corruption. The negative "high" value is suspicious.

You can use this for nonSystem and nonDeletedTables

SELECT Name, 'table' AS ObjTyp FROM MSysObjects
WHERE ([Name] not Like '~*' AND [Name] not Like 'MSys*' AND MsysObjects.Type=1)

I used your function and it went to completion no errors.
I adjusted your code slightly -- I have a ListTables function, so added the "d"

Public Function dListTables()
10 On Error GoTo dListTables_Error

Dim lIDX As Long
Dim lMAX As Long
20 lMAX = CurrentDb.TableDefs.Count
Dim Tdx As TableDef
30 lIDX = 0

40 For Each Tdx In CurrentDb.TableDefs
50 lIDX = lIDX + 1
60 Debug.Print Tdx.name
70 Next Tdx

80 MsgBox "complete"
90

100 Debug.Print "Managed to run through " & lIDX & " records before breaking it!"
110 Debug.Print "It claims there are " & lMAX & " tables..."
120 Debug.Print Err.number & ": " & Err.Description

130 On Error GoTo 0
140 Exit Function

dListTables_Error:

150 MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure dListTables of Module AWF_Related"

End Function

Here are the last few records from my database
....
XAuthorArticle
XAuthors
YourTable
ZZZArticles
Managed to run through 384 records before breaking it!
It claims there are 384 tables...
0:

[tym]

I think you may be right, but I've tried every conceivable method of repairing the database to no avail! NOTHING WORKS!!

So, I think I'll be using the SQL to list all the table names and loop through those. Thanks for your input.

[orange]

If you have some corruption, it isn't likely to go away and could come back to bite you.

Have you done compact and repair? or
Start with a fresh empty database, then import each object into it?

Good luck

[tym]

Believe me - I've tried EVERYTHING! I've (programitcally) copied all the tables across to a blank database. I've tried "repair". I've tried compact... I think the way forward with this is the SQL method of listing the tables and work on the migration to access 2007!! :-)

[tym]

Added complication. Whilst it works in the database itself, when I use my VB6 app to read the list of tables I get:

3112 Record(s) can't be read; no read permission on 'MSysObjects'.

[tym]

I tried removing some of the tables and running it again, and it always falls over at 32768, which coincidentally is 1000000000000000 in binary. Looks like it's an issue with the number of tables.

run 1
462.1035 seconds
Managed to run through 32768 records before breaking it!
It claims there are -30909 tables...
3421: Data type conversion error.


run 2
465.6055 seconds
Managed to run through 32768 records before breaking it!
It claims there are -30991 tables... (this is after removing a number of tables)
3421: Data type conversion error.


I think this is verified by the negative number of tables.

Wish I could get the SQL to work...