add new field to an exisiting access table using vba

[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

 

[Douglas J. Steele]

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

 

[Rod Manson]

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

 

[Jim Burke in Novi]

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

 

[Douglas J. Steele]

What line of code raises the error?

 

[Rod Manson]

this one . .

tdfNew.Fields.Append .CreateField(strField, dbText)

Cheers,

Rod

 

[Rod Manson]

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

 

[Douglas J. Steele]

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

 

[Jim Burke in Novi]

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.

 

[Rod Manson]

Brilliant! Works a treat!

Thanks a lot Doug, very grateful!

Rod