Avatar of commeng
commeng
Flag for United States of America

asked on 

Using VBA to run .SQL file in Access 2016

I am trying to write a script that will run a .sql file directly from Access to update the Access database and SQL database at the same time.  My script works great in SQL Management Studio,  but I am having problems calling it from VBA:  My script reads:

Public Sub sqldmo_execute()

Dim conn
Dim db
Set conn = CreateObject("ADODB.Connection")
conn.CommandTimeout = 9999
conn.Provider = "SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Data Source=tcp:192.168.0.135,1433"
conn.ConnectionTimeout = 10
conn.Open "server=" & [ServerName] & ";uid=" & [User] & ";pwd=" & [password] & ";database=[Database Name]"

CurrentDb.Execute ("\\[ServerName]\[ShareName]\Scripts\SQLSync.sql")

MsgBox ("Complete")

End Sub

I am getting a runtime error 3078:  The Microsoft Access database engine cannot find the input table or query "\\[ServerName]\[ShareName]\Scripts\SQLSync.sql".  Make sure it exists and that its name is spelled correctly.

Is there a better way to call a .sql into Access?  Can someone help me figure out what I coded wrong?
Microsoft AccessMicrosoft SQL Server

Avatar of undefined
Last Comment
commeng

8/22/2022 - Mon