Form seems to have table locked even after closing.

[Papa_Bear1]

Typically, when I need Autonumber, I'll create a template table to reset it, since it will inherently increment forever and I don't want to even consider how long that would take to become a problem. Ha.

This has worked without issue for a long time. Today, however, I've run into a situation where I am on a form that has the data table underlying it (a subform actually). So, ignoring the "DELETE * FROM ..." option since that doesn't get that Autonumber reset, I want to delete the underlying data table altogether and then re-display everything. I simply CANNOT get Access to let go of the table.

I've tried:
> Changing the subform's record source to vbNullString (and to "") first - and then closing the form, but no joy.
> Moving the code that deletes the table to an entirely separate form (the calling form), but still no joy. (I really thought this would HAVE to work. Yikes...)

Is it really not possible to delete a table even after the form is closed?? I really don't know what could possibly have its hooks into it. There is literally nothing else open and nothing else uses that table anyway. I'm going to place a button on the calling form to clear that table - but that is quite ugly to me - as it makes more sense to the user to clear the data they're looking - IMHO. We'll see if that works, but who knows... Ha.

Ideas?

 

[MajP]

is it really not possible to delete a table even after the form is closed?? I really don't know what could possibly have its hooks into it.

To me that sounds like some form of curruption.
Compact and repair is unlikely to help but worth trying
I would try a decompile and recompile and see if that helps

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

www.fmsinc.com

May also want to create a blank db then reimport in all the objects.
May also want to save the form to text and import from text

Crash When Add Option Group

Hi, I am working on a form, everytime I add an Option Group with the value to store the selection to a table and save the form Access Crashes. If I create a new form I can create the option group no problem and save it. Can someone tell me why this might be happening? Thanks. Fen How

www.access-programmers.co.uk

 

[Papa_Bear1]

To me that sounds like some form of curruption.
Compact and repair is unlikely to help but worth trying

Good point.

> I think I can see why the 1st thing I tried wouldn't work - as it doesn't really let go until you actually leave the form.
> I think the 2nd option not working was due to a dumb mistake on my part (--- this is probably the one that made you figure something is really wrong and in need of serious repair - and I would agree.) Turns out, I accidentally left the recordset open that was checking if it was empty - maybe because I had not had my coffee yet - not sure - . (The alternate approach I was taking was to have the start [calling] form's open process to check if there were no rows in that data table, and then reset it with the template if the data has been cleared.) Once I fixed that little nugget of ensuring the checking recordset was closed - it worked. So, at least the other form CAN do what is needed.

As far as that first option - I've heard of that working - and I may have even succeeded with that once - but not here. It seems it will not let go if the code that is trying to delete that table is within the form that has ties to it - even if it is a parent form of a sub-form, all of which has been closed. Oh well. Once again - I have something working - although not as I intended.

btw - I have never tried a "decompile". Maybe I'll go down that rabbit hole, just to see what is involved and be in a better position if/when I need it!

Thanks again for the ideas/help!

 

[isladogs]

For info, are you aware that a subform opens before the main form opens and closes after the main form closes

 

[MajP]

btw - I have never tried a "decompile". Maybe I'll go down that rabbit hole, just to see what is involved and be in a better position if/when I need it!

From what you were saying my assumption was the form was closed and all connection to the table were closed. This may not have been a good assumption. But assuming it was true, that was why I was suggesting it could be corruption.
However although @isladogs did not chime in he does have a utility that can help show connections, and would be useful to diagnose something like this. You have a connection when you think you should not.

Available Connections

This utility is used to monitor the number of open databases/tables and prevent 'cannot open any more databases' errors.

www.isladogs.co.uk

If your database ever starts hanging, crashing, or doing real weird stuff then a decompile and recompile is the best solution. And you want to do it before the db gets so corrupted that no longer can be opened.

 

[jwcolby54]

Typically, when I need Autonumber, I'll create a template table to reset it, since it will inherently increment forever and I don't want to even consider how long that would take to become a problem. Ha.

This has worked without issue for a long time. Today, however, I've run into a situation where I am on a form that has the data table underlying it (a subform actually). So, ignoring the "DELETE * FROM ..." option since that doesn't get that Autonumber reset, I want to delete the underlying data table altogether and then re-display everything. I simply CANNOT get Access to let go of the table.

I've tried:
> Changing the subform's record source to vbNullString (and to "") first - and then closing the form, but no joy.
> Moving the code that deletes the table to an entirely separate form (the calling form), but still no joy. (I really thought this would HAVE to work. Yikes...)

Is it really not possible to delete a table even after the form is closed?? I really don't know what could possibly have its hooks into it. There is literally nothing else open and nothing else uses that table anyway. I'm going to place a button on the calling form to clear that table - but that is quite ugly to me - as it makes more sense to the user to clear the data they're looking - IMHO. We'll see if that works, but who knows... Ha.

Ideas?

Is the form bound to the table? Is the form actually closing? Is it the subform's table that you are trying to delete? As @isladogs pointed out, the subform closes after the main form so if the subform is bound to the table, the main form can't delete the table because the subform is still bound to it, and the subform is not closed yet. You might consider unbinding the subform before the close starts, then have the subform itself delete the table. If the subform is the only object with a pointer to the table's recordset that might work.

Just out of curiosity, why are you doing this delete thing? If it is just to get an autonumber value of zero, you might want to use that code from (I believe) @MajP, set the autonumber to -1 (yes that is a thing) and then when the first record is inserted, it should pick up at 0.

The autonumber can "wrap around" numerically from the max value (2^32) to the highest (smallest?) negative value, working it's way back towards zero. If you set the seed to -1 and the autonumber increments by one for the next insertion...

 

[Papa_Bear1]

For info, are you aware that a subform opens before the main form opens and closes after the main form closes

Hmmm - no - but I suppose only because (until now?) it hasn't mattered.

Turns out - this problem isn't actually resolved. Interestingly - the first time I open the Start/Calling form, it CAN operate on that other subform's data table. But once I visit that other form/sub-form, THEN it won't. Ugh! This is so irritating. I'm not even in the SAME FORM and the other form and it's sub-form are closed. It just makes no sense for there to be ANY hooks in it. Apparently "Close" doesn't mean "Close".

 

[Papa_Bear1]

Is the form bound to the table? Is the form actually closing? Is it the subform's table that you are trying to delete? As @isladogs pointed out, the subform closes after the main form so if the subform is bound to the table, the main form can't delete the table because the subform is still bound to it, and the subform is not closed yet. You might consider unbinding the subform before the close starts, then have the subform itself delete the table. If the subform is the only object with a pointer to the table's recordset that might work.

Just out of curiosity, why are you doing this delete thing? If it is just to get an autonumber value of zero, you might want to use that code from (I believe) @MajP, set the autonumber to -1 (yes that is a thing) and then when the first record is inserted, it should pick up at 0.

The autonumber can "wrap around" numerically from the max value (2^32) to the highest (smallest?) negative value, working it's way back towards zero. If you set the seed to -1 and the autonumber increments by one for the next insertion...

> So - there is a form, bound to a table, and then its sub-form bound to another table. It is the sub-form's table that I'm trying to reset.
> Given that I can't even get this to work from an entirely separate form - after the parent and sub-form have been closed - means it isn't about the timing of parent and sub-form (right?)
> I am indeed doing the delete to reset the counter. In the past, when I've monkeyed around with trying to control an Autonumber field, bad things happened. (A case of trying to work around what Access is trying to for me...) If I can reliably simply reset it - that would be great. I've just never been able to do that. I'll take a look at that resource from @MajP for this.

 

[Papa_Bear1]

From what you were saying my assumption was the form was closed and all connection to the table were closed. This may not have been a good assumption. But assuming it was true, that was why I was suggesting it could be corruption.
However although @isladogs did not chime in he does have a utility that can help show connections, and would be useful to diagnose something like this. You have a connection when you think you should not.

I like the idea of knowing where the connections are... that seems to be the piece I'm missing --- will give this a try!

 

[Papa_Bear1]

I like the idea of knowing where the connections are... that seems to be the piece I'm missing --- will give this a try!

This is a great tool - but it seems to be aimed at external connections. In this case, I'm interested in internal dependencies - so it doesn't seem to show me how a form is 'connected' to a data table, for example. Amazing tool though @isladogs - wow!

 

[Pat Hartman]

Just out of curiosity, why are you doing this delete thing? If it is just to get an autonumber value of zero, you might want to use that code from (I believe) @MajP, set the autonumber to -1 (yes that is a thing) and then when the first record is inserted, it should pick up at 0.

It wasn't MagP who posted the code and it doesn't set the seed to -1. It finds the current seed and compares it to the max autonumber. Then updates the seed if necessary.

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

''--------------------------------------------------------------------------------
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

A simpler, but less useful version comes from Microsoft.

 

[Pat Hartman]

But the bottom line is - doing this is simply poor practice. Autonumbers have no intrinsic meaning and they have such a large range that I'm prettu sure you would never, ever, run out of numbers.

There are reasons for resetting the seed so that the first record added will be 1 but doesn't sound like yours falls into the "an expert would do this" category.

 

[jwcolby54]

It wasn't MagP who posted the code and it doesn't set the seed to -1. It finds the current seed and compares it to the max autonumber. Then updates the seed if necessary.

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

''--------------------------------------------------------------------------------
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

A simpler, but less useful version comes from Microsoft.

It never occurred to me to just find a property somewhere to set it. That is pretty cool!

 

[jwcolby54]

But the bottom line is - doing this is simply poor practice. Autonumbers have no intrinsic meaning and they have such a large range that I'm prettu sure you would never, ever, run out of numbers.

There are reasons for resetting the seed so that the first record added will be 1 but doesn't sound like yours falls into the "an expert would do this" category.

It is indeed poor practice. The autonumber is supposed to be just a surrogate Key.

 

[isladogs]

This is a great tool - but it seems to be aimed at external connections. In this case, I'm interested in internal dependencies - so it doesn't seem to show me how a form is 'connected' to a data table, for example. Amazing tool though @isladogs - wow!

It’s not intended to show the interrelationships between objects.
To do that, use the built-in Access dependency checker which shows the dependencies between tables, queries, forms and reports. The built-in version doesn’t include macros and modules but there are several tools that do so, both free and commercial.

 

[isladogs]

Just for the record, the code posted by @Pat Hartman in post #11 is by Allen Browne. It is part of a collection of ADOX functions which can be found at

Microsoft Access tips: ADO Programming Code Examples

VBA code using the ADOX library in Microsoft Access.

allenbrowne.com

 

[Gasman]

It is indeed poor practice. The autonumber is supposed to be just a surrogate Key.

True, but after testing on any table that did not have data to start with, I would always set it back to 1.

 

[jwcolby54]

True, but after testing on any table that did not have data to start with, I would always set it back to 1.

And I have never done so. I just pay absolutely no attention to its value. Its only use in my universe is as a unique record identifier, to be used as a foreign key in a child table.

In 32 bit Office, an autonumber is a 32 bit number.It can have 4 294 967 296 possible positive values. However as an autonumber hits it's max value it will wrap or roll over and start counting back down to zero. So... we have almost 8.3 BILLION possible values. In an empty table, there will never be a collision. In fact it simply isn't possible.

The max container size is 2^32 / 2, which is a windows limitation from the olden days. As such the database can contain no more than 2+ billion bytes of information. Sooooo.... If you had a table with the ONLY field being the pk autonumber, AND that was the only table in the database, AND each autonumber being 4 bytes, you could only have 2 billion records / 4 meaning you could only ever have 512 million records in the db, containing only the PK.

So... if the only use for the autonumber is the PK it represents, and given all of the above, why would I ever pay any attention to the actual value of the thing, or bother resetting it to zero before I inserted records into my table.

But that is just me. My daughter is obsessive compulsive. She has to have the bedding just so before she can go to sleep. There is no arguing with that, it's just who and how she is.

I suspect that you have a bit of OCD going on there.

Access limits

 

[MajP]

@Papa_Bear1
I agree with others that this design might not be the best, but understanding the problem regardless is important.
So when you close all forms / subforms connected to a table "closed is closed". And this should work. There may be a timing issue. Without seeing your code you may be calling the delete code prior to the form actually closing. As mentioned forms open and close from the inside out. So if the main form is bound to the table and your subform calls the code on its on close then the main form is still connected.

The reason I said it may be corruption and reason I mentioned this tool was that I had a similar issue. When I closed everything the table seemed still connected, and I could not figure it out. Now I cannot remember what I used but I either used @isladogs tool or another one to prove that the table was still connected. That suggested to me that the form was no longer visible but actually hanging open in the background. When I decompiled and recompiled that solved it. (Or I might have done as saveastext / loadfromtext cannot remember). What did not solve this problem was C/R and creating a brand new database and importing in all the objects. Chasing this problem was very frustrating.

Again, I do agree you should rethink this design, but to understand this problem maybe you can post the db. Again it is likely a timing issue or corruption IMO.

btw - I have never tried a "decompile". Maybe I'll go down that rabbit hole, just to see what is involved and be in a better position if/when I need it

Every access developer needs this in their toolbox! It is amazing this is not built in to the IDE. I do not even think MZTOOLS has a way to do this. This is a complete lifesaver at times and easy to do. I often use ActiveX controls like treeviews which are notorious for corrupting the application, so I do this routinely.

Once you find the path just paste the string into a text file an put on your dektop. Then drop the string in the search box in the left corner of windows.
Mine looks like
C:\Program Files\Microsoft Office\root\Office16\MSAccess.exe /decompile

 

[Gasman]

Once you find the path just paste the string into a text file an put on your dektop. Then drop the string in the search box in the left corner of windows.
Mine looks like
C:\Program Files\Microsoft Office\root\Office16\MSAccess.exe /decompile

Access VBA Savant

I actually used a Shortcut.