DSNLess Connection the MySQL Server Back-End and Access Front-End Question??

I have a Access 2000 front-end with MySQL back-end application that uses a DSN on startup to link all the tables from the MySQL server into the front-end.  This all works fine and have been for a number of years.  Due to growth, expansion and deployment issues I may have to reconfigure and use a DSNLess connection to the MySQL server.  Since there are forms in the front-end that are bound to various tables, etc. I cannot just open a connection using a connection string and pulling a recordset based on a select statement.  I need something to bind the form to.

Therefore, I was going to use some code similar to what's shown below which should work.  The question that I have is ... when creating a DSN for MySQL using the ODBC Data Source Administrator Wizard there are some additional options on the Flags Tab that can be set (Return Matching Rows and Don't Prompt Upon Connect) ....  How do I set these properties in the connection string below????

Function LinkTableDSNLess()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String

strConnect = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=myDataBase;User=myUsername;Password=myPassword;Option=3;"

Set db = CurrentDb()
Set tdf = db.CreateTableDef("Table1")
tdf.SourceTableName = "Table1"

tdf.Connect = strConnect

db.TableDefs.Append tdf

Set tdf = Nothing
Set db = Nothing
End Function


LVL 19
Eric ShermanAccountant/DeveloperAsked:
Who is Participating?
Mohed SharfiConnect With a Mentor Vice CEOCommented:
Hi etsherman,
I hope you fine
try these options in your connection string:
option=16 for Don't Prompt Upon Connect
FLAG_FOUND_ROWS=2 for Return Matching Rows
Eric ShermanAccountant/DeveloperAuthor Commented:
Thanks for the reply mSharfi ....

Are you saying the the the connection should look like the following?  Do I still need the Option=3 in there???

strConnect = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=myDataBase;User=myUsername;Password=myPassword;Option=16;FLAG_FOUND_ROWS=2;"

Also, is there a reference somwhere that shows the value for all the connection properties???


Mohed SharfiVice CEOCommented:
Hi etsherman,
how are you, in this document I think you can find these parameters:
I'm not sure it will be work but you can please to try to pass all options you need to params variable like:
params->return_matching_rows =   (nOptions & FLAG_FOUND_ROWS) > 0;  /* 2 */
+  params->allow_big_results =  (nOptions & FLAG_BIG_PACKETS) > 0;
+  params->dont_prompt_upon_connect = (nOptions & FLAG_NO_PROMPT) > 0;
I hope its ok for you
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Eric ShermanAccountant/DeveloperAuthor Commented:
Thanks mSharfi ... but I am doing this in Visual Basic so I'm trying to find how to structure the connection string in there to set the (Return Matching Rows and Don't Prompt Upon Connect) parameters.

Eric ShermanAccountant/DeveloperAuthor Commented:
Ok, I think I've found what I was looking for with mSharfi's help.  Your post pointed me in the right direction (Connection Parameters).  See link below.


Basically you add the value for all the Option Parameters you want to specify in the connection string.  Therefore ...

Access/VB = 3
Return Matching Rows = 2
Do Not Promt Upon Connect = 16

The string would look lik this ...

strConnect = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=myDataBase;User=myUsername;Password=myPassword;Option=21;"


Eric ShermanAccountant/DeveloperAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.