@Pat Hartman you might know this one, as you had originally given me and assisted with this code.
Since I split the database, CurrentProject.Connection.Execute strsql throws the error "Cannot execute data defintion statements on linked data sources".
Based on what I am currently reading, that line doesn't work on linked tables. Anyone offer insight to fixing this? I am actively reading around on how to do it as well.
Code:
Function ResetSeed(strTable As String) As String
'Purpose: Reset the Seed of the AutoNumber, using ADOX.
Dim strAutoNum As String 'Name of the autonumber column.
Dim lngSeed As Long 'Current value of the Seed.
Dim lngNext As Long 'Next unused value.
Dim strsql As String
Dim strResult As String
lngSeed = GetSeedADOX(strTable, strAutoNum)
If strAutoNum = vbNullString Then
strResult = "AutoNumber not found."
Else
lngNext = Nz(DMax(strAutoNum, strTable), 0) + 1
If lngSeed = lngNext Then
strResult = strAutoNum & " already correctly set to " & lngSeed & "."
Else
Debug.Print "lngnext = " & lngNext, "lngSeed = "; lngSeed
'strSQL = "ALTER TABLE [" & strTable & "] ALTER COLUMN [" & strAutoNum & "] COUNTER(" & lngNext & ", 1);"
strsql = "ALTER TABLE [" & strTable & "] ALTER COLUMN " & strAutoNum & " COUNTER(" & lngNext & ", 1);"
Debug.Print strsql
CurrentProject.Connection.Execute strsql
strResult = strAutoNum & " reset from " & lngSeed & " to " & lngNext
End If
End If
ResetSeed = strResult
End Function
Since I split the database, CurrentProject.Connection.Execute strsql throws the error "Cannot execute data defintion statements on linked data sources".
Based on what I am currently reading, that line doesn't work on linked tables. Anyone offer insight to fixing this? I am actively reading around on how to do it as well.