Link to home
Start Free TrialLog in
Avatar of k heitz
k heitz

asked on

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

dbDelete.Close
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
                    'nuttin
                Else
                    .Fields(iCount).AllowZeroLength = True
                End If
                Exit For
            End If
        Next iCount
   
    End With
       
    Set tdf = Nothing

Thanks
Avatar of Chris Watson
Chris Watson
Flag of United Kingdom of Great Britain and Northern Ireland image

What happens if you use an ALTER TABLE statement?

ALTER TABLE YourTable DROP COLUMN YourField

Open in new window

Avatar of k heitz
k heitz

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of k heitz

ASKER

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?

Thanks!
You need full Access for the command line method.
Avatar of k heitz

ASKER

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.