add new field to an exisiting access table using vba

  • Thread starter Thread starter Rod Manson
  • Start date Start date
R

Rod Manson

Hi, can anyone help please?
I am trying to find the code to add a new field to an existing table. I have
found out how to add fields to a newly created table using

Set tdfNew = db.CreateTableDef("TableName")
tdfNew.Fields.Append .CreateField("Field1", dbText)

but how do i do it for an existing table???

Thanks,

Rod
 
Set tdfNew = db.TableDefs("TableName")
tdfNew.Fields.Append .CreateField("Field1", dbText)
 
Hi thanks for the advice, but I still can't get it to work. This is the
Function which generates a Compile error: Invalid or unqualified reference.
Any suggestions please?

Thanks!

Function Test()

Dim myDB As DAO.Database, tdfNew As TableDef
Dim strTitle As String, strPrompt As String, strInput As String
Dim strTable As String, strField As String

Set myDB = CurrentDb()

strTitle = "Table Name"
strPrompt = "Enter the table name you want to add a new text field to .
.. "

strTable = InputBox(strPrompt, strTitle)

strTitle = "Field Name"
strPrompt = "Enter the field name you want to enter . . "

strField = InputBox(strPrompt, strTitle)

Set tdfNew = myDB.TableDefs(strTable)
tdfNew.Fields.Append .CreateField(strField, dbText)

DoCmd.OpenTable strTable

End Function
 
I've always used DDL to add fields. You need to set a reference to the
library for Microsoft ADO Extension n.n for DDO and Security for this to
work. Here's the code I use for adding text fields. it allows you to specify
the length if you don't want to use the default.

Dim cmd As New ADODB.Command

With cmd
.ActiveConnection = cn 'cn is a variable I set to my active connection
.CommandType = adCmdText
.CommandText = "ALTER TABLE " & _
tableName & _
" ALTER COLUMN " & _
columnName & " TEXT (" fieldLength & ")"
.Execute
End With
 
Thanks Jim, but it doesn't work for me I'm afraid . . this is what I tried to
run, but it produced a run time error 3001 at the .ActiveConnection line :
Arguments of the wrong type, are out of acceptable range, or are in conflict
with another one. . . any suggestions please?

Function Test()

Dim cmd As New ADODB.Command
Dim bytFieldLength As Byte
Dim strTableName As String, strColumnName As String

bytFieldLength = 12
strTableName = "Taxis"
strColumnName = "Driver"

With cmd
.ActiveConnection = cn 'cn is a variable I set to my active connection
.CommandType = adCmdText
.CommandText = "ALTER TABLE " & strTableName & " ALTER COLUMN " &
strColumnName & " TEXT (" & bytFieldLength & ")"
.Execute
End With

End Function
 
Set tdfNew = myDB.TableDefs(strTable)
tdfNew.Fields.Append .CreateField(strField, dbText)

needs to be

Set tdfNew = myDB.TableDefs(strTable)
tdfNew.Fields.Append tdfNew.CreateField(strField, dbText)

or

Set tdfNew = myDB.TableDefs(strTable)
With tdfNew
.Fields.Append .CreateField(strField, dbText)
End With
 
cn is a 'global' variable that I set in my application when it starts. For
your purposes just use

.ActiveConnection = CurrentProject.Connection

That should work.
 
Back
Top