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?
Set tdfDelete = dbDelete.TableDefs(sTable)
' Create and append field.
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
If .Indexes(intI).Name = sFieldName Then
Set tdfDelete = Nothing
Set dbDelete = Nothing
To Modify property:
Set tdf = db.TableDefs(sTable)
For iCount = 0 To .Fields.Count - 1
If UCase(.Fields(iCount).Name) = UCase(sField) Then
If .Fields(iCount).AllowZeroLength Then
.Fields(iCount).AllowZeroLength = True
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.
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: