Retaining control from MyODBC

[marklenel]

Hello,

I'm developing a system with an Excel front-end that uses ADODB and MyODBC to fire off instructions to a (local machine) MySQL server. As I step through my VBA code, it sends an instruction to MySQL (e.g. [tt] gconMySQL.Execute "DROP TABLE IF EXISTS MyData;" [/tt]), waits for the instruction to be executed, before returning control to the code and moving on to the next VBA statement. All well and good; this works fine for the majority of the processing.

However there is some initialisation of the MySQL database, particularly loading some data tables into MEMORY, that takes up to half a minute to complete. I'd rather fire off the instruction(s) to MySQL to do this so that MySQL initialises invisibly in the background, whilst the user continues playing around with the front-end themselves.

Can anyone suggest any ways to do this?

 

[dalchri]

I can't remember if this works but I know that the Execute statement accepts a flag something like adCmdExecAsync.

The next issue that I would warn you about though is to make sure you validate the data is completed loading in your app before you let the user do anything that requires that data.

If you are just loading data sequentially, you may want to look into optimizing your database connection using Option = 3 in your connection string. I forget what connection option it enables but I know that it yields a significant improvement for readonly forwardonly recordsets.

You might want to check out MySql.com for the connection options to verify.