Error Code 3709

------=_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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE id=3DridTitle>Clear Day</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<STYLE>BODY {
MARGIN-TOP: 25px; FONT-SIZE: 10pt; MARGIN-LEFT: 10px; COLOR: #0033cc; =
FONT-FAMILY: Arial, Helvetica
}
</STYLE>

<META content=3D"MSHTML 6.00.2900.2802" name=3DGENERATOR></HEAD>
<BODY id=3DridBody background=3Dcid:875214920 [at] 20032006-11D1>
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 =
=3D New
ADODB.Connection On Error GoTo
ErrorHandler 
 Set
objRsShows =3D New ADODB.Recordset 
objRsShows.ActiveConnection =3D =
CurrentProject.Connection 
objRsShows.CursorType =3D adOpenForwardOnly 
objRsShows.LockType =3D adLockOptimistic =
objRsShows.Open
("Select WebLoginName, WebPassword From =
tblEventMaster") 

conn.ConnectionString =3D "DRIVER=3D{MySQL ODBC 3.51 Driver};"
_ =
&
"SERVER=3Dwww.dbhost.com;"
_ =
&
"DATABASE=3DmyDatabase;"
_ =
&
"UID=3DmyUID;"
_ =
&
"PWD=3DmyPW;"
_ =
&
"OPTION=3D" & 1 + 2 + 8 + 32 + 2048 + 16384

conn.CursorLocation =3D adUseClient 
conn.Open Dim rs As =
ADODB.Recordset 
Set rs =3D 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 =3D '" & objRsShows.Fields("WebLoginName") =
& "'",
conn, adOpenStatic, adLockOptimistic MsgBox =
"recordset
created", vbInformation, "Show =
Secretary" </BODY></HTML>

------=_NextPart_001_003A_01C64C20.9054D160--

------=_NextPart_000_0039_01C64C20.90526060--

--------------090501000009060102080705
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Vic Spainhower wrote:

<snipped >
>
> * ' *** 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"
>
Have you allowed these remote computers to connect to MySQL? If the only
difference between the working PCs and the non-working PCs is the
network location, then it sure sounds like a problem with permissions
for the remove PCs. Have a look in the docs about setting up user
accounts and specifying which IP addresses can connect.

Dan

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak [at] nusconsulting.com.au
website: http://www.nusconsulting.com.au

--------------090501000009060102080705--

>> Have you allowed these remote computers to connect to MySQL? Have a look
in the docs about setting up user accounts and specifying which IP addresses
can connect.

Dan,

Thanks for responding and that is exactly what the problem is. I moved the
site from a hosting company where the IP address did not have to be white
listed but the new one does require it. My problem is many of the clients
have dynamic IP addresses and I'm not sure how to resolve this so they can
be permanently authenticated. Any ideas?

Thanks,

Vic

-----Original Message-----
From: Daniel Kasak [mailto:dkasak [at] nusconsulting.com.au]
Sent: Monday, March 20, 2006 7:38 PM
To: Vic Spainhower
Cc: myodbc [at] lists.mysql.com
Subject: Re: Error Code 3709

Vic Spainhower wrote:

<snipped >
>
> * ' *** 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"
>
Have you allowed these remote computers to connect to MySQL? If the only
difference between the working PCs and the non-working PCs is the network
location, then it sure sounds like a problem with permissions for the remove
PCs. Have a look in the docs about setting up user accounts and specifying
which IP addresses can connect.

Dan

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak [at] nusconsulting.com.au
website: http://www.nusconsulting.com.au

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc [at] m.gmane.org

>> Have you allowed these remote computers to connect to MySQL? Have a
>> look
in the docs about setting up user accounts and specifying which IP addresses
can connect.

Dan,

Thanks for responding and that is exactly what the problem is. I moved the
site from a hosting company where the IP address did not have to be white
listed but the new one does require it. My problem is many of the clients
have dynamic IP addresses and I'm not sure how to resolve this so they can
be permanently authenticated. Any ideas?

BTW - I do have the Access Hosts set to % in CPANEL but the issue is the
firewall which the provider has indicated they have to specify the IP to
grant access to the database.

Thanks,

Vic

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc [at] m.gmane.org

Vic Spainhower wrote:
>
>
>>> Have you allowed these remote computers to connect to MySQL? Have a look
>>>
> in the docs about setting up user accounts and specifying which IP addresses
> can connect.
>
> Dan,
>
> Thanks for responding and that is exactly what the problem is. I moved the
> site from a hosting company where the IP address did not have to be white
> listed but the new one does require it. My problem is many of the clients
> have dynamic IP addresses and I'm not sure how to resolve this so they can
> be permanently authenticated. Any ideas?
>
Ouch!

If they have dynamic IPs, then you are in for some seriously dodgy fudging.

The 1st thing that comes to mind is to use something like dyndns (
http://www.dyndns.org ). However I think that MySQL will want to do a
reverse DNS lookup ( to get a domain name from an IP address ), and I'm
not sure if dyndns will allow this from a free account. There's a page
at https://www.dyndns.com/support/kb/archives/reverse_dns.html that
talks a bit about it - have a read of it, and also post to the main
mysql list ( mysql [at] lists.mysql.com ) to see if someone has done
something like this before.

Next is something dodgy like having a 'proxy' type setup - you give the
proxy server access, and everyone connects via it. You then forward all
MySQL traffic ( ie port fowarding with iptables ) from the proxy to the
actual server. This solution sounds like it stinks, but it might work.

Lastly, you could have something dodgy like a web page that your clients
have to hit before they can log in. You might get them to enter a
username / password here, for security. If they authenticate, a user
account on the MySQL server is set up for them with their current IP
address. You'd want to then remove accounts when they log out though.

Take your pick. Maybe post to that main mysql list with something like
'Dynamic IP address and authenticating' in the subject, and see what
others think.

Good luck :)

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak [at] nusconsulting.com.au
website: http://www.nusconsulting.com.au

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc [at] m.gmane.org

Error Code 3709

Re: Error Code 3709

RE: Error Code 3709

RE: Error Code 3709

Re: Error Code 3709