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]

Re: Problem connecting to SQL Server from Access Database

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]

Re: Problem connecting to SQL Server from Access Database

Hi Rich,

Our code is a follows:

Public Function fnbValidUserIDP assword(ByVal stUserID As String, ByVal
stPassword As String) As Boolean
On Error GoTo Exit_fnbValidUs erIDPassword
Dim mEncrypt As New MessageDigest5
Dim cn As New ADODB.Connectio n
Dim rs As New ADODB.Recordset
Dim rst As Recordset
Dim stSQL As String

fnbValidUserIDP assword = 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
fnbValidUserIDP assword = (CInt(rs![UserCount]) = 1)
If Not (fnbValidUserID Password) Then
stSQL = "SELECT count(*) as UserCount from Authoriser
where AuthoriserID = '" & stUserID & "' and Password = '" & stPassword
& "' and DirectaUser = false"
Set rst = CurrentDb.OpenR ecordset(stSQL, dbOpenSnapshot,
dbInconsistent, dbReadOnly)
If Not (rst.EOF) Then
rst.MoveFirst
fnbValidUserIDP assword = (CInt(rst!UserC ount) = 1)
End If
End If
End If

Exit_fnbValidUs erIDPassword:

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 <rpng...@aol.co mwrote:

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 Developersdexht tp://www.developersd ex.com***

[lyle fairfield]

Re: Problem connecting to SQL Server from Access Database

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_fnbValidU serIDPassword:" implies the presence
of "Err_fnbValidUs erIDPassword:" 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 <writetoda...@g mail.comwrote:

Hi Rich,
>
Our code is a follows:
>
Public Function fnbValidUserIDP assword(ByVal stUserID As String, ByVal
stPassword As String) As Boolean
On Error GoTo Exit_fnbValidUs erIDPassword
Dim mEncrypt As New MessageDigest5
Dim cn As New ADODB.Connectio n
Dim rs As New ADODB.Recordset
Dim rst As Recordset
Dim stSQL As String
>
    fnbValidUserIDP assword = 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
        fnbValidUserIDP assword = (CInt(rs![UserCount]) = 1)
        If Not (fnbValidUserID Password) Then
            stSQL = "SELECT count(*) as UserCount from Authoriser
where AuthoriserID = '" & stUserID & "' and Password = '" & stPassword
& "' and DirectaUser = false"
            Set rst = CurrentDb.OpenR ecordset(stSQL, dbOpenSnapshot,
dbInconsistent, dbReadOnly)
            If Not (rst.EOF) Then
                rst.MoveFirst
                fnbValidUserIDP assword = (CInt(rst!UserC ount) = 1)
            End If
        End If
    End If
>
Exit_fnbValidUs erIDPassword:
>
    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 <rpng...@aol.co mwrote:
>

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 Developersdexht tp://www.developersd ex.com***

[Daveo]

Re: Problem connecting to SQL Server from Access Database

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_fnbValidUs erIDPassword ) is closing the connection? Could it be
trying to close the connection before it is opened?

Many thanks,

David

[lyle fairfield]

Re: Problem connecting to SQL Server from Access Database

On Jun 13, 8:15 am, Daveo <writetoda...@g mail.comwrote:

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_fnbValidUs erIDPassword ) 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.Prin t cn.State"

before cn.Close

then the immediate window shows 0 (zero)?

[Daveo]

Re: Problem connecting to SQL Server from Access Database

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_fnbValidUs erIDPassword.

Many thanks,

David

[lyle fairfield]

Re: Problem connecting to SQL Server from Access Database

If you comment this line out and run the code, you may find the
initial, real error.


On Jun 13, 8:46 am, Daveo <writetoda...@g mail.comwrote:

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_fnbValidUs erIDPassword.
>
Many thanks,
>
David

[Daveo]

Re: Problem connecting to SQL Server from Access Database

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