Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using VBA to run .SQL file in Access 2016

Posted on 2016-09-29
8
Medium Priority
?
314 Views
Last Modified: 2016-09-29
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?
0
Comment
Question by:commeng
  • 4
  • 2
  • 2
8 Comments
 
LVL 36

Accepted Solution

by:
ste5an earned 1400 total points
ID: 41821741
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.

That is only possible when you either run the script against your Access database and use linked tables to SQL Server or when you run the script against your SQL Server and use a linked server to your Access database.

So the question is: What are you trying to do?

For both scenarios you need to read the file first into a string variable.

Further more CurrentDb.Execute runs only SQL statements against your Access database. You cannot provide a path to your script. Also your usage of CreateObject() in your procedure makes absolutely no sense.
0
 

Author Comment

by:commeng
ID: 41821762
The Access database is link to the SQL tables.  OK so I need to read my .sql file into a string variable not call the file itself.
0
 

Author Comment

by:commeng
ID: 41821773
Basically I have a script on the SQL server that calls information out of AutoCad and populates a table in SQL.  I want the end user to use Access as the front end, so I need the data to always be current. When they are in Access.  So my script updates the SQL table and then the Access database.  But the don't want the end user to have to use SQL Management Studio to get the most current data.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 600 total points
ID: 41821835
I want the end user to use Access as the front end, so I need the data to always be current. When they are in Access.
So, if Access is the front end and SQL Server the back end why do you need to update data in Access? Like ste5an said, a link table in Access should solve your problem so you'll only need to update SQL Server database and who connects to Access will see immediately any change made in SQL Server database.
0
 

Author Comment

by:commeng
ID: 41821850
Yes, I do have the tables linked.  But it he user makes a change in AutoCAD, then they need to update the SQL table so it will reflect in Access.  So I need to work on automating the SQL update not Access.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41821875
Yes, I do have the tables linked.
But it he user makes a change in AutoCAD, then they need to update the SQL table so it will reflect in Access.
What I'm missing here? Is there tables in Access that aren't linked to SQL Server and you're updating those? If affirmative, are the same tables existing in SQL Server database? Again, if affirmative, why not working only with SQL Server tables and get rid of the Access databases?
0
 
LVL 36

Expert Comment

by:ste5an
ID: 41821879
Can you explain the connection between AutoCAD, Access and SQL Server? What kind of process?
0
 

Author Comment

by:commeng
ID: 41821881
It got the code.  Thank you both.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question