Add A Field To A Table With SQL

[wizcow2]

I am trying to run an update query that will add a yes/no field to tblParts.

I have this code connected to the click event of a button.

DoCmd.RunSQL = "ALTER TABLE tblParts ADD COLUMN [AllowDiscount] YESNO;"

This doesn't work. Could someone please tell me what is wrong.

Also, will this work if I split the db?

Thanks
Tom

Access 2k
Window 2k

 

[wizcow2]

I got some of this to work!

Dim dbs As Database
Set dbs = CurrentDb

dbs.Execute "ALTER TABLE Parts ADD COLUMN AllowDiscount YESNO;"

But, this does not work one the db has been split.

How do I reference the back end db?

Thanks Tom

 

[Fornatian]

make reference to the backend database and execute the SQL statement 'there'.

Public Sub AddFieldToAnotherDatabaseTable()
Dim db As DAO.Database
Dim dbPath As String
Dim strSQL As String
dbPath = "D:\My Documents\DesignNotes\Access\TestNames_be.mdb"
strSQL = "ALTER TABLE tblNames ADD COLUMN AnotherField2 YESNO;"

On Error GoTo MyErr
Set db = OpenDatabase(dbPath)
db.Execute strSQL
MsgBox "successfully amended"
fromhere:
db.Close
Set db = Nothing
Exit Sub

MyErr:
If Err.Number = 3211 Then ' can't alter table, open by another person or process
MsgBox "can't alter table, open by another person or process, please try later"
Else
MsgBox Err.Number & vbCrLf & Err.Description
End If
GoTo fromhere
End Sub

Note that there will be a conflict if someone had the table open while you were attempting to change the data structure.

Also, after changing the table you will need to refresh the link to the front-end table to reflect the changes, so try here...

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=27990&highlight=refreshlinks

 

[wizcow2]

Thanks Ian!

Nice bit of code.
Works like a dream too!!

Tom