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-prop erties 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).AllowZeroL ength Then
'nuttin
Else
.Fields(iCount).AllowZeroL ength = True
End If
Exit For
End If
Next iCount
End With
Set tdf = Nothing
Thanks
I have VB6 application that adds/deletes/modifies-prop
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
If .Fields(iCount).AllowZeroL
'nuttin
Else
.Fields(iCount).AllowZeroL
End If
Exit For
End If
Next iCount
End With
Set tdf = Nothing
Thanks
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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.
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.
Open in new window