I need to add a column to a bunch of Access 97 databases. The databases all have the same name and identical structures but they're in different folders.
If I open each MDB in Access 97 I can run a SQL query to add the column like this:
ALTER TABLE table1 ADD COLUMN field5 text(50)
However, there are about 200 databases so I'm trying to run a query in SQL Server Management Studio 2005 like this:
ALTER TABLE OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\xxxx0001\TEST.MDB')...[TABLE1]
ADD COLUMN field5 Text(50)
This throws error:
"Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'OpenDataSource'.
I can't figure out what's wrong with the syntax - can this done in SSMS?
If I open each MDB in Access 97 I can run a SQL query to add the column like this:
ALTER TABLE table1 ADD COLUMN field5 text(50)
However, there are about 200 databases so I'm trying to run a query in SQL Server Management Studio 2005 like this:
ALTER TABLE OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\xxxx0001\TEST.MDB')...[TABLE1]
ADD COLUMN field5 Text(50)
This throws error:
"Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'OpenDataSource'.
I can't figure out what's wrong with the syntax - can this done in SSMS?