469,962 Members | 2,778 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,962 developers. It's quick & easy.

Help With Adox And Autonumber

10
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
Jul 24 '07 #1
6 3485
ADezii
8,800 Expert 8TB
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).
Jul 25 '07 #2
Jacopo
10
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
Jul 25 '07 #3
ADezii
8,800 Expert 8TB
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.
Jul 25 '07 #4
Jacopo
10
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
Jul 25 '07 #5
ADezii
8,800 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Dim cat As New ADOX.Catalog
  2. Dim tbl As ADOX.Table
  3. Dim col As ADOX.Column
  4. Dim strSql As String
  5.  
  6. 'Delete all records.
  7. strSql = "DELETE FROM Table1;"
  8. CurrentProject.Connection.Execute strSql
  9.  
  10. 'Find and reset the AutoNum field.
  11. cat.ActiveConnection = CurrentProject.Connection
  12.  
  13. Set tbl = cat.Tables("Table1")
  14.  
  15. For Each col In tbl.Columns
  16.   If col.Properties("Autoincrement") Then
  17.     col.Properties("Seed") = 1
  18.     DeleteAllAndResetAutoNum = True
  19.   End If
  20. Next
Jul 25 '07 #6
Jacopo
10
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
Jul 26 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Randy | last post: by
5 posts views Thread by Wayne Wengert | last post: by
8 posts views Thread by T Clancey | last post: by
3 posts views Thread by Miro | last post: by
1 post views Thread by rainxy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.