Issue with code after splitting DB

[tmyers]

@Pat Hartman you might know this one, as you had originally given me and assisted with this 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.

 

[Gasman]

Open the BE directly?

 

[tmyers]

Open the BE directly?

I tried:

 Dim mydb As Database
    
    Set mydb = OpenDatabase("filepath")
    mydb.execute strsql

But then I got an "Invalid field data type" error.

 

[moke123]

Assuming this is a one-off operation what about making the table local again, run the procedure and then split it again

 

[tmyers]

Assuming this is a one-off operation what about making the table local again, run the procedure and then split it again

I initially thought about that. Converting this table back to a local table, however it is part of a relationship and wouldn't let me. This code will be run quite a lot, so doing a one off fix wouldn't work.

 

[Gasman]

Where are you setting strAutonum?

 

[Gasman]

I initially thought about that. Converting this table back to a local table, however it is part of a relationship and wouldn't let me. This code will be run quite a lot, so doing a one off fix wouldn't work.

I have only ever used it after testing, just to start afresh at 1.
Why do you think you need to keep setting it?

 

[tmyers]

Where are you setting strAutonum?

I believe its:

If strAutoNum = vbNullString Then
        strResult = "AutoNumber not found."
    Else
        lngNext = Nz(DMax(strAutoNum, strTable), 0) + 1

I am not the original writer of this code. This entire module was kindly provided by Pat Hartman

 

[tmyers]

I have only ever used it after testing, just to start afresh at 1.
Why do you think you need to keep setting it?

This module is resetting the autonumber field of a temp table to allow me the ability to assign values on 1-10 and set field names for a report based off a crosstab query.

 

[tmyers]

I think I fixed it. I was unaware you could have relationships between local and linked tables. I just unlinked my temp table, converted it to local, then redid the relationship and it works again.

Even when I think I know the basic in and outs of Access, I get proven wrong.

 

[theDBguy]

This module is resetting the autonumber field of a temp table to allow me the ability to assign values on 1-10 and set field names for a report based off a crosstab query.

Two things/questions. Why is a temp table part of any relationship and will it always contain only 10 records?

Usually, a temp table belongs in the front end and sometimes in a separate/external db.

If the temp table only contains 10 records, it might be easier to use a separate Number field and use code to assign a sequential number to the records.

Autonumber is not guaranteed to produce sequential numbers anyway.

 

[theDBguy]

I think I fixed it. I was unaware you could have relationships between local and linked tables. I just unlinked my temp table, converted it to local, then redid the relationship and it works again.

Even when I think I know the basic in and outs of Access, I get proven wrong.

Interesting, you cannot establish a true relationship between local and linked tables, so I'm not sure what you created.

 

[tmyers]

Two things/questions. Why is a temp table part of any relationship and will it always contain only 10 records?

Usually, a temp table belongs in the front end and sometimes in a separate/external db.

If the temp table only contains 10 records, it might be easier to use a separate Number field and use code to assign a sequential number to the records.

Autonumber is not guaranteed to produce sequential numbers anyway.

To be honest, I dont know why it is that way. Pat originally had helped me with this and it is entirely based off a sample he had given me.
I may have done the simple 1-1 relationship because his sample had it that way. I would have to find it and look again.

Looking at it, the relationship is just allowing me to pull the names of contractors over. It is just a 1-1 with nothing else checked.

 

[theDBguy]

To be honest, I dont know why it is that way. Pat originally had helped me with this and it is entirely based off a sample he had given me.
I may have done the simple 1-1 relationship because his sample had it that way. I would have to find it and look again.

Looking at it, the relationship is just allowing me to pull the names of contractors over. It is just a 1-1 with nothing else checked.

Do me two favors, please. When you find the original thread where Pat gave the sample, please post the link here. And, try removing the relationship and see if it breaks your db or code.

 

[Gasman]

I believe its:

If strAutoNum = vbNullString Then
        strResult = "AutoNumber not found."
    Else
        lngNext = Nz(DMax(strAutoNum, strTable), 0) + 1

I am not the original writer of this code. This entire module was kindly provided by Pat Hartman

Ok, too clever for me, as you pass strAutoNum to the GetSeed function and the result is in lngSeed, not the other way around.?
I know you can have parameters that can get changed in functions, but I have normally avoided those and just worked with the returned values of functions.

Oh, and Pat is a lady and a she.

 

[tmyers]

Ok, too clever for me, as you pass strAutoNum to the GetSeed function and the result is in lngSeed, not the other way around.?
I know you can have parameters that can get changed in functions, but I have normally avoided those and just worked with the returned values of functions.

Oh, and Pat is a lady and a she.

Well darn, one time I assumed a person was a he/she I get it wrong. Sorry Pat!

Do me two favors, please. When you find the original thread where Pat gave the sample, please post the link here. And, try removing the relationship and see if it breaks your db or code.

Sure thing!

Using Crosstab Query for Report

Got it! Public Sub Form_Open(Cancel As Integer) Dim strContractor As String, strSql As String, strQuery As String strContractor = Contractorlist() strQuery = "ContractorCountQry_Crosstab" strSql = "PARAMETERS [Forms]![JobQuote]![JobID] Short;" strSql = strSql & " TRANSFORM...

www.access-programmers.co.uk

I am pretty sure that is where Pat originally posted the example.

I deleted that relationship, and turns out it didn't actually do anything and wasn't needed. Must have been something I did thinking it needed to be done.

 

[theDBguy]

Well darn, one time I assumed a person was a he/she I get it wrong. Sorry Pat!


Sure thing!

Using Crosstab Query for Report

Got it! Public Sub Form_Open(Cancel As Integer) Dim strContractor As String, strSql As String, strQuery As String strContractor = Contractorlist() strQuery = "ContractorCountQry_Crosstab" strSql = "PARAMETERS [Forms]![JobQuote]![JobID] Short;" strSql = strSql & " TRANSFORM...

www.access-programmers.co.uk

I am pretty sure that is where Pat originally posted the example.

I deleted that relationship, and turns out it didn't actually do anything and wasn't needed. Must have been something I did thinking it needed to be done.

Thanks for posting the link.

 

[Gasman]

I couldn't see that code? plus I cannot think why it would be used with a crosstab query?

 

[tmyers]

I couldn't see that code? plus I cannot think why it would be used with a crosstab query?

In Pats example db, its the mADOfunctions module.

What it is doing is taking values from the crosstab, assigning them to a temp table to allow me to "hardcode" headers for my report. My report headers are labels 1-10 and when the report loads, their caption is set to the text values of 1-10 in the temp table.

 

[Gasman]

OK, found the code back in 2012 and indeed the parameter is altered because of ByRef ?

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