How to alter tables in SQL Server from Access

[mikebrewer]

Hey guys,

If I'm being stupid about this, please let me know... I have an access front-end package that works just fine but if there is an update, I just want the program to run scripts to update the SQL Server tables that it is connected to. I just don't know how to do this. I know how to do this on SQL Server Management studio and all, I just was hoping I could do it straight from my access front end so that a user could just click a button and update the tables or something.

thanks!

 

[pbaldy]

I've always done the changes directly in SSMS, but I suppose you could try putting DDL in a pass-through query. Worked in a brief test for me.

 

[mikebrewer]

I hate to seem stupid but can you post your code to how you did it? I've tried but I always get errors.

 

[pbaldy]

What are you trying to do? What worked in SSMS? I tested a simple ALTER TABLE statement.

 

[mikebrewer]

This is my code:

strSQL = "ALTER TABLE TEST ADD COLUMN Test int"
DoCmd.RunSQL strSQL


I get an error 3611 cannot execute data definition statements on linked data sources

 

[WayneRyan]

Mike,

The DoCMD works on your local Access db.

You have to issue the command over an ADO connection.

ADO_Connection.Execute strSQL

Wayne

 

[Galaxiom]

You must also refresh the link in Access after the change on the server.

You should also consider the risks of giving permissions for ordinary users to alter tables on the server. You might want to make this available only to designated admin users.

 

[pbaldy]

This is my code:

strSQL = "ALTER TABLE TEST ADD COLUMN Test int"
DoCmd.RunSQL strSQL


I get an error 3611 cannot execute data definition statements on linked data sources

Note I mentioned a pass through query.

 

[mikebrewer]

ah ha! Thank you guys! I appreciate it. I hadn't worked with pass through queries before.

Thanks again!

 

[pbaldy]

Happy to help!