Error Code 3709
am 20.03.2006 22:16:55 von Vic Spainhower------=_NextPart_000_0039_01C64C20.90526060
Content-Type: multipart/alternative;
boundary="----=_NextPart_001_003A_01C64C20.9054D160"
------=_NextPart_001_003A_01C64C20.9054D160
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: 7bit
Hello,
I have a MySQL database on a remote host that I am connecting to using
MyODBC version 3.51.11 and MS Access 2002. I just moved this dataabase from
a Windows host to a Linux host and now I'm have trouble on client machines
creating a recordset. I have no problem on any of 5 different machines in my
office but I'm getting the error on a number of my clients computers in
other locations. I am not able to reproduce the error so I'm having a heck
of a time tryiing to resolve the problem. I installed MyODBC 3.51.12 and got
the same results. I have indicated and highlighted the error that is
occuring and where it occurs in the following code sample.
The error occurs on the rs.open in the following code. I would appreciate
some help in why this may be occuring as I am fresh out of ideas on why this
is happening.
Thanks very much
Vic
Dim objRsShows As ADODB.Recordset
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
On Error GoTo ErrorHandler
Set objRsShows = New ADODB.Recordset
objRsShows.ActiveConnection = CurrentProject.Connection
objRsShows.CursorType = adOpenForwardOnly
objRsShows.LockType = adLockOptimistic
objRsShows.Open ("Select WebLoginName, WebPassword From tblEventMaster")
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=www.dbhost.com;" _
& "DATABASE=myDatabase;" _
& "UID=myUID;" _
& "PWD=myPW;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
conn.CursorLocation = adUseClient
conn.Open
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
If IsNull(objRsShows.Fields("WebLoginName")) _
Or IsNull(objRsShows.Fields("WebPassword")) Then
MsgBox "Please supply Web Site Logon Information!", vbCritical,
"Show Secretary"
Exit Sub
End If
' *** The following statement produces the error code 3709 "The
connection cannot be used to perform this operation. It is either closed or
invalid in this context.
rs.Open "SELECT LoginName, UserPassword FROM user WHERE LoginName = '" &
objRsShows.Fields("WebLoginName") & "'", conn, adOpenStatic,
adLockOptimistic
MsgBox "recordset created", vbInformation, "Show Secretary"
------=_NextPart_001_003A_01C64C20.9054D160
Content-Type: text/html;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
charset=3Dus-ascii">
Hello,
I have a MySQL database on a remote =
host that=20
I am connecting to using MyODBC version 3.51.11 and MS Access =
2002. I=20
just moved this dataabase from a Windows host to a Linux host and now =
I'm have=20
trouble on client machines creating a recordset. I have no problem on =
any of 5=20
different machines in my office but I'm getting the error on a number of =
my=20
clients computers in other locations. I am not able to reproduce =
the error=20
so I'm having a heck of a time tryiing to resolve the problem. I =
installed=20
MyODBC 3.51.12 and got the same results. I have indicated and =
highlighted the=20
error that is occuring and where it occurs in the following code=20
sample.
The error occurs on the rs.open in =
the=20
following code. I would appreciate some help in why this may be occuring =
as I am=20
fresh out of ideas on why this is happening.
Thanks very much
Vic
color=3D#000000> Dim=20
objRsShows As ADODB.Recordset
Dim conn =
As=20
ADODB.Connection
Set conn =
=3D New=20
ADODB.Connection
ErrorHandler
color=3D#000000> Set=20
objRsShows =3D New ADODB.Recordset
=20
objRsShows.ActiveConnection =3D =
CurrentProject.Connection
=20
objRsShows.CursorType =3D adOpenForwardOnly
=20
objRsShows.LockType =3D adLockOptimistic
=
objRsShows.Open=20
("Select WebLoginName, WebPassword From =
tblEventMaster")
color=3D#000000> =20
conn.ConnectionString =3D "DRIVER=3D{MySQL ODBC 3.51 Driver};"=20
_
=
&=20
"SERVER=3Dwww.dbhost.com;"=20
_
=
&=20
"DATABASE=3DmyDatabase;"=20
_
=
&=20
"UID=3DmyUID;"=20
_
=
&=20
"PWD=3DmyPW;"=20
_
=
&=20
"OPTION=3D" & 1 + 2 + 8 + 32 + 2048 + 16384
color=3D#000000> =20
conn.CursorLocation =3D adUseClient
=20
conn.Open
Dim rs As =
ADODB.Recordset
=20
Set rs =3D New =
ADODB.Recordset
=20
If IsNull(objRsShows.Fields("WebLoginName"))=20
_
Or=20
IsNull(objRsShows.Fields("WebPassword"))=20
Then
MsgBox "Please supply =
Web=20
Site Logon Information!", vbCritical, "Show=20
Secretary"
Exit=20
Sub
End If
statement=20
produces the error code 3709 "The connection cannot be used to perform =
this=20
operation.
class=3D875214920-20032006>
invalid in this=20
context.
class=3D875214920-20032006>
UserPassword FROM=20
user WHERE LoginName =3D '" & objRsShows.Fields("WebLoginName") =
& "'",=20
conn, adOpenStatic, adLockOptimistic
MsgBox =
"recordset=20
created", vbInformation, "Show =
Secretary"
------=_NextPart_001_003A_01C64C20.9054D160--
------=_NextPart_000_0039_01C64C20.90526060--