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

Posted on 2014-07-18
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:klheitz
    LVL 4

    Expert Comment

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


    Open in new window


    Author Comment

    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 84

    Accepted Solution

    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.

    Author Comment

    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 84
    You need full Access for the command line method.

    Author Closing Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now