The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Using VBA to run .SQL file in Access 2016
Posted on 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.Connec
conn.CommandTimeout = 9999
conn.Provider = "SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Data Source=tcp:192.168.0.135,1
conn.ConnectionTimeout = 10
conn.Open "server=" & [ServerName] & ";uid=" & [User] & ";pwd=" & [password] & ";database=[Database Name]"
CurrentDb.Execute ("\\[ServerName]\[ShareNam
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
Is there a better way to call a .sql into Access? Can someone help me figure out what I coded wrong?
Public Sub sqldmo_execute()
Dim conn
Dim db
Set conn = CreateObject("ADODB.Connec
conn.CommandTimeout = 9999
conn.Provider = "SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Data Source=tcp:192.168.0.135,1
conn.ConnectionTimeout = 10
conn.Open "server=" & [ServerName] & ";uid=" & [User] & ";pwd=" & [password] & ";database=[Database Name]"
CurrentDb.Execute ("\\[ServerName]\[ShareNam
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
Is there a better way to call a .sql into Access? Can someone help me figure out what I coded wrong?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
2
2
8 Comments
Active today
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.
Active 7 days ago
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.
Active 7 days ago
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.
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.
Active 7 days ago
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.
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?
Featured Post
We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
AutoNumbers should increment automatically, without duplicates. But sometimes something goes wrong, and the next AutoNumber value is a duplicate. This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 11 hours left to enroll
635 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.