Solved

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

Posted on 2009-07-12
6
297 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

943 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now