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
db.TableDefs.Refresh

Set tdf = Nothing
Set db = Nothing
End Function


Thanks,

ET
LVL 19
Eric ShermanAccountant/DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mohed SharfiVice 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
thanks
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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???

Thanks

ET
0
Mohed SharfiVice CEOCommented:
Hi etsherman,
how are you, in this document I think you can find these parameters:
http://lists.mysql.com/commits/37485
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
thanks
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.

ET
0
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.

http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-parameters.html

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;"

ET


0
Eric ShermanAccountant/DeveloperAuthor Commented:
Thanks!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.