SQL query to ALTER TABLE ADD COLUMN in Access database (1 Viewer)

[Neophyte2]

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?

 

[static]

Why would SSMS alter Access?

currentdb.execute "ALTER TABLE [pathtodatabase].table1 ADD COLUMN field5 text(50)"

 

[Neophyte2]

That's awesome - thank you very much. I'm not a programmer or developer so I didn't understand your reply at first but after googling currentdb.execute now I get it. Thanks again!

 

[isladogs]

Not sure about the first part of the script as I can't get the connection part right
My error in SSMS is:

[COLOR="red"]Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'OpenDataSource'.[/COLOR]

However, the last part should be as shown in RED:

ALTER TABLE OpenDataSource ('Microsoft.Jet.OLEDB.4.0', 'Data Source=YourDathbasepath') [COLOR="Red"][table1] ADD [field5] [varchar] (50) NULL[/COLOR]

When adding a field, you don't use COLUMN
You do use COLUMN when altering the field type

Remember text fields in Access are varchar is SQL Server
I've added NULL but it could be NOT NULL instead

If you can reply with the FULL connection string, I'd be grateful in case I ever want to use this idea

 

[Neophyte2]

static's reply got me what I needed without using SSMS. I don't understand what you mean by FULL connection string but here's an example of an SSMS query that does work.

UPDATE OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\xxxx0001\TEST.MDB')...[TABLE1]
SET DEPTNAME = 'deptvalue'
WHERE DEPTID = 'someid'

 

[isladogs]

I realised Static's answer would work from Access but your question got my interest in terms of doing it from SSMS...

I thought the ... was you omitting part of a long string

I've tried using that and various alternatives but still get errors
For example,

EXEC OpenDataSource ('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\Programs\MendipDataSystems\SDA\SetControls.accdb') 
	ALTER TABLE [dbo].[table1] ADD [field5] [varchar] (50) NULL 
GO

This errors at ALTER

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'ALTER'.

So I'm no nearer either
None of the online examples I've found for OpenDataSource connection strings cover ALTER TABLE scenario.