Problem connecting to SQL Server from Access Database

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • David McColl
    #1

    Problem connecting to SQL Server from Access Database

    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
    Tags: None
    • Erland Sommarskog
      #2
      Re: Problem connecting to SQL Server from Access Database

      David McColl (writetodaveo@g mail.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@sommarsk og.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

        Comment

        • David McColl
          #3
          Re: Problem connecting to SQL Server from Access Database

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


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

          Cheers - David

            Comment

            • Erland Sommarskog
              #4
              Re: Problem connecting to SQL Server from Access Database

              David McColl (writetodaveo@g mail.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@sommarsk og.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

                Comment

                Previous template Next
                Working...