Prev: Fuzzy logic for duplicate identification?
Next: MSDE upgraded to SP4 but showing SP2
From: David McColl on 12 Jun 2008 10:42
Dear all,

We recently migrated a SQL database from one server to another. Now,
when you try to run some code in an Access Database which links to the
SQL one, you get a 3704 error "Operation is not allowed when the
object is closed". I think this means that the connection between the
Access DB and SQL Server has not been made.

- The IP address of the new server is the same as the old one and the
host file on the client PCs has been updated to reflect the name
change.
- The server name in the connection details has been changed to the
new server name in the constants section of our VBA code.
- The new server is running SQL Server 8.0 on MS Server 2003
- The old server was running NT.
- When you hit Debug the line of code that closes the connection
(cn.Close) is highlighted as it has nothing to close (I think).

Everything else is exactly the same. Is there something I'm missing?
Any help would be greatly appreciated!

Many thanks - David
From: Erland Sommarskog on 12 Jun 2008 17:15
David McColl (writetodaveo(a)gmail.com) writes:
> We recently migrated a SQL database from one server to another. Now,
> when you try to run some code in an Access Database which links to the
> SQL one, you get a 3704 error "Operation is not allowed when the
> object is closed". I think this means that the connection between the
> Access DB and SQL Server has not been made.
>...
> - When you hit Debug the line of code that closes the connection
> (cn.Close) is highlighted as it has nothing to close (I think).

Hm, if you come that far, you have probably been able to open the
connection. Rather it seems that you somehow manage to close the
connection twice.

I think you should debug the code a little more in depth and step
through it to see what happens.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: David McColl on 13 Jun 2008 07:15
Our code is a follows:

Public Function fnbValidUserIDPassword(ByVal stUserID As String, ByVal
stPassword As String) As Boolean
On Error GoTo Exit_fnbValidUserIDPassword
Dim mEncrypt As New MessageDigest5
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rst As Recordset
Dim stSQL As String

fnbValidUserIDPassword = False

stSQL = "SELECT count(*) as UserCount from Users Where UserID = '"
& stUserID & "' and Password = '" & stPassword & "'"
cn.Open (gcstSQLServer_Directa)
rs.Open stSQL, cn, adOpenDynamic, adLockReadOnly

If Not (rs.EOF) Then
rs.MoveFirst
fnbValidUserIDPassword = (CInt(rs![UserCount]) = 1)
If Not (fnbValidUserIDPassword) Then
stSQL = "SELECT count(*) as UserCount from Authoriser
where AuthoriserID = '" & stUserID & "' and Password = '" & stPassword
& "' and DirectaUser = false"
Set rst = CurrentDb.OpenRecordset(stSQL, dbOpenSnapshot,
dbInconsistent, dbReadOnly)
If Not (rst.EOF) Then
rst.MoveFirst
fnbValidUserIDPassword = (CInt(rst!UserCount) = 1)
End If
End If
End If

Exit_fnbValidUserIDPassword:

cn.Close
Set cn = Nothing
Set rs = Nothing
End Function

****END OF CODE*****


Not sure why it doesn't work as I pointed it back at the old server
and it works fine...

Cheers - David

From: Erland Sommarskog on 13 Jun 2008 18:12
David McColl (writetodaveo(a)gmail.com) writes:
> stSQL = "SELECT count(*) as UserCount from Users Where UserID = '"
> & stUserID & "' and Password = '" & stPassword & "'"
> cn.Open (gcstSQLServer_Directa)

I don't know why you get this error, but I see now that may have a
much more serious problem to tackle. What is in that connect string?
That is, with which account do you connect? One with lots of privileges?

In that try to enter this for a password:

' CREATE TABLE killroywashere(a int NOT NULL) --

Use parameterised queries to deal with this security hole.

> rs.Open stSQL, cn, adOpenDynamic, adLockReadOnly

And generally, make your cursors client-side and static.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 | 
Pages: 1
Prev: Fuzzy logic for duplicate identification?
Next: MSDE upgraded to SP4 but showing SP2