Solved

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

Posted on 2009-07-12
6
302 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:Eric Sherman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 7

Accepted Solution

by:
Mohed Sharfi earned 500 total points
ID: 24834998
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
 
LVL 19

Author Comment

by:Eric Sherman
ID: 24835222
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
 
LVL 7

Expert Comment

by:Mohed Sharfi
ID: 24835449
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 19

Author Comment

by:Eric Sherman
ID: 24835621
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
 
LVL 19

Author Comment

by:Eric Sherman
ID: 24835683
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
 
LVL 19

Author Closing Comment

by:Eric Sherman
ID: 31602587
Thanks!!!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question