Maisie has attended:
Access VBA course
Excel VBA Intro Intermediate course
ODBC drivers
Trying to connect to a Mysql database we have set up the database accordingly and connected it with the appropriate drivers but we are trying to connect to the databases tables with vba.
Currently we are using this
DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=DBname;UID=xx;PWD=xx;LANGUAGE=us _english;" _
& "DATABASE=tbl1", acTable, "Authors", "dboAuthors"
it connects fine but it pops up with the driver asking you to click okay how can we get disable this so it connects automatically without asking us to click ok
RE: ODBC drivers
Hi Maisie
Thank you for your question - can I just check does the dialog box present a message (and if so what does it say) and also does it offer an option to save your setting when you click OK?
Kind regards,
Andrew
RE: ODBC drivers
Its the standard input for a ODBC driver connection its put all our infomation in and just asks us to confirm it so it jumps out of the vba/access , the vba cant run until its done. Its not so much saving the settings rather entering them into the pop up and asking to click okay
Do you have any links or tutorials , that can push us in the right direction to actually accessing and using the data
RE: ODBC drivers
Hi Maisie
This is tricky to solve without seeing the database and obvious solution is to turn off the application's display alerts before the code runs and then switch them on again after.
so
docmd.setwarnings false
(Your Code)
docmd.setwarnings true
Try this to see if it works, if not let me know and I'll see if I can think of something else
Regards
Stephen
RE: ODBC drivers
I have fixed the previous error to not display the pop up. At the moment it connects to the database and links the table to MySql database which is what I need. In order to do this it needs to have a pre-defined odbc connection to be made using the driver or else it doesnt work.Even though I have defined the connection source in the code it doesn't seem to be working. I don't know where i'm going wrong.
Here's the code I am using:
DoCmd.TransferDatabase acLink, _
"ODBC Database", _
"ODBC;Driver = MySql ODBC 3.51 Driver;DSN=xxxx;ServerName=xxxx;ServerDSN=DIOMED;UID=xxxx;PWD=xxxx;StoreLogin=true;ArrayFetchOn=1;ArrayBufferSize=8", _
acTable, "tbl_1", "tbl_1"
RE: ODBC drivers
Hi Maisie
I have been speaking with my colleague Stephen about your forum posts and we have reached the limit of what we can arrange via the forum.
In order to find a solution the next stage will be to assess your actual working files. If we are confident of a solution we will prepare a scope of work including costs and durations for your review.
If you would like to discuss this further please let me know.
Kind regards
Jacob
