Change data type of column in multiple tables in Access with VBA

Question

I am fairly new to using VBA in MS Access and I am having trouble embedding SQL statements into VBA code. I have a database with almost 200 tables, and I would like to change the data type of one column (named lake) in each table to a "text" data type. I wrote the following code, but keep getting a syntax error for the ALTER TABLE statement.

Public Sub changeDataType()

Dim db As DAO.Database
Dim table As DAO.TableDef

Set db = CurrentDb

  For Each table In db.TableDefs
     DoCmd.RunSQL "ALTER TABLE" & table.Name & "ALTER COLUMN [lake] TEXT(100);"
  Next

Set db = Nothing

End Sub

Can anyone tell me why I'm getting the syntax error?

Thanks,
Paul

BWS · Accepted Answer · 2013-11-18 19:44:22Z

3

this statement will not be correct:

DoCmd.RunSQL "ALTER TABLE" & table.Name & "ALTER COLUMN [lake] TEXT(100);"

if, for instance "table.Name" = "myTable", the resulting statement will look like this:

DoCmd.RunSQL "ALTER TABLEmyTableALTER COLUMN [lake] TEXT(100);"

try adding a space to separate the name, like this:

DoCmd.RunSQL "ALTER TABLE [" & table.Name & "] ALTER COLUMN [lake] TEXT(100);"

edited Nov 18, 2013 at 19:44

answered Nov 18, 2013 at 19:39

BWS

3,85620 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gord Thompson Over a year ago

Good catch. You might want to go one step further and suggest ALTER TABLE [" & table.Name & "] ..." for added safety.

BWS Over a year ago

aha ... good as well ... I'll edit the answer to include ... thanks.

logicForPresident Over a year ago

Thanks all. Gord, with your additional suggestion I got it to work...at least I think...Access is still running it. Thanks to both

BWS Over a year ago

should be able to ... what's the error message you are getting?

Collectives™ on Stack Overflow

Change data type of column in multiple tables in Access with VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related