SQL Server does not exist or access denied

Discussion in 'Microsoft Access Macros' started by Eric Bragas, Dec 3, 2008.

  1. Eric Bragas

    Eric Bragas Guest

    Hi All,

    I have an Access 2003 file that keeps giving the same error message to
    two users on file open, but the other two users are not getting the
    error. I'm responsible for figuring out why this error is occurring
    so that I can resolve it, but so far I'm stumped. The error message
    the user receives is "SQL Server does not exist or access is denied."

    The very first form that appears on startup sets a "strSQL" value and
    then calls the following declared procedure:

    Set rst = GetRecordset(strSQL, GetSQLServerConnectString)

    Since GetSQLServerConnectString is a function, it executes before
    GetRecordset. It is:

    Public Function GetSQLServerConnectString()
    GetSQLServerConnectString = "Provider=SQLOLEDB.1;Persist Security
    Info=True;" & _
    "User
    ID=one_user;Timeout=60;Password=one_user_pw;" & _
    "Data Source=MYSQLSERVER;" & _
    "Initial Catalog=MYDATABASE;"
    End Function

    And then the GetRecordset function gets called:

    Public Function GetRecordset(strSQL, connectionString)
    'This function returns a recordset variable; it encapsulates
    'all the commonly-used variables for calling disconnected recordsets
    'for read-only operations

    Const adOpenForwardOnly = 0
    Const adLockReadOnly = 1
    Const adUseClient = 3

    Dim objRs
    Dim oConn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rst As New ADODB.Recordset

    oConn.Open connectionString
    .....
    End Function

    A user who's file keeps throwing the error on file open says that:
    1. If she chooses "debug," she's taken straight to the oConn.Open
    connectionString line;
    2. If she clicks "OK" at the error, she's able to use the file as
    normal. (Changes save, etc.)

    So my question:
    How in the world can this be causing a "SQL Server does not exist or
    access denied" error when every user is connecting as "one_user"? If
    I look at SQL Server activity, I can see "one_user" is connected to
    "MYDATABASE".

    Thanks,
    Eric
     
    Eric Bragas, Dec 3, 2008
    #1
    1. Advertisements

  2. Eric,

    As your question is based on VBA procedures, and does not realte to Access
    Macros, you would have a better chance of a good answer if you repost to a
    more applicable newsgroup. Perhaps microsoft.public.access.externaldata.

    --
    Steve Schapel, Microsoft Access MVP

    "Eric Bragas" <> wrote in message
    news:...
    > Hi All,
    >
    > I have an Access 2003 file that keeps giving the same error message to
    > two users on file open, but the other two users are not getting the
    > error. I'm responsible for figuring out why this error is occurring
    > so that I can resolve it, but so far I'm stumped. The error message
    > the user receives is "SQL Server does not exist or access is denied."
     
    Steve Schapel, Dec 3, 2008
    #2
    1. Advertisements

  3. Hello Eric.

    Eric Bragas wrote:
    > I have an Access 2003 file that keeps giving the same error message to
    > two users on file open, but the other two users are not getting the
    > error. I'm responsible for figuring out why this error is occurring
    > so that I can resolve it, but so far I'm stumped. The error message
    > the user receives is "SQL Server does not exist or access is denied."
    >
    > The very first form that appears on startup sets a "strSQL" value and
    > then calls the following declared procedure:
    >
    > Set rst = GetRecordset(strSQL, GetSQLServerConnectString)
    >
    > Since GetSQLServerConnectString is a function, it executes before
    > GetRecordset. It is:
    >
    > Public Function GetSQLServerConnectString()
    > GetSQLServerConnectString = "Provider=SQLOLEDB.1;Persist Security
    > Info=True;" & _
    > "User
    > ID=one_user;Timeout=60;Password=one_user_pw;" & _
    > "Data Source=MYSQLSERVER;" & _
    > "Initial Catalog=MYDATABASE;"
    > End Function
    > [...]


    I guess that it's a name resolution problem. Try to "ping" the server:
    Open a command prompt and enter: ping mysqlserver
    If you don't get 4 successful answers displayed on the two computers,
    compare the DNS and WINS settings in the TCP/IP properties of the
    network adapters and also check the hosts and lmhosts files.

    --
    Regards,
    Wolfgang
     
    Wolfgang Kais, Dec 3, 2008
    #3
  4. Eric Bragas

    Eric Bragas Guest

    Wolfgang,

    Thanks for your advice, but I don't think it applies very well in this
    case. The server name is hardcoded into the mdb file. If one user is
    able to connect to the server, then then server name is correct for
    every user that attempts to connect to the server. It's not even a
    variable, it's a literal string. You can see that in the
    GetSQLServerConnectString function above.

    Eric
     
    Eric Bragas, Dec 4, 2008
    #4
  5. Eric Bragas

    Eric Bragas Guest

    Thanks, Steve, I'll give that a try.
     
    Eric Bragas, Dec 4, 2008
    #5
  6. Hello Eric.

    Eric Bragas wrote:
    > Wolfgang,
    >
    > Thanks for your advice, but I don't think it applies very well in this
    > case. The server name is hardcoded into the mdb file. If one user is
    > able to connect to the server, then then server name is correct for
    > every user that attempts to connect to the server. It's not even a
    > variable, it's a literal string. You can see that in the
    > GetSQLServerConnectString function above.


    I did not say that it was a name problem, but a name resolutuion problem.
    Before making a connection to server MYSQLSERVER, the client computers
    have to resolve an IP address for that name. On a computer that is well
    configured, a "ping" for that servername will result in 4 replies from
    the correct server. Ask you network administrator for help.

    --
    Regards,
    Wolfgang
    (..., MCSE, ...)
     
    Wolfgang Kais, Dec 5, 2008
    #6
  7. Eric Bragas

    Eric Bragas Guest

    Wolfgang,

    Thanks for the clarification, I will definitely check that out. I
    wouldn't have considered that possibility.

    The user hasn't contacted me in a week so I haven't been able to try
    your advice yet. I will post the results when the time comes.

    Again, thank you.

    -Eric
     
    Eric Bragas, Dec 8, 2008
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. tob baker

    Tools-Macro-Security Option Does not Exist!

    tob baker, Oct 10, 2003, in forum: Microsoft Access Macros
    Replies:
    5
    Views:
    778
  2. Guest

    Access Macros to SQL Server

    Guest, Feb 16, 2005, in forum: Microsoft Access Macros
    Replies:
    0
    Views:
    172
    Guest
    Feb 16, 2005
  3. Guest

    Error message: Form doesn't exist

    Guest, Jun 24, 2005, in forum: Microsoft Access Macros
    Replies:
    6
    Views:
    252
    Steve Schapel
    Jun 28, 2005
  4. Guest
    Replies:
    1
    Views:
    201
    Guest
    Jul 9, 2005
  5. Salil

    macros generating incorrect SQL in SQL Server 2000

    Salil, May 8, 2006, in forum: Microsoft Access Macros
    Replies:
    0
    Views:
    210
    Salil
    May 8, 2006
Loading...

Share This Page