How to reset internal column count after deleting fields (or editing a field property) VB6

Hello Experts -
I have VB6 application that adds/deletes/modifies-properties of fields in an Access database (.mdb).
When a field is either deleted or modified, the internal column count doesn't reset.
I've run DBEngine.CompactDatabase in my VB6 code and this still doesn't clear the internal column count.
(Opening the d/b and manually running C/R does clear the internal column count.)

The result is I'm getting the 'too many fields' error when I attempt to add another field - even though there are physically < 255 fields

Is there a way to reset the internal column count programatically?
Else: I use the following code to delete/modify a field. Are there other ways to do this that will decrement the column count as well as delete/modify the field?

To Delete:
Set tdfDelete = dbDelete.TableDefs(sTable)
   ' Create and append field.
    With tdfDelete
        On Error Resume Next
        '02.27.14 to remove the index if it exists on this field
        Dim intI As Integer
        For intI = .Indexes.Count - 1 To 0 Step -1
            Debug.Print .Indexes(intI).Name
            If .Indexes(intI).Name = sFieldName Then
                .Indexes.Delete .Indexes(intI).Name
                Exit For
            End If
        Next intI
        '02.27.14 done
        .Fields.Delete sFieldName
    End With

Set tdfDelete = Nothing

Set dbDelete = Nothing

To Modify property:
Set tdf = db.TableDefs(sTable)
    With tdf
        For iCount = 0 To .Fields.Count - 1
            If UCase(.Fields(iCount).Name) = UCase(sField) Then
                If .Fields(iCount).AllowZeroLength Then
                    .Fields(iCount).AllowZeroLength = True
                End If
                Exit For
            End If
        Next iCount
    End With
    Set tdf = Nothing

k heitzsoftware developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris WatsonSoftware DeveloperCommented:
What happens if you use an ALTER TABLE statement?


Open in new window

k heitzsoftware developerAuthor Commented:
Hi Chris -
Thanks for the reply.
I changed code to use the ALTER TABLE statement w/ DROP COLUMN instead of Fields.Delete.
I verified the field was deleted.

However I still get the 'too many fields' error when I invoke Fields.Append to add a field.

I also tried changing my Fields.Append to ALTER TABLE .. ADD COLUMN - but still same results.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
My first question would be: Why are you adding/removing Fields from a table at runtime?

Can you show the full code example you're using for CompactDatabase? Remember that the db must be closed (i.e. not in use at all) in order to do this, so if your VB app is connecting to it, you won't be able to complete the operation.

You can only reset that internal counter by either (a) compacting the database or (b) creating a new database and importing the tables to that new database.

Instead of DBEngine.CompactDatabase, try running a command line to do that:

"full path to msaccess.exe" "full path to the database" /compact

Of course, this assumes that the machine has a copy of Access installed.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

k heitzsoftware developerAuthor Commented:
Hi Scott;

We alter the table structure at run time because we have .mdbs on varied customer systems who all have different data in the tables. Due to new requirements for our data, we need to occasionally change the table structure, but not the data underneath.

Here's my compact and repair code and I think you've pointed out the issue - The database is open when I invoke this code:

'            'compact & repair
'            'This statement creates a compact version of the  database
'            Dim sTempMDB As String
'            sTempMDB = Replace(sFileName, ".mdb", "_temp.mdb")
'            DBEngine.CompactDatabase sFileName, sTempMDB
'            fso.CopyFile sTempMDB, sFileName, True
'            fso.DeleteFile sTempMDB

I'll start by closing the d/b before compacting, and if that doesn't solve I'll try the command line.

If the user has the db.engine installed (we distribute w/ build), will the command line work? Or do they need full-blown Access?

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You need full Access for the command line method.
k heitzsoftware developerAuthor Commented:
Thanks Scott. The open d/b was stopping the compact and repair. By closing the d/b and the c/r running the internal column count was reset.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.