add password to circumvent mysql connector pop-up

[nik]

Hi,

This seems like it should be obvious, but various attempts just don't
seem to work.

I'm using Access '07 as a FE to MySQL BE, via ODBC 5.1, with linked
tables. Everything works well, except whenever Access first connects
to the BE the MySQL Connector/ODBC Data Source Configuration pops up
asking for the user and password. I want the user to enter this
information into a custom login form and then set it with the code
instead of having to use the pop-up.

I've tried adding the user name and password to the tabledefs.connect
and then using RefreshLink to establish the connection for the first
time:

sConnect = "ODBC;Driver={MySQL ODBC 5.1 Driver};" & _
"SERVER=" & mdlConst.sMYSQLSERVERNAME & ";" & _
"PORT=3306;" & _
"DATABASE=" & mdlConst.sMYSQLDATABASENAME & ";" & _
"USER=" & mdlConst.sMySQLUSERNAME & ";" & _
"PASSWORD=" & sPassword ' & ";"

For i = 0 To CurrentDb.TableDefs.Count - 1
If (CurrentDb.TableDefs(i).Connect <> "") Then
CurrentDb.TableDefs(i).Connect = sConnect
CurrentDb.TableDefs(i).RefreshLink
End If
Next

But the MySQL ODBC Configuration still pops up asking for the username
and password.

Interestingly, the CurrentDb.TableDefs(i).Connect does not have the
user name or password in the string after the CurrentDb.TableDefs
(i).RefreshLink command.

I've also tried UID and PWD instead of USER and PASSWORD, without
success.

Am I missing something? I'm not trying to change the table link, I
just want to set the username and password through the code instead of
having someone have to use the configuration pop-up.

Thanks,
Nik

 

[S.Clark]

Perhaps the MySQL newsgroups has people that can answer this?

 

[Hans Up]

I've tried adding the user name and password to the tabledefs.connect
and then using RefreshLink to establish the connection for the first
time:

sConnect = "ODBC;Driver={MySQL ODBC 5.1 Driver};" & _
"SERVER=" & mdlConst.sMYSQLSERVERNAME & ";" & _
"PORT=3306;" & _
"DATABASE=" & mdlConst.sMYSQLDATABASENAME & ";" & _
"USER=" & mdlConst.sMySQLUSERNAME & ";" & _
"PASSWORD=" & sPassword ' & ";"

You have a lone single quote after sPassword. Will it work if you
discard that quote?

If that change doesn't cure the problem, add "Debug.Print sConnect" and
view the completed connection string in the Immediate Window. (You
didn't tell us what mdlConst represents.) It could be useful for you to
verify your code builds the actual connection string you're expecting.

 

[nik]

You have a lone single quote after sPassword.  Will it work if you
discard that quote?

If that change doesn't cure the problem, add "Debug.Print sConnect" and
view the completed connection string in the Immediate Window.  (You
didn't tell us what mdlConst represents.) It could be useful for you to
verify your code builds the actual connection string you're expecting.

Hi,

The sConnect string is being built correctly and does connect
correctly in other scenerios, but I gave up on this general approach
and found a different method that actually works. I guess (not
confirmed) there must be something preventing the Connect string from
being updated on an existing TableDef. Instead of updating the Connect
string and then calling RefreshLink a TableDef I now individually
replace the TableDefs using the DoCmd.TransferDatabase command.

DoCmd.TransferDatabase(TransferType, DatabaseType, DatabaseName,
ObjectType, Source, Destination, StructureOnly, StoreLogin)

My code now looks like:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim dbLocal As DAO.Database
Dim sConnect As String
sConnect = "ODBC;Driver={MySQL ODBC 5.1 Driver};" & _
"SERVER=" & mdlConst.sMYSQLSERVERNAME & ";" & _
"PORT=3306;" & _
"DATABASE=" & mdlConst.sMYSQLDATABASENAME & ";" & _
"USER=" & mdlConst.sMySQLUSERNAME & ";" & _
"PASSWORD=" & sPassword ' & ";"
Set dbLocal = CurrentDb()

dbLocal.TableDefs.Delete mdlConst.sLOCALLINKTABLENAME
dbLocal.TableDefs.Refresh
DoCmd.TransferDatabase acLink, "ODBC Database", _
sConnect, acTable, mdlConst.sREMOTELINKTABLENAME,
mdlConst.sLOCALLINKTABLENAME

'....

dbLocal.Close

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Thanks for all the help,
Nik