ALTER TABLE in a sub?

  • Thread starter Thread starter Gary Schuldt
  • Start date Start date
G

Gary Schuldt

I want to add 4 columns to a table created in a previous step. I have
thought of various ways to do this, and the most elegant seems to be to do
it in the same procedure where I'm already manipulating the table's data.

Does that seem like a good solution?

If so, can I do something at the beginning like:
-------------------
Dim rs As DAO.Recordset

Set rs = CurrentDB.OpenRecordset("PlantData")

ALTER TABLE rs ADD COLUMN L1 TEXT(50)
ALTER TABLE rs ADD COLUMN L2 TEXT(50)
etc.
--------------------
If so, how do I code that SQL statement in VB?

Thanks.

Gary
 
Gary Schuldt said:
I want to add 4 columns to a table created in a previous step. I have
thought of various ways to do this, and the most elegant seems to be
to do it in the same procedure where I'm already manipulating the
table's data.

Does that seem like a good solution?

If so, can I do something at the beginning like:
-------------------
Dim rs As DAO.Recordset

Set rs = CurrentDB.OpenRecordset("PlantData")

ALTER TABLE rs ADD COLUMN L1 TEXT(50)
ALTER TABLE rs ADD COLUMN L2 TEXT(50)
etc.
Click to expand...

No, you can't do it that way. But why don't you just alter the table
first, and then open the recordset:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

db.Execute _
"ALTER TABLE PlantData ADD COLUMN L1 TEXT(50)", _
dbFailOnError

db.Execute _
"ALTER TABLE PlantData ADD COLUMN L2 TEXT(50)", _
dbFailOnError

Set rs = db.OpenRecordset("PlantData")

' ... work with rs ...

rs.Close
Set rs = Nothing
Set db = Nothing
 
Of course! Exactly what I meant! ;-|

(VBA, the Full Employment Act for MVPs)

Thanks again, Dirk.
 
Back
Top