Solved

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

Posted on 2009-07-12
6
307 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 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