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

Posted on 2014-07-18
Medium Priority
Last Modified: 2014-07-21
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

Question by:k heitz
  • 3
  • 2

Expert Comment

by:Chris Watson
ID: 40204644
What happens if you use an ALTER TABLE statement?


Open in new window


Author Comment

by:k heitz
ID: 40205369
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.
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40207353
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.

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

by:k heitz
ID: 40209062
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?

LVL 85
ID: 40209104
You need full Access for the command line method.

Author Closing Comment

by:k heitz
ID: 40210173
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.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question