Help With Adox And Autonumber

[Jacopo]

Hi to everyone.
I am having a problem running the following code downloaded from the tips of Allen Browne:

Function DeleteAllAndResetAutoNum(strTable As String) As Boolean
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim strSql As String

'Delete all records.
strSql = "DELETE FROM [" & strTable & "];"
CurrentProject.Connection.Execute strSql

'Find and reset the AutoNum field.
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTable)
For Each col In tbl.Columns
If col.Properties("Autoincrement") Then
col.Properties("Seed") = 1
DeleteAllAndResetAutoNum = True
End If
Next
End Function

this function has always worked perfectly. Now not anymore.
When it gets to the line "set tbl=cat.tables(strtable)" access crashes.
I use Office 2003. I tried reinstalling it, import the whole db into a new access file, apply the functions to different tables, empty or not. Still crash.
I then installed service pack two of office 2003 and no better.
The error information on crash are the following but to me they don't mean anything:

AppName: msaccess.exe AppVer: 11.0.6566.0 AppStamp:42cdb33e
ModName: msjet40.dll ModVer: 4.0.8618.0 ModStamp:403430ac
fDebug: 0 Offset: 00046ddc

The reference to the adox library is "Microsoft ADO Ext. 2.8 for DDL and security" and it points to the, I guess, correct Msadox.dll. The version of that dll is 2.81.1128.0 which i think is the latest one.
Please if anyone can help because I am now out of resources.
Thank you,
Jacopo

[ADezii]

Hi to everyone.
I am having a problem running the following code downloaded from the tips of Allen Browne:

Function DeleteAllAndResetAutoNum(strTable As String) As Boolean
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim strSql As String

'Delete all records.
strSql = "DELETE FROM [" & strTable & "];"
CurrentProject.Connection.Execute strSql

'Find and reset the AutoNum field.
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(strTable)
For Each col In tbl.Columns
If col.Properties("Autoincrement") Then
col.Properties("Seed") = 1
DeleteAllAndResetAutoNum = True
End If
Next
End Function

this function has always worked perfectly. Now not anymore.
When it gets to the line "set tbl=cat.tables(strtable)" access crashes.
I use Office 2003. I tried reinstalling it, import the whole db into a new access file, apply the functions to different tables, empty or not. Still crash.
I then installed service pack two of office 2003 and no better.
The error information on crash are the following but to me they don't mean anything:

AppName: msaccess.exe AppVer: 11.0.6566.0 AppStamp:42cdb33e
ModName: msjet40.dll ModVer: 4.0.8618.0 ModStamp:403430ac
fDebug: 0 Offset: 00046ddc

The reference to the adox library is "Microsoft ADO Ext. 2.8 for DDL and security" and it points to the, I guess, correct Msadox.dll. The version of that dll is 2.81.1128.0 which i think is the latest one.
Please if anyone can help because I am now out of resources.
Thank you,
Jacopo

Everything looks fine - the only logical explanation, in my mind, would be a corrupt .dll, specifically the one which provides the ADOX functionality (Msadox.dll).

[Jacopo]

Dear Adezii,
thank you for the reply. I concentrated on the dll and turns out that the version 2.81.1128.00 has been updated from version 1117 with an automatic update of windows through the security update reference KB92779. I uninstalled the security update and the msadox.dll turned back to version 1117 but the problem was still there. So i manually downloaded and installed again the security update changing again the dll to version 1128, but again access crashes.
Any idea? Thank you for any help.
Jacopo

[ADezii]

Dear Adezii,
thank you for the reply. I concentrated on the dll and turns out that the version 2.81.1128.00 has been updated from version 1117 with an automatic update of windows through the security update reference KB92779. I uninstalled the security update and the msadox.dll turned back to version 1117 but the problem was still there. So i manually downloaded and installed again the security update changing again the dll to version 1128, but again access crashes.
Any idea? Thank you for any help.
Jacopo

Everything else seems sound - when you type ADOX. does the Intellisense feature work, namely the Properties, Methods, etc. of ADOX automatically appear in a Drop Down.

[Jacopo]

Again thank you for the reply.
And yes the intellisense works. I thought about it so I retyped all the declarations. I also tried to move the function to a brand new module thinking that maybe that specific module could be corrupted (never heard about it but when you are desperate...).
Now I have reinstalled again Office, this time the US version. It is a student version but complete. I shouldn't use it to develop commercial products but hell I paid good money to Microsoft for the italian one.
Anyway the problem is still there, even if I tried to delete the reference and point again to it, and also changed the order of the references (sometimes it affects the behavior).
Quite honestly I am thinking to bypass ADOX considering that the same can be done without it. It might be a problem in the future if I will have to modify the database on site when the customer already has data in it, because there ADOX comes handy, but I will think about it when it comes.
Yet, even if I don't want to bug you too much, I would be grateful if you come up with some other ideas.
Thank you for your time Adezii.
Jacopo

[ADezii]

Again thank you for the reply.
And yes the intellisense works. I thought about it so I retyped all the declarations. I also tried to move the function to a brand new module thinking that maybe that specific module could be corrupted (never heard about it but when you are desperate...).
Now I have reinstalled again Office, this time the US version. It is a student version but complete. I shouldn't use it to develop commercial products but hell I paid good money to Microsoft for the italian one.
Anyway the problem is still there, even if I tried to delete the reference and point again to it, and also changed the order of the references (sometimes it affects the behavior).
Quite honestly I am thinking to bypass ADOX considering that the same can be done without it. It might be a problem in the future if I will have to modify the database on site when the customer already has data in it, because there ADOX comes handy, but I will think about it when it comes.
Yet, even if I don't want to bug you too much, I would be grateful if you come up with some other ideas.
Thank you for your time Adezii.
Jacopo

I've looked at this from every conceivable angle, and still I come up with a big ZERO. Just one more, last ditch effort. Run the code directly, outside of the Function Procedure, so no Argument (Table Name) is being passed to the Function, so the Object Variable assignment is set directly to a Table Name. Something this drastic as a System Crash on this specific line may indicate something similar to a memory overwrite where strName is overwriting an existing and occupied memory space. I know it sounds crazy, but please just try it. Let me know how you make out.

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim strSql As String
 
'Delete all records.
strSql = "DELETE FROM Table1;"
CurrentProject.Connection.Execute strSql
 
'Find and reset the AutoNum field.
cat.ActiveConnection = CurrentProject.Connection
 
Set tbl = cat.Tables("Table1")
 
For Each col In tbl.Columns
  If col.Properties("Autoincrement") Then
    col.Properties("Seed") = 1
    DeleteAllAndResetAutoNum = True
  End If
Next

[Jacopo]

Dear Adezii,
I tried what you said, but no luck. Sorry I gave you headache for this. You have been very very kind in trying and helping me.
The development of my program is still far from being done so I wouldn't surprise that before the end, for some odd reasons it goes back working. By the way, I have a back up (though too behind in the development to be used) where it works, so the dll are fine. I tried cleaning that file and import all from the new one into it but again it crashes. I throw the towel at the moment.
Again ty for all your efforts, but i don't want you to get crazy over it.
Jacopo