Problem connecting to SQL Server from Access Database

[Daveo]

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

[Rich P]

Hi Dave,

Have you tried creating a new ODBC Dsn from Control panel since the DB
migration? Try creating a new ODBC Dsn from the Control Panel and then
try connecting to a table in the DB using this Dsn. If you can't create
the Dsn (you can't see the server from the Control Panel) then it is a
network problem. If you can create the Dsn but cannot access/see any
data in a data table from you mdb - then there is either a problem with
the workstation or with the DB. If you can't see data from the ODBC
connection - try creating a new blank mdb and see if you can see any
data from the ODBC connection in the new mdb.

If everything works normal with the ODBC connection then there is
something wrong with the code statement you are using. Perhaps you
could share your code statement.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Daveo]

Hi Rich,

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*****

The cn.Open (gcstSQLServer_Directa) part refers to our constant
section of the code - I've tested it and the connection settings are
definitely correct.

Cheers - David


On Jun 12, 4:14 pm, Rich P <[e-mail]:

Hi Dave,

Have you tried creating a new ODBC Dsn from Control panel since the DB
migration? Try creating a new ODBC Dsn from the Control Panel and then
try connecting to a table in the DB using this Dsn. If you can't create
the Dsn (you can't see the server from the Control Panel) then it is a
network problem. If you can create the Dsn but cannot access/see any
data in a data table from you mdb - then there is either a problem with
the workstation or with the DB. If you can't see data from the ODBC
connection - try creating a new blank mdb and see if you can see any
data from the ODBC connection in the new mdb.

If everything works normal with the ODBC connection then there is
something wrong with the code statement you are using. Perhaps you
could share your code statement.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

[lyle fairfield]

It would seem that something closes the connection, cn, before
"cn.Close" errors. What? I see nothing in the code you show us that
does; the connection is open at "rs.Open stSQL, cn, adOpenDynamic,
adLockReadOnly" or we'd error out there and "cn" is not referenced
again until the line that errors.

So where?

The presence of "Exit_fnbValidUserIDPassword:" implies the presence
of "Err_fnbValidUserIDPassword:" or similar. One can guess (one chance
in a hundred) that code referenced by that label closes the
connection, cn.

On Jun 13, 6:20*am, Daveo <[e-mail]:

Hi Rich,

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*****

The cn.Open (gcstSQLServer_Directa) part refers to our constant
section of the code - I've tested it and the connection settings are
definitely correct.

Cheers - David

On Jun 12, 4:14 pm, Rich P <[e-mail]:

Hi Dave,

Have you tried creating a new ODBC Dsn from Control panel since the DB
migration? *Try creating a new ODBC Dsn from the Control Panel and then
try connecting to a table in the DB using this Dsn. *If you can't create
the Dsn (you can't see the server from the Control Panel) then it is a
network problem. *If you can create the Dsn but cannot access/see any
data in a data table from you mdb - then there is either a problem with
the workstation or with the DB. *If you can't see data from the ODBC
connection - try creating a new blank mdb and see if you can see any
data from the ODBC connection in the new mdb.

If everything works normal with the ODBC connection then there is
something wrong with the code statement you are using. *Perhaps you
could share your code statement.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

[Daveo]

Hi Lyle,

Thanks for your reply. Am I right in thinking that the code is
hitting an error and (as per On Error GoTo
Exit_fnbValidUserIDPassword ) is closing the connection? Could it be
trying to close the connection before it is opened?

Many thanks,

David

[lyle fairfield]

On Jun 13, 8:15*am, Daveo <[e-mail]:

Hi Lyle,

Thanks for your reply. *Am I right in thinking that the code is
hitting an error and (as per On Error GoTo
Exit_fnbValidUserIDPassword ) is closing the connection? *Could it be
trying to close the connection before it is opened?

Many thanks,

David

That's my guess. You have all the code. Is there and "On Error
Goto ..." line? It seems something is closing CN.

If you add the line, "Debug.Print cn.State"

before cn.Close

then the immediate window shows 0 (zero)?

[Daveo]

Hi Lyle,

Thanks for the response - yes I get a 0 when adding the line. The
only On Error Goto.. line is On Error GoTo
Exit_fnbValidUserIDPassword.

Many thanks,

David

[lyle fairfield]

If you comment this line out and run the code, you may find the
initial, real error.
On Jun 13, 8:46*am, Daveo <[e-mail]:

Hi Lyle,

Thanks for the response - yes I get a 0 when adding the line. *The
only On Error Goto.. line is On Error GoTo
Exit_fnbValidUserIDPassword.

Many thanks,

David

[Daveo]

Hi Lyle,

Thanks for all your help - it appears that someone (helpfully) decided
to change the uid and pw on the database without telling anyone.
Should have really checked that before bothering you, but everything
was supposed to be identical.
Thanks again - David