Adding a field to Access DB using vba (1 Viewer)

[JohnPapa]

I use the following 3 calls to add 3 fields to an Access db,

Call subCreateField("tblRootCanalTreatment", "lngMethodID", "dbLong", strPath, 606)
Call subCreateField("tblRootCanalTreatment", "txtReferencePoint", "dbText", strPath, 620)
Call subCreateField("tblRootCanalTreatment", "txtSpaceForPole", "dbText", strPath, 644)

The sub is indicated below,

Private Sub subCreateField(strTable As String, strField As String, strFieldType As String, strPath As String, lngVersion As Long)
 
Dim rstSerial As ADODB.Recordset
Set rstSerial = New ADODB.Recordset
rstSerial.Open "tblSerial", CurrentProject.connection, adOpenKeyset, adLockPessimistic
rstSerial.MoveFirst
If rstSerial!lngVersion < lngVersion Then
 
On Error Resume Next
 
    DoEvents
 
    Dim db142 As DAO.Database
    Dim tdf142 As DAO.TableDef
    Dim fld142 As DAO.Field
    Dim prp142 As DAO.Property
    
    'Initialize
    Set db142 = OpenDatabase(strPath)
 
    Set tdf142 = db142.TableDefs(strTable)
    
    'Add a field to the table.
    If strFieldType = "dbText" Then
      Set fld142 = tdf142.CreateField(strField, dbText)
    ElseIf strFieldType = "dbLong" Then
      Set fld142 = tdf142.CreateField(strField, dbLong)
    ElseIf strFieldType = "dbBoolean" Then
      Set fld142 = tdf142.CreateField(strField, dbBoolean)
    ElseIf strFieldType = "dbDate" Then
      Set fld142 = tdf142.CreateField(strField, dbDate)
    End If
    tdf142.Fields.Append fld142
    
    ' after append new field create the property
    ' "DisplayControl" as a checkbox
    If strFieldType = "dbText" Then
      Set prp142 = fld142.CreateProperty("DisplayControl", dbText, acTextBox)
    ElseIf strFieldType = "dbLong" Then
      Set prp142 = fld142.CreateProperty("DisplayControl", dbLong)
    ElseIf strFieldType = "dbBoolean" Then
      Set prp142 = fld142.CreateProperty("DisplayControl", dbBoolean, acCheckBox)
    ElseIf strFieldType = "dbDate" Then
      Set prp142 = fld142.CreateProperty("DisplayControl", dbDate)
    End If
    ' append the property to the field
    fld142.Properties.Append prp142
    
 
 
    db142.Close
    
    'Clean up
    Set fld142 = Nothing
    Set tdf142 = Nothing
    Set db142 = Nothing
 
    rstSerial!lngVersion = lngVersion
    rstSerial.update
End If
End Sub

Only the first field ("lngMethodID") gets created. The other two fields ("txtReferencePoint") and ("txtSpaceForPole") do not get created. If I exit the db before each sub call then all fields get added. Do I need to add some "refresh field" action or other action.

Thanks in advance.

John

 

[JohnPapa]

It appears that I get Error 3211, see attachment. The database engine cannot lock the table to do the update.

Any ideas welcome.

Regards,
John

 

[billmeye]

Try using SQL to add columns to a table.

Function AddCols(AccessVer As Integer, DBName, tblName, ColName, ColType As String, Optional ColSize As Integer)
Dim myConn As New ADODB.Connection, FullColType As String
If ColSize > 0 Then
FullColType = ColType & "(" & ColSize & ")"
Else
FullColType = ColType
End If
If AccessVer > 2006 Then
myConn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBName)
Else
myConn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBName)
End If
myConn.Execute ("ALTER TABLE " & tblName & " ADD " & ColName & " " & FullColType & ";")
myConn.Close
Set myConn = Nothing
End Function
Function TestAddCol()
Call AddCols(2010, "C:\YrDB.Acccdb", "tblRootCanalTreatment", "lngMethodID", "Long")
Call AddCols(2010, "C:\YrDB.Acccdb", "tblRootCanalTreatment", "txtReferencePoint", "Text", 255)
Call AddCols(2010, "C:\YrDB.Acccdb", "tblRootCanalTreatment", "txtSpaceForPole", "Text", 255)
End Function

 

[billmeye]

To do any editing of a DB you have to have sole access, in other words, it can't be opened by someone else when you are attempting to remote access to it.

 

[JohnPapa]

Billmeye, I am opening the db on a desktop pc with exclusive rights.

 

[billmeye]

From the code you showed I am assuming you are remotely trying to add columns to a table. If you are just adding columns to the same DB you are working from, you don't need the connection code, just use docmd:

Function AddCols(tblName, ColName, ColType As String, Optional ColSize As Integer)
Dim FullColType As String
If ColSize > 0 Then
FullColType = ColType & "(" & ColSize & ")"
Else
FullColType = ColType
End If
Docmd.RunSql "ALTER TABLE " & tblName & " ADD " & ColName & " " & FullColType & ";"

 

[JohnPapa]

Summarizing,

The application is multi-up BUT the table/field update takes place always on a local pc with the db opened exlusively, so no multiuser issues.

Depending on which stage of the update the user is, probably 30 tables may be added and 100 fields. The problem appears when entering 3 fields of a specific table, as indicated earlier. The update of these 3 fields does not take place sequentially ie it takes place at different stages of the update process.

The update of the first field is OK. If I exit the application before the update of the 2nd and 3rd fields there is no problem.

Any ideas? I must have searched half the Internet so far!

Thanks,
John

 

[billmeye]

Try using SQL.

 

[MarkK]

It is very unusual to add fields as a routine process in managing data. Is this a learning process you are engaged in, or is this an actual process you intend to use? If the latter, you may want to re-think the process.

Typically you want to design tables so that you add data (rows), not structure (columns).

 

[Cronk]

John

Firstly, I avoid the expression On Error Resume Next except under special circumstances where I am expecting an error and want to ignore it. You can't catch errors and have no idea where the error is occurring.

So replace it with
On Error Goto subCreateField_Err

At the bottom of the sub subCreateField, just before the End Sub add the following

Exit Sub
subCreateField_Err:
stop
resume

When you run your code, if an error occurs in the Sub, execution will jump to the Stop and then you can press the function key F8 to step to the line causing the error.

When you do this, the problem is occurring with the adding of properties. It looks to me like you are trying to add Form properties to a table. However, I never use this method of adding fields, preferring to use SQL DDL as in "ALTER TABLE xxx ADD COLUMN ...

You can fix this by simply removing this code - it's irrelevant.

So delete or comment out everything between
tdf142.Fields.Append fld142 and db142.Close

Let us know how you go.

 

[JohnPapa]

It is very unusual to add fields as a routine process in managing data. Is this a learning process you are engaged in, or is this an actual process you intend to use? If the latter, you may want to re-think the process.
 
Typically you want to design tables so that you add data (rows), not structure (columns).

We are talking about a software package which has been around since 1997. Enhancements and updates require the addition of new tables and new fields to existing tables. If you can suggest another way, please let me know.

Firstly, I avoid the expression On Error Resume Next except under special circumstances where I am expecting an error and want to ignore it. You can't catch errors and have no idea where the error is occurring.

Good point and Error trapping was introduced.

When you do this, the problem is occurring with the adding of properties. It looks to me like you are trying to add Form properties to a table. However, I never use this method of adding fields, preferring to use SQL DDL as in "ALTER TABLE xxx ADD COLUMN ...

Apparently you cannot use SQL DDL to update (add field in this case) a linked source. In other words, we have a FE and a BE and the specific field needs to be added to a BE table.

John

 

[Mihail]

What about the Make Table queries ?

 

[JohnPapa]

Thanks Mihail,

The Make table query deals with tables. There is no problem with creating or copying a table to a BE. The problem I have is with adding a field to a BE table. If you mean something else, please let me know.

John

 

[Cronk]

John
You state "Apparently you cannot use SQL DDL to update (add field in this case) a linked source."

Not true. You can't use a database object opened on the front end, but need to open a db object in the back end.

viz set db = dbengine(0).opendatabase(strPath, True) where strPath is the full path\filename of the BE.

Any db SQL operations are then done on contained tables, not linked tables.

And I agree with you, additional functionality added over the life a system does require the addition of new fields, even new tables.

 

[billmeye]

JohnPapa, I gave you code on how to use SQL on your backend.

Your problem sounds like you are trying to add columns to a live table, one that is open. If you have the form open and want to add columns to the underlying recordsource (table) you first need to remove the forms recordsource (set it blank) so the table is closed then you can add your columns and then reset the forms recordsource back to the table.

docmd.Echo False
Me.RecordSource =""
'Do all your table additions
Me.RecordSource = "tblYourTable"
Docmd.Echo True

 

[JohnPapa]

billmeye,

I did not phrase it correctly. I meant that you cannot use the table link to add a field.
The form for the specific table is not open. The updating takes place at the very beginning when only a setup form is open. If it was a problem with a form using the table then the first field addition would cause a problem (which it does not), as well as the 2nd and 3rd.

In any case the following works in place of the original code, where "strPath" is the pathname to the BE db.

Dim db As DAO.Database
Set db = OpenDatabase(strPath)
If strFieldType = "dbText" Then
db.Execute "ALTER TABLE [" & strTable & "] ADD COLUMN [" & strField & "] CHAR(255);", dbFailOnError
ElseIf strFieldType = "dbLong" Then
db.Execute "ALTER TABLE [" & strTable & "] ADD COLUMN [" & strField & "] LONG;", dbFailOnError
ElseIf strFieldType = "dbBoolean" Then
db.Execute "ALTER TABLE [" & strTable & "] ADD COLUMN [" & strField & "] YesNo;", dbFailOnError
ElseIf strFieldType = "dbDate" Then
db.Execute "ALTER TABLE [" & strTable & "] ADD COLUMN [" & strField & "] DATE;", dbFailOnError
End If
db.Close

The bad news is that I still get that the database engine cannot lock the specific table. I still need to do an Application.Quit prior to entering the 2nd and 3rd fields.

John

 

[billmeye]

John, The code I gave you

Function AddCols(AccessVer As Integer, DBName, tblName, ColName, ColType As String, Optional ColSize As Integer)
Dim myConn As New ADODB.Connection, FullColType As String
If ColSize > 0 Then
FullColType = ColType & "(" & ColSize & ")"
Else
FullColType = ColType
End If
If AccessVer > 2006 Then
myConn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBName)
Else
myConn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBName)
End If
myConn.Execute ("ALTER TABLE " & tblName & " ADD " & ColName & " " & FullColType & ";")
myConn.Close
Set myConn = Nothing
End Function
Function TestAddCol()
Call AddCols(2010, "C:\YrDB.Accdb", "tblRootCanalTreatment", "lngMethodID", "Long")
Call AddCols(2010, "C:\YrDB.Accdb", "tblRootCanalTreatment", "txtReferencePoint", "Text", 255)
Call AddCols(2010, "C:\YrDB.Accdb", "tblRootCanalTreatment", "txtSpaceForPole", "Text", 255)
End Function

really does work. If you want to do multiple additions do just like I show in the second function. I'm only guessing your code is locking the remote DB after the first addition and that is preventing you from making any other additions.

If you want a single function to add many columns at once you could simplify it:

Function AddcolsAtOnce()
Dim myConn As New ADODB.Connection
myConn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\YrDB.Accdb")
myConn.Execute ("ALTER TABLE [tblRootCanalTreatment] ADD lngMethodID Long;")
myConn.Execute ("ALTER TABLE [tblRootCanalTreatment] ADD txtReferencePoint Text(255);")
myConn.Execute ("ALTER TABLE [tblRootCanalTreatment] ADD txtSpaceForPole Text(255);")
myConn.Close
Set myConn = Nothing

End Function

 

[Mihail]

The Make table query deals with tables. There is no problem with creating or copying a table to a BE. The problem I have is with adding a field to a BE table. If you mean something else, please let me know.
John

I've think to a way to collect all necessary fields from both tables in the query then make a new table.
This way also will have a populated table.
Then you can remove the old table and rename the new one.
Of course is needed to redesign the relationships.

Note, please, that this is not a solution. Is only a idea. If is useful for you I'll be happy, if not... I am happy because I try to help you.

 

[JohnPapa]

billmeye,
Will try out your code. I am in the process of migrating from 2003 to 2013 and I notice you take care of that.
Will let you know.
Regards,
John

 

[Cronk]

Your SQL won't work to add a field to a table in another database file.

Firstly there is a missing key word COLUMN missing after the ADD.

Secondly, as above, it does not work on an external file, whether anything is open or not.

Try it yourself and you will get an error message saying so..