DSN Less String in MySQL

jeff1470

New member
Local time
Today, 16:55
Joined
Oct 25, 2017
Messages
1
Hi all

I keep getting a 424 object error in the code and I can't find where the issue is.
Basically what the code does is, when a person gets to the login form, he puts his details in and when he clicks on login, this code will execute to save the connection string temporarily and then discard when the database closes.

Public Function InitConnect(Optional UserName As String, Optional Password As String) As Boolean
On Error GoTo ErrHandler

Dim dbCurrent As dao.Database
Dim qdf As dao.QueryDef
Dim rst As dao.Recordset

strConnection = "ODBC;DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
"Server=" & SERVER & ";" & _
"Port=" & 3306 & ";" & _
"Option=" & 0 & ";" & _
"Database=" & DATABASENAME & ";"

Set dbCurrent = DBEngine(0)(0)
Set qdf = dbCurrent.CreateQueryDef("")

With qdf
.Connect = strConnection & _
"Uid=" & USERNAME & ";" & _
"Pwd=" & PWD
.SQL = "SELECT CURRENT_USER();"
Set rst = .OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
End With
InitConnect = True

ExitProcedure:
On Error Resume Next
Set rst = Nothing
Set qdf = Nothing
Set dbCurrent = Nothing
Exit Function
ErrHandler:
InitConnect = False
MsgBox Err.Description & " (" & Err.Number & ") encountered", _
vbOKOnly + vbCritical, "InitConnect"
Resume ExitProcedure
Resume
End Function

Appreciated.
Jeff
 
Hi Jeff

I know nothing about MySQL but I have noticed that in your connection string.....

strConnection = "ODBC;DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
"Server=" & SERVER & ";" & _
"Port=" & 3306 & ";" & _
"Option=" & 0 & ";" & _
"Database=" & DATABASENAME & ";"

.......the values of "SERVER" and "DATABASENAME" were not passed as parameters into your function

If they are defined and set as global variables then there won't be a problem otherwise they may be empty strings ?

George
 
Also noticed that Variable "Password" is being passed into the function.......

Public Function InitConnect(Optional UserName As String, Optional Password As String) As Boolean

..........but you are using variable "PWD" as part of the connection string in your querydef

With qdf
.Connect = strConnection & _
"Uid=" & USERNAME & ";" & _
"Pwd=" & PWD
.SQL = "SELECT CURRENT_USER();"
Set rst = .OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
End With
InitConnect = True
 

Users who are viewing this thread

Back
Top Bottom