reset autonumber help

[davyre]

Hi, I searched this forum and found the solution

http://www.tek-tips.com/viewthread.cfm?qid=1050735

by flyover789, but when I applied the code, I got the error runtime '3611' Cannot execute data definition statements or linked data sources.

DoCmd.RunSQL "ALTER TABLE TmpPartsTableALL DROP CONSTRAINT [TmpPartID];"

I used the first piece of code. What could be my issue here? Thanks for help!

 

[dhookom]

Apparently the table is linked? If so, you need to run the code in the non-linked database file. You could als try to create a database object to the back-end database and use the Execute method.

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

Thanks dhookom for the reply. But im beginner here and i am trying to modify someone's coding which is not working. At first, he used the code like this:

DoCmd.RunSQL "Alter Table TmpPartsTableALL Alter Column TmpPartID Counter(1,1)"

and this code is also not working. Can you see why? Or is it the same issue as the code above? Thanks

 

[davyre]

and is there any difference between

[highlight #FCE94F]DoCmd.RunSQL "Alter Table TmpPartsTableALL Alter Column TmpPartID Counter(1,1)"[/highlight]

and

[highlight #AD7FA8]CurrentDB.Execute "Alter Table TmpPartsTableALL Alter Column TmpPartID Counter(1,1)"[/highlight]
?

 

[dhookom]

Is the table in the current Access file or is it linked from another Access file.

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

the table is in the current access file.

 

[dhookom]

Create a new table with an autonumber primary key then add a few records. Then try the same code on the new table.

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

Well it worked! Apparently maybe I opened the table so the code wont work. When I closed the table, it worked. Thanks!

 

[davyre]

duh my bad. The query still not working. The table seems linked, but I cannot find where it linked. I can assure you that the table is in the current access file. The full code is

DoCmd.SetWarnings False
DoCmd.RunSQL "delete * From TmpPartsTableALL"
DoCmd.SetWarnings True
DoCmd.RunSQL "Alter table TmpPartsTableALL Alter column TmpPartID counter(1,1)"

Can you help me?

 

[dhookom]

You should be able to open the linked table manager to determine the location of the file containing the table. This code should also work and will find the file:

Private Sub cmdResetAutonumber_Click()
    Dim dbThis As DAO.Database
    Dim dbRemote As DAO.Database
    Dim td As DAO.TableDef
    Dim strTable As String
    Dim strPKField As String
    strTable = "[tblPassers]"
    strPKField = "[PassID]"
    Set dbThis = CurrentDb
    Set td = dbThis.TableDefs(strTable)
    Set dbRemote = OpenDatabase(Mid(td.Connect, 11))
    dbRemote.Execute "delete * From " & strTable
    dbRemote.Execute "Alter table " & strTable & " Alter column " & strPKField & " counter(1,1)"
    Set td = Nothing
    Set dbThis = Nothing
    Set dbRemote = Nothing
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

is the linked file name filename_be.accdb? The ones with _be at the end of the file name? And after I found it, what should I do? And what to do with the code you gave me above? Sorry for the trouble

 

[dhookom]

I created a form and added a command button named cmdResetAutonumber. I then added the code to the On Click event of the command button. You would need to change the table and field names at

    strTable = "[tblPassers]"
    strPKField = "[PassID]"

You should then be able to view the form and click the button which will delete all the records from the table and reset the autonumber.

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

gee that worked! Thanks heaps!

 

[davyre]

just out of curiosity, why the codes that I used wasnt working (the DoCmd.RunSQL and CurrentDB.Execute)? Is it because the table is linked? So I cant use those two simple codes if the table is linked? Can I remove the link? What is the importance of linking table to another file? Sorry if OOT. Thanks

 

[dhookom]

The issue was caused by the table being linked. You see the same type of behavior if you try to make design changes to a linked table. You have to open the database file containing the actual table. That is what the code that I provided does with this line:

Set dbRemote = OpenDatabase(Mid(td.Connect, 11))

Duane
Hook'D on Access
MS Access MVP