What is the DAO equal for this ADO Change Autocounter Seed code? (1 Viewer)

Rx_

Nothing In Moderation
Local time
Today, 09:51
Joined
Oct 22, 2009
Messages
2,803
Our Citrix ADO object isn't up to date with an auto update for our development PC ADO object, among some other issues today.

I need to unselect my Tools Reference to ADO to fix some issues today in the short term. This code references ADO. Does anyone have the equal for DAO handy? See line 30 - Thanks

Code:
Public Sub ChangeSeedAutocounter(strTableName As String _
                           , strFieldName As String _
                           , lngStartAt As Long _
                           , intIncrementBy As Integer)
  ' 6/30/2015 This is the OLD Rule Engine - removing
  ' will not work on linked tables
          Dim strSQL As String
          'Build the SQL statement
          On Error GoTo errorTRap
10        strSQL = "ALTER TABLE " & strTableName & _
                   " ALTER COLUMN " & strFieldName & _
                   " COUNTER (" & lngStartAt & "," & intIncrementBy & ")"
          'Execute the SQL statement
30        CurrentProject.Connection.Execute strSQL, , adCmdText
          Exit Sub
errorTRap:
    Debug.Print "Sub ChangeSeedAutocounter in Rule engine" & Err.Description
    Err.Clear
End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:51
Joined
Aug 30, 2003
Messages
36,118
Shot in the dark, but does this work?

CurrentDb.Execute strSQL, dbFailOnError
 

MarkK

bit cruncher
Local time
Today, 08:51
Joined
Mar 17, 2004
Messages
8,178
Curious to hear. I thought you couldn't run data definition SQL in DAO.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:51
Joined
Aug 30, 2003
Messages
36,118
This is from a utility db I sent to a client to add a field to his back end:

Code:
    Dim strSql As String
    Dim db As DAO.Database
    
    Set db = DBEngine.Workspaces(0).OpenDatabase("PathToBackEndFile")

    strSql = "ALTER TABLE tbl_customers ADD COLUMN SendEmailStatements YESNO;"
    db.Execute strSql, dbFailOnError
    Set db = Nothing
    MsgBox "New field added"
 

Rx_

Nothing In Moderation
Local time
Today, 09:51
Joined
Oct 22, 2009
Messages
2,803
Thanks, I was going that direction, could not find an example anywhere. Just amazed that the ADO was very easy to find on Google search, but not a DAO (or ACE) example. If my ReDim Memory serves my trivia indicator right, was this something added to the ACE engine for a later versions?

Just for others to follow, it is usually good to delete the existing data first, then use the reset autocounter - empty table with 1 as first number shown below:
Code:
Public Sub RE_ResetLocalTableAutoNumber(LocalTableName As String, AutonumberFieldName As String)
' This will delete the existing data then reset / reseed the autocounter / autonumber field back to 1
' warning this will NOT work on LINKED SQL Server tables, only works on local Access tables
' warning, this will error if the table is currently being edited e.g. The database engine could not lock table "Mental Note to self: don't be editing the table's live data when running this code"
' Example of code to run:   RE_ResetLocalTableAutoNumber "RE_1Seg_datapull", "ID_RE1seg_Datapull"
          On Error GoTo errorTRap
10        DoCmd.SetWarnings False
20        DoCmd.RunSQL ("Delete From " & LocalTableName)
30        Call ChangeSeedAutocounter(LocalTableName, AutonumberFieldName, 1, 1)
40        DoEvents
50        DoCmd.SetWarnings True
Exit Sub
errorTRap:
       Debug.Print "RE_ResetLocalTableAutoNumber " & Err.Description
       Err.Raise Err.Number
       Err.Clear
       DoCmd.SetWarnings True
End Sub

Code:
Public Sub ChangeSeedAutocounter(strTableName As String _
                           , strFieldName As String _
                           , lngStartAt As Long _
                           , intIncrementBy As Integer)
  ' 6/30/2015 This is the OLD Rule Engine - removing
  ' will NOT work on linked tables
          Dim strSQL As String '
          Dim Response
          'Build the SQL statement
          On Error GoTo errorTRap
10        strSQL = "ALTER TABLE " & strTableName & _
                   " ALTER COLUMN " & strFieldName & _
                   " COUNTER (" & lngStartAt & "," & intIncrementBy & ")"
          'Execute the SQL statement
30        'CurrentProject.Connection.Execute strSQL, , adCmdText
            ' NON ADO option to the code above
            DBEngine(0)(0).Execute strSQL, dbFailOnError
Exit Sub
errorTRap:
    ' this was having an error if the Rules Review form appeared with rule eval of false
    ' that table was already in ue by another user
    Debug.Print "Sub ChangeSeedAutocounter in Rule engine" & Err.Description

End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:51
Joined
Aug 30, 2003
Messages
36,118
By the way, noting your comment "will NOT work on linked tables" you'll see that mine does, by setting the db variable to the back end file.
 

Rx_

Nothing In Moderation
Local time
Today, 09:51
Joined
Oct 22, 2009
Messages
2,803
Great point, I knew it would work for linked tables.... except for Autocounter reset.
Now, that being said, my code and bad habits for this DB started with an Access 2000 being upgraded twice.
So, I was just rightfully scolded about how it isn't necessary to use DoCmd.SetWarnings False for the execute - and how dangerous it is to have an error with it set to False. This is something I have been copying and pasting since I converted some of the code from stored queries of the previous programmer. What bad habits I tend to have.

So, will my code work on a Linked Table (SQL Server?) to reseed?
ran RE_ResetLocalTableAutoNumber "Sys_Info", "ID_Sys_Info"
on line 20 DoCmd.RunSQL ("Delete From " & LocalTableName)
got the error message:
RE_ResetLocalTableAutoNumber Could not delete from specified tables.

My Linked tables use SQL Server Client 11.0
Do you get a different result?
 

Rx_

Nothing In Moderation
Local time
Today, 09:51
Joined
Oct 22, 2009
Messages
2,803
Dim strSQL As String
On Error GoTo errorTRap
strSQL = "Delete From " & LocalTableName
20 DoCmd.RunSQL ("Delete From " & LocalTableName)
' YES Line 20 does work on SQL Server Linked Tables (as permissions allow)
30 Call ChangeSeedAutocounter(LocalTableName, AutonumberFieldName, 1, 1)
40 ' The Change seed, Turncate Table or other variations - can't get this to work in DAO.
Exit Sub
errorTRap:
Debug.Print "RE_ResetLocalTableAutoNumber " & Err.Description
Err.Raise Err.Number
Err.Clear
End Sub
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:51
Joined
Aug 30, 2003
Messages
36,118
I never tried that with a SQL Server back end. I wouldn't expect it to fail at the delete step though. Would a pass through query be an option?
 

Rx_

Nothing In Moderation
Local time
Today, 09:51
Joined
Oct 22, 2009
Messages
2,803
I must apologize! Today was very busy. Five years ago I updated someone elses code and left a type Integer in a list box for an autocouther key. Yesterday, at 1:45 PM, a user entered a new record that exceeded the value of an Integer. Nobody botherd to tell me until this morning after hundreds of record were attempted to be entered. Bad news... wrong data type but it worked until today. Good News.. I am still on the same contract to fix my mistake.

Let me delete the entry right above yours. It was for a Read Only table that records the free memory on the server every day. Something I wrote to keep an eye on the system. For anyone else following this thread, Rx_ tested a Delete statement on a Read Only table and was surprised to see an error. DOH!!

OK, I found a table that was not Read Only and ran the exact same code using the DoCmd.RunSQL ("Delete From " & LocalTableName)
- and that worked perfectlly followed by the
DBEntine(0)(0).Execute as shown above does in fact delete all of the records.
However, it does not reseed the counter.
In TSQL Server, the statement: Truncate Table R_Rules would both delete the data and automatically reseed the identity column back to 1.

Only using DAO, I have not been able to figure out how to use the Truncate.
DAO limites the first word to the typical Select, Delete, Insert...
 

Users who are viewing this thread

Top Bottom