ODBC and BATCH mode ON mysql

[mikeyd03]

I have a file named runsql.txt that is created each night and holds SQL statements, such as:

Code: Select all

DELETE FROM table1 WHERE ID = '1';
DELETE FROM table1 WHERE ID = '15';
DELETE FROM table1 WHERE ID = '56';

Normally, I FTP the file to the server and run MySql in batch mode to process the commands.

I have been able to use macro scheduler to insert records into a database:

Code: Select all

VBSTART
Dim MyDB
Dim rs

Set MyDB = CreateObject("ADODB.Connection")

MyDB.Open "MYSQL-Connect"

Set rs = MyDB.Execute("LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\\Addthese.txt' INTO TABLE table1 IGNORE 1 LINES;")
VBEND

How do I delete records?

Sorry if this was in another thread, I searched but didn't find it.

[Marcus Tettmar]

This is a SQL question, not a Macro Scheduler question, but - use a DELETE FROM query.

[mikeyd03]

Sorry I wasn't clear. I understand it's a DELETE statement, I don't understand how to use this with macro scheduler. I could have 50 - 100 delete statements. I usually create a txt file with all my DELETE commands, ftp it to the server, then use Macro scheduler to SSH in and run mysql in batch mode to run the file ex:

Code: Select all

Send>mysql -t --force <deletefile>deleteresult.txt

I'm just not sure how to do this thru ODBC and macro scheduler.
I Tried:

Code: Select all

VBSTART
Dim MyDB
Dim rs

Set MyDB = CreateObject("ADODB.Connection")

MyDB.Open "MySQL"

Set rs = MyDB.Execute("SOURCE deletefile.txt;")
VBEND

But I get an error message. I would like deletefile.txt to be a local file, but could ftp it to server first if needed.
I also tried:

Code: Select all

Set rs = MyDB.Execute("SOURCE C:\deletefile.txt;")
and
Set rs = MyDB.Execute("SOURCE C:/deletefile.txt;")

[Marcus Tettmar]

deletefile.txt needs to be on the server. The SOURCE statement refers to a file on the mysql server.