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
