Issue with code after splitting DB (1 Viewer)

[tmyers]

OK, found the code back in 2012

Function GetSeedADOX(strTable As String, Optional ByRef strCol As String) As Long
    'Purpose:   Read the Seed of the AutoNumber of a table.
    'Arguments: strTable the table to examine.
    '           strCol = the name of the field. If omited, the code finds it.
    'Return:    The seed value.
    Dim cat As New ADOX.Catalog 'Root object of ADOX.
    Dim tbl As ADOX.Table       'Each Table in Tables.
    Dim col As ADOX.Column      'Each Column in the Table.
  
    'Point the catalog to the current project's connection.
    Set cat.ActiveConnection = CurrentProject.Connection
    Set tbl = cat.Tables(strTable)
  
    'Loop through the columns to find the AutoNumber.
    For Each col In tbl.Columns
        If col.Properties("Autoincrement") Then
            strCol = "[" & col.Name & "]"
            GetSeedADOX = col.Properties("Seed")
            Exit For    'There can be only one AutoNum.
        End If
    Next
  
    'Clean up
    Set col = Nothing
    Set tbl = Nothing
    Set cat = Nothing
End Function

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, lngSeed
            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

Yup!
It has been working flawlessly (until I split the database). As I said above, simply converting that temp table back to a local table fixed the problem it was having.

 

[theDBguy]

Yup!
It has been working flawlessly (until I split the database). As I sad above, simply converting that temp table back to a local table fixed the problem it was having.

Please don't forget what I sai about Autonumber fields and sequential numbers.

 

[tmyers]

Please don't forget what I sai about Autonumber fields and sequential numbers.

Nope!

It was my understanding from Pat that the resetseed module's purpose is to reset those numbers so that they are always sequential and start at 1. Why Pat went with that method rather than just having a number field with 1-10, I am not sure. I would assume it is because based on Pat process, you don't want empty fields, so you couldn't have preloaded numbers otherwise it would mess the report up.

Once again just being honest, I don't 100% understand what all the code Pat allowed me to use is doing and why certain things are done the way they are.

 

[Gasman]

Yup!
It has been working flawlessly (until I split the database). As I sad above, simply converting that temp table back to a local table fixed the problem it was having.

Oh, I have no doubt that Pat's code would work, I was more interested in the implementation of it.

 

[theDBguy]

Nope!

It was my understanding from Pat that the resetseed module's purpose is to reset those numbers so that they are always sequential and start at 1. Why Pat went with that method rather than just having a number field with 1-10, I am not sure. I would assume it is because based on Pat process, you don't want empty fields, so you couldn't have preloaded numbers otherwise it would mess the report up.

Once again just being honest, I don't 100% understand what all the code Pat allowed me to use is doing and why certain things are done the way they are.

Hi. Pat's code is simply resetting the starting number to 1, but it is relying on the Autonumber field to produce the next values. Autonumber fields have been know to produce negative numbers.

As for having empty fileds, I wasn't suggesting pre-filling the table with the numbers. I was saying loop through the records and assign a sequential number for each one, after you have populated the table.

 

[tmyers]

Hi. Pat's code is simply resetting the starting number to 1, but it is relying on the Autonumber field to produce the next values. Autonumber fields have been know to produce negative numbers.

As for having empty fileds, I wasn't suggesting pre-filling the table with the numbers. I was saying loop through the records and assign a sequential number for each one, after you have populated the table.

Ah I understand now. Sorry, it's early and I still have a mug of coffee to down.

 

[moke123]

Following up on what DBG said, if this is a multi user db using a temp table in the backend may cause problems when 2 people are using it.

I use a local temp database (code stolen from DBG's website many years ago and slightly modified) and a temp table. I usually create the temp database when the app is opened and then create the temp table on demand. I use a template table to create the temporary table in the temp DB. (just makes maintenance and modification easier) Once done with the temp table it is deleted. When needed again it is recreated and the autonumber field starts at 1 again.

 

[tmyers]

Following up on what DBG said, if this is a multi user db using a temp table in the backend may cause problems when 2 people are using it.

I use a local temp database (code stolen from DBG's website many years ago and slightly modified) and a temp table. I usually create the temp database when the app is opened and then create the temp table on demand. I use a template table to create the temporary table in the temp DB. (just makes maintenance and modification easier) Once done with the temp table it is deleted. When needed again it is recreated and the autonumber field starts at 1 again.

I see. Does converting the temp table to a local table prevent that issue? Also the amount of concurrent users I ever expect to have at once, is maybe 3. The chances of someone trying to generate the report at the same time would be very low.

 

[Pat Hartman]

I just unlinked my temp table, converted it to local, then redid the relationship and it works again.

Relationships can only be defined between tables in the SAME database so the relationship you defined is documentation only. You should see that you cannot enforce RI and that is the entire point of defining relationships. The reason is quite logical. The database engine enforces the relationship. If the tables are not within its scope, it can't enforce anything. And if you didn't enforce RI, its just documentation wherever the tables are.

In the sample I gave you, I believe the table is local so that's why it works in the example. You can modify a linked table but not with this code.

For this particular table, you do want it to be local. You don't want it to be shared because It can't support multiple users at one time. I should probably mention that in the sample. An alternative is to use a technique I have suggested a number of times for handling temp data and that is to completely replace the "temp" BE each time you start the process. Given that this table never has more than 10 records, it would be a long time before you experienced bloat but if you want more details on the template temp database, I can provide it.

 

[tmyers]

Relationships can only be defined between tables in the SAME database so the relationship you defined is documentation only. You should see that you cannot enforce RI and that is the entire point of defining relationships. The reason is quite logical. The database engine enforces the relationship. If the tables are not within its scope, it can't enforce anything. And if you didn't enforce RI, its just documentation wherever the tables are.

In the sample I gave you, I believe the table is local so that's why it works in the example. You can modify a linked table but not with this code.

For this particular table, you do want it to be local. You don't want it to be shared because It can't support multiple users at one time. I should probably mention that in the sample. An alternative is to use a technique I have suggested a number of times for handling temp data and that is to completely replace the "temp" BE each time you start the process. Given that this table never has more than 10 records, it would be a long time before you experienced bloat but if you want more details on the template temp database, I can provide it.

Dont worry about it Pat.
I am going to have to rework my whole app and will see what happens when I return to this.

 

[Pat Hartman]

Why would you have to rework the whole app to make a temp table local?

 

[tmyers]

Why would you have to rework the whole app to make a temp table local?

Sorry, was referencing another thread I had going.