By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,200 Members | 1,755 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,200 IT Pros & Developers. It's quick & easy.

Using MS Access as a front end and trying to run a script

P: n/a
Does anyone know how I can use vba in access to run a script that is stored
as a file. I can make a connection to mysql database and run sql statements
but this sql statement is very long (creates many tables with hundreds of
fields). I can run the script in the mysql interface but I want to do this
from my access front end to make it easy for the user to set up the tables.
I need something like
myconnection.execute ("c:\myfiles\myscript.sql)
but obviously this does not work.
Alex
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Sun, 02 Jan 2005 22:44:01 +0000, Paradigm wrote:
Does anyone know how I can use vba in access to run a script that is
stored as a file. I can make a connection to mysql database and run sql
statements but this sql statement is very long (creates many tables with
hundreds of fields). I can run the script in the mysql interface but I
want to do this from my access front end to make it easy for the user to
set up the tables. I need something like myconnection.execute
("c:\myfiles\myscript.sql) but obviously this does not work.
Alex


The best way to use Access/VBA with MySQL is to use the ODBC driver and
create linked tables to your MySQL tables. Having accomplished this, you
can treat your MySQL database as you would an ordinary Access database.

Your "myconnection.execute" example would not work because it requires a
string argument that is one single SQL statement. There are
several things you can do.

You can park each SQL statemnt in your script in an individual Access
query. Name your queries Query_01, Query_02, ... Query_NN. Now you can use
your VBA to loop execute each query in turn. NB: Make sure to define your
queries as sql PASSTHROUGH if you are using pure MySQL syntax.

OR

Write your VBA code to read the the [myscript.sql] file and pass each
single query statement to the connection .execute method in turn. All you
need do is assemble each line into a string (DIM sql As String). When you
encounter the ";" terminating line, you stop reading the file,
"myconnection.execute sql", sql = "", then continue reading the file.
Again, if you are using MySQL syntax, you need to be sure you define your
connection as type PASSTHROUGH.

It really isn't complicated if you are familiar with VBA together with ADO
or DAO. I use Access all the time to get around the MySQL 4.0x lack of
stored procedures.

If you get stuck, give another shout.
Thomas Bartkus

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.