Run SQL Scripts Files from ADP

[Guest]

I have an Access ADP application that connects to SQL Server 2005.

I am having a hard time figuring out how to execute script files (.sql) I
have created that will modify the database structure in SQL Server. I need to
have the ability to modify my SQL Server Database structure from within my
Access Application so that I can add tables, modify tables, add fields,
remove fields, etc without using the Enterprise Manager. I have no problem
creating a valid script file (.sql). But, what i can't figure out is how I
would run these scripts against my current database?

I need to be able to run these scripts so I can update customer databases
without effecting existing data.

Can anyone help?

 

[Sylvain Lafontaine]

CurrentProject.Connection.Execute(YourSQLString) will do that.

Remember that any GO command is not part of the T-SQL language and won't be
recognised by the SQL-Server, so you must remove them.

Also, you may have problems between the new schema of your database and what
Access thinks the schema is. Calling the Application.RefreshDatabaseWindow
will help you with this but not for all situations. It's also possibly a
good idea to put some time between your modification to the database and the
call to this method so that the server have had the time to complete the
operation.

 

[Guest]

My SQL Script is in a file, named SQLScript.sql. How can I execute that text
using this method? Thanks.

 

[Sylvain Lafontaine]

Use FSO in VBA code, for example:

http://groups.google.ca/group/micro...89bfdaf?lnk=gst&q=fso&rnum=5#e8257305689bfdaf

 

[Guest]

Excellent,

This is exactly what I was looking for.

Thanks a million....

Greg