UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How to log in MySQL from Access, Office 2007    
 
   
Sako
post Jul 15 2010, 02:15 PM
Post#1



Posts: 577
Joined: 25-October 08
From: Canada


Hi everyone,
connected MySQL database to MS Access via ODBC.
Now, I can work in access and append queries to the MySQL DB (if it is less than 50MB - Limit set by my Provider).
However, I want to automate the logging process, (Username, password) so It will run smoothly without promting me for anything.
Is this possible?
Go to the top of the page
 
BananaRepublic
post Jul 15 2010, 02:35 PM
Post#2


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Yes, this is very possible.
On my last post in this thread, I gave a sample connecting to a MySQL database. While this sample uses a custom Log In form, you can just automate the thing behind the scene by using the code in the module in the sample.
Do be aware, though - if you don't want to have to type something to login, that may mean you need to store password and it's never safe to store a password. It's OK if your MySQL database is accessible via only local network and you trust your employees but that is a hole you need to know about.
HTH.
Go to the top of the page
 
Sako
post Jul 15 2010, 03:25 PM
Post#3



Posts: 577
Joined: 25-October 08
From: Canada


Thank you BR,
will try to implement it!!
Have a great day!
Go to the top of the page
 
Sako
post Jul 15 2010, 03:40 PM
Post#4



Posts: 577
Joined: 25-October 08
From: Canada


Below is the code that I found on your post. I modified it a bit. I don't understand what "opts" is for?
lso, do I need the last paragraph? I just want to connect to MySQL automatically, so is the 1st and 2nd part of this code sufficient?
CODE
Private Sub MySQLConnect()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
With cn
    .ConnectionString = _
       "Driver=MySQL ODBC 5.1 Driver;" & _
       "Server=www.mywebsite.com;" & _
       "Database=DatabaseName;" & _
       "user=UserName;" & _
       "pwd=Password;" & _
       "opts=???;"
    .Open
End With
With rs
    Set .ActiveConnection = cn
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    cn.Execute "BEGIN WORK;"
    .Source = "SELECT * FROM city FOR UPDATE;"
    .Open
End With
Set Me.Recordset = rs
End Sub
Go to the top of the page
 
BananaRepublic
post Jul 16 2010, 09:02 AM
Post#5


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Options: They determine what optional features you want to enable/disable. At a minimum, you must specify 2 (Return Affected Rows instead of Matched Rows) else Access may behave oddly now and then.
Documentations of the Options can be found here. If you look in the standard moduel in my sample, I wrote an enum that corresponds to the options to make it easy to figure out what options you want to enable/disable.
Yes your code should work well enough if ADO is the only thing you need.
Do beware: You may not actually need the "FOR UPDATE" clause - that was for the other user who needed restrictive locking. FOR UPDATE can be expensive, and is very restrictive - nobody else can even read the rows! Also, it won't work if the table isn't driven by InnoDb.
Do consider moving your Connection object out and make it global so you don't have to re-open your connection every time you need a new Recordset. Connection are quite expensive so it's good to re-use it if you can.
Go to the top of the page
 
Sako
post Jul 16 2010, 02:30 PM
Post#6



Posts: 577
Joined: 25-October 08
From: Canada


Hi BR,
could not find the link. the page could have been removed.
-I changed from private to public sub now. Is this how I make my connection reusable?
-I also removed the FOR UPDATE clause, since I don't need it and you said it was expensive.
CODE
Public Sub MySQLConnect()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
With cn
    .ConnectionString = _
       "Driver=MySQL ODBC 5.1 Driver;" & _
       "Server=www.mywebsite.com;" & _
       "Database=DatabaseName;" & _
       "user=UserName;" & _
       "pwd=Password;" & _
       "opts=???;"
    .Open
End With
Set Me.Recordset = rs
End Sub
Go to the top of the page
 
BananaRepublic
post Jul 16 2010, 03:00 PM
Post#7


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Sorry about that. Does this link work for you?
Well, public is one way but you're still creating a new connection every time you call that sub so it's not really re-use.
Here's how I typically handle this:
CODE
Property Get MySQLConn() As ADODB.Connection
Static cn As ADODB.Connection
Select Case True
   Case cn Is Nothing, cn.State = adStateClosed
      Set cn = New ADODB.Connection
      With cn
         .ConnectionString = _
           "Driver=MySQL ODBC 5.1 Driver;" & _
           "Server=www.mywebsite.com;" & _
           "Database=DatabaseName;" & _
           "user=UserName;" & _
           "pwd=Password;" & _
           "opts=???;"
         .Open
      End With
End Select
Set MySQLConn = cn
End Property

Then whenever you need a new ADODB recordset anywhere in your project:
CODE
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = MySQLConn
rs.Open ...

HTH.
Go to the top of the page
 
Sako
post Jul 16 2010, 04:23 PM
Post#8



Posts: 577
Joined: 25-October 08
From: Canada


Yes, that link does work for me!
think I will put 0 for options (which is the default). I read all the other options but I don't understand them at all.
So, to recap:
I will use your first code and put it in a module in my Access DB.
Then If I want let's say to run my Append Query to the MySQL table, I am not sure what the next step would be... Will Access go fetch that code in the module or do I have to call it by running it in a macro?
Thanks, I am quite new at this ODBC thing. I usually run queries by using macros so that 1 click can run 10 queries one after the other...
Go to the top of the page
 
BananaRepublic
post Jul 16 2010, 06:15 PM
Post#9


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


At a minimum you should at least specify 2. This is to necessary for Access to work well with MySQL. The rest are optional but you do really need to have the 2.
Okay.
Two very important things.
1) If you want to use linked tables, Access saved queries, etc. you are actually using DAO. If you want to use ADO, you must supply the SQL all by yourself. While you can bind forms to an ADO recordset, you have to do that in VBA; you couldn't just say, open form in the design view, go to Data tab and change the Recordsource because that is using DAO, not ADO.
2) There is a reason why I use ADO only 5% of time: DAO already does a great job of working well with ODBC sources and by sticking to DAO, I can continue to use Access objects and techniques that are not only familiar but also requires less coding. I happily reach for ADO when I need something that depends on specialized features available only in backend... writing in its native dialect or to run a stored procedure that I can then bind to the form *and* update the resultset. But most of time, I don't really need that, so DAO covers the 95%.
So if you want to be able to use your existing Append Query, you may find it much easier to stick with DAO. To use ADO for the same operation, you would actually do something like:
CODE
With MySQLConn
   .Execute "..."
   .Execute "..."
   .Execute "..."
   .Execute "..."
   .Execute "..."
   ...
End With
With rs
   ...
   .Open
End With

Basically, where you have Append, Update, Delete queries, you use Execute method. When you do a SELECT, you do a Open. Do note that you can't necessarily just copy the SQL from your original Access query to ADO's statement because you now have to use native dialect. So if we had a Access Delete Query:
CODE
DELETE * FROM my_tbl;

and put it in VBA:
CODE
MySQLConn.Execute "DELETE * FROM my_tbl;"

This would fail because MySQL does not recongize the form "DELETE <column-list>" that Jet uses to presumably makes it easy to view the effect of deletion. So you have to remove the "*" to get it to work. There are more but that's one idea.
I hope this helps...
Go to the top of the page
 
Sako
post Jul 17 2010, 12:41 PM
Post#10



Posts: 577
Joined: 25-October 08
From: Canada


Thanks Banana,
You really made it clear ADO vs DAO. I was already planning to go with ADO (saved queries, Access objects, etc).
However, I am still not clear as to what the process is to automate the login process. I have the code that you provided, do I still need that code even if I use DAO?
Go to the top of the page
 
BananaRepublic
post Jul 17 2010, 12:57 PM
Post#11


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


The sample contains both code to support both DAO and ADO.
If you wanted to use DAO exclusively, you probably only need the FixConnections and GenerateIndex sample. If you want to support ADO as well, just import the entire modODBCConn module and change the parameters.
In the startup, you would call FixConnections, meaning that your linked tables will get refreshed every time it's started up. This may not be strictly necessary but that way, the code is relatively straightforward in terms of keeping the links functional without having to prompt the users to fix the links or to check for changes to the links, etc.
HTH.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 01:42 AM