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?
Our community of experts have been thoroughly vetted for their expertise and industry experience.