Test Connection to SQL Server

Discussion in 'Microsoft Access VBA Modules' started by Ivan Grozney, Sep 4, 2009.

  1. Ivan Grozney

    Ivan Grozney Guest

    I have a A2K7 FE and SQL 05 BE. There is fairly tight security on the SQL
    Server for this database. I use Doug Steele's DSNless connection but need to
    test if the user is authorized to use/access to the database.

    So if User BOB has access, he gets in
    If User Gus doesn't, it times out after a while and gives some
    error.

    I would like Gus to get a quicker response and trap the error and have it
    tell him to contact our Risk Management group for access. I don't know where
    to start on this.

    tia

    Vanya
     
    Ivan Grozney, Sep 4, 2009
    #1
    1. Advertisements

  2. You can trap a logon error with sql server using DAO and you can avoid
    having that odbc message come up.

    How this trick is done is outlined here:

    ACC2000: How to Trap ODBC Logon Error Messages
    http://support.microsoft.com/kb/210319

    I use the following code based on the above KB article:

    Code:
    
    Function TestLogin(strCon As String) As Boolean
    
    On Error GoTo TestError
    
    Dim dbs          As DAO.Database
    Dim qdf          As DAO.QueryDef
    
    Set dbs = CurrentDb()
    Set qdf = dbs.CreateQueryDef("")
    
    qdf.Connect = strCon
    qdf.ReturnsRecords = False
    
    'Any SQL statement will work below.
    qdf.SQL = "SELECT * FROM calls"
    qdf.Execute
    
    TestLogin = True
    
    Exit Function
    
    TestError:
    TestLogin = False
    Exit Function
    
    End Function
    
    
    To call the above, I would go:
    
    
    strCon = "ODBC;DRIVER={sql server};" & _
    "SERVER=" & ServerName & ";" & _
    "DATABASE=" & DatabaseName & ";" & _
    "UID=" & UserID & ";" & _
    "PWD=" & USERpw & ";" & _
    "APP=Microsoft Office 2003;" & _
    "WSID=" & WSID
    
    If TestLogin(strCon) = True Then
    ' logon is ok!
    
    So, the above allows you to "test" your logon. I assume you also have some
    built in re-link code that allows you to re-link your tables. You can find
    that code here:
    
    The above is nice since it avoids both the long time out and also the system
    generated message.
    
    
    --
    Albert D. Kallal    (Access MVP)
    Edmonton, Alberta Canada
    
     
    Albert D. Kallal, Sep 4, 2009
    #2
    1. Advertisements

  3. Ivan Grozney

    John Spencer Guest

    Albert,

    Do you know of a way to modify the code when you are using a DSN-less
    connection? The poster specifically said he was using Doug Steele's DSNless
    connection method.

    John Spencer
    Access MVP 2002-2005, 2007-2009
    The Hilltop Institute
    University of Maryland Baltimore County

    Albert D. Kallal wrote:
    > You can trap a logon error with sql server using DAO and you can avoid
    > having that odbc message come up.
    >
    > How this trick is done is outlined here:
    >
    > ACC2000: How to Trap ODBC Logon Error Messages
    > http://support.microsoft.com/kb/210319
     
    John Spencer, Sep 4, 2009
    #3
  4. Ivan Grozney

    Ivan Grozney Guest

    Gents,

    Thank you for the prompt response.

    In Doug's code it says to use it as long as the copyright is not
    changed. So I think I am free to add this snippet to the DSNless code. I
    will be giving it a try today or tonight.

    "John Spencer" wrote:

    > Albert,
    >
    > Do you know of a way to modify the code when you are using a DSN-less
    > connection? The poster specifically said he was using Doug Steele's DSNless
    > connection method.
    >
    > John Spencer
    > Access MVP 2002-2005, 2007-2009
    > The Hilltop Institute
    > University of Maryland Baltimore County
    >
    > Albert D. Kallal wrote:
    > > You can trap a logon error with sql server using DAO and you can avoid
    > > having that odbc message come up.
    > >
    > > How this trick is done is outlined here:
    > >
    > > ACC2000: How to Trap ODBC Logon Error Messages
    > > http://support.microsoft.com/kb/210319

    >
     
    Ivan Grozney, Sep 4, 2009
    #4
  5. "John Spencer" <> wrote in message
    news:...
    > Albert,
    >
    > Do you know of a way to modify the code when you are using a DSN-less
    > connection? The poster specifically said he was using Doug Steele's
    > DSNless connection method.
    >


    The example I gave actually works well even with a DSN less. In fact I only
    use DSN less connections with that code snip.

    Note in code:

    Set dbs = CurrentDb()
    Set qdf = dbs.CreateQueryDef("")

    qdf.Connect = strCon <--- connection string here is DSN less
    qdf.ReturnsRecords = False

    In my example you can see that I set the connection setup "strCon" is thus
    DSN less

    It works VERY well, and it far better then the ODBC timeout msg.

    My code does assume you have someplace the connection string setup with
    logon info. However, you can/could pull that out of any existing linked
    table with ease.

    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
     
    Albert D. Kallal, Sep 4, 2009
    #5
  6. Ivan Grozney

    Ivan Grozney Guest

    Albert,

    Thanks for all your insight and help.

    However, I guess I had too many helpings of Ding Dong flakes this
    morning. I cannot get it to work for me.

    I have a splash screen with a time so that I can check the SQL
    Database for their access level. So that if they are a general user the can
    do X and super user Y and if a member of the risk folks they get Z. So I was
    trying to put it in there to test before I do all the other stuff but it just
    hangs up.

    In your first post that you "assume you also have some built in re-link
    code... You can find that code here: "

    I don't have any re-link code and I didn't see it in your response,
    maybe that is the issue.

    Any further guidance would be much appreciated.

    Vanya



    "Albert D. Kallal" wrote:

    > "John Spencer" <> wrote in message
    > news:...
    > > Albert,
    > >
    > > Do you know of a way to modify the code when you are using a DSN-less
    > > connection? The poster specifically said he was using Doug Steele's
    > > DSNless connection method.
    > >

    >
    > The example I gave actually works well even with a DSN less. In fact I only
    > use DSN less connections with that code snip.
    >
    > Note in code:
    >
    > Set dbs = CurrentDb()
    > Set qdf = dbs.CreateQueryDef("")
    >
    > qdf.Connect = strCon <--- connection string here is DSN less
    > qdf.ReturnsRecords = False
    >
    > In my example you can see that I set the connection setup "strCon" is thus
    > DSN less
    >
    > It works VERY well, and it far better then the ODBC timeout msg.
    >
    > My code does assume you have someplace the connection string setup with
    > logon info. However, you can/could pull that out of any existing linked
    > table with ease.
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    >
    >
    >
    >
     
    Ivan Grozney, Sep 4, 2009
    #6
  7. "Ivan Grozney" <stormovick (ditch) @hotmail.com> wrote in message
    news:...
    > Albert,
    >
    >
    > In your first post that you "assume you also have some built in re-link
    > code... You can find that code here: "
    >

    The re-link code is not really relevant to this issue. I was however
    referring to Doug's DSN less re-link code here:

    http://www.accessmvp.com/djsteele/DSNLessLinks.html

    (so, I did accident leave above link it out...sorry)


    > I don't have any re-link code and I didn't see it in your response,
    > maybe that is the issue.
    >


    No, the re-link code should not make any difference here.

    'Any SQL statement will work below.
    qdf.SQL = "SELECT * FROM calls"
    qdf.Execute

    While I state above that any sql statement should work...you do have to
    choose a LEGAL and existing table in sql server. So, perhaps your table is
    dbo.Calls for example. I used table Calls, but you have to choose something
    that the user/person has access to.


    > Any further guidance would be much appreciated.


    Check you string/setup of:

    strCon = "ODBC;DRIVER={sql server};" & _
    "SERVER=" & ServerName & ";" & _
    "DATABASE=" & DatabaseName & ";" & _
    "UID=" & UserID & ";" & _
    "PWD=" & USERpw & ";" & _
    "APP=Microsoft Office 2003;" & _
    "WSID=" & WSID

    Perhaps put a debug.print strCon after the above statement. Then create a
    pass-though query, and in the connection setting paste in the strCon results
    and see if the pass-though query works.

    In other words, create the pass-though query. I would also suggest you in
    the debug-window go:

    debug.print currentdb.TableDefs("name of some linked table").Connect

    Take a look at the connection string, and note any differences between it
    and what the strCon produces. Perhaps the string needs some tweaking due to
    you using the sql server native driver for example.


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
     
    Albert D. Kallal, Sep 5, 2009
    #7
  8. Ivan Grozney

    Ivan Grozney Guest

    Albert,

    Thank you. I will check it all again and post back.

    Vanya

    "Albert D. Kallal" wrote:

    > "Ivan Grozney" <stormovick (ditch) @hotmail.com> wrote in message
    > news:...
    > > Albert,
    > >
    > >
    > > In your first post that you "assume you also have some built in re-link
    > > code... You can find that code here: "
    > >

    > The re-link code is not really relevant to this issue. I was however
    > referring to Doug's DSN less re-link code here:
    >
    > http://www.accessmvp.com/djsteele/DSNLessLinks.html
    >
    > (so, I did accident leave above link it out...sorry)
    >
    >
    > > I don't have any re-link code and I didn't see it in your response,
    > > maybe that is the issue.
    > >

    >
    > No, the re-link code should not make any difference here.
    >
    > 'Any SQL statement will work below.
    > qdf.SQL = "SELECT * FROM calls"
    > qdf.Execute
    >
    > While I state above that any sql statement should work...you do have to
    > choose a LEGAL and existing table in sql server. So, perhaps your table is
    > dbo.Calls for example. I used table Calls, but you have to choose something
    > that the user/person has access to.
    >
    >
    > > Any further guidance would be much appreciated.

    >
    > Check you string/setup of:
    >
    > strCon = "ODBC;DRIVER={sql server};" & _
    > "SERVER=" & ServerName & ";" & _
    > "DATABASE=" & DatabaseName & ";" & _
    > "UID=" & UserID & ";" & _
    > "PWD=" & USERpw & ";" & _
    > "APP=Microsoft Office 2003;" & _
    > "WSID=" & WSID
    >
    > Perhaps put a debug.print strCon after the above statement. Then create a
    > pass-though query, and in the connection setting paste in the strCon results
    > and see if the pass-though query works.
    >
    > In other words, create the pass-though query. I would also suggest you in
    > the debug-window go:
    >
    > debug.print currentdb.TableDefs("name of some linked table").Connect
    >
    > Take a look at the connection string, and note any differences between it
    > and what the strCon produces. Perhaps the string needs some tweaking due to
    > you using the sql server native driver for example.
    >
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    >
    >
    >
    >
     
    Ivan Grozney, Sep 7, 2009
    #8
    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. Chris Nebinger

    Multiple SQL Statements - Access Linked To SQL Server

    Chris Nebinger, Jul 12, 2004, in forum: Microsoft Access VBA Modules
    Replies:
    0
    Views:
    295
    Chris Nebinger
    Jul 12, 2004
  2. Guest

    How to test for error in SQL?

    Guest, Dec 22, 2005, in forum: Microsoft Access VBA Modules
    Replies:
    7
    Views:
    217
    Tim Ferguson
    Dec 24, 2005
  3. Guest

    sql loop test

    Guest, Jul 18, 2006, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    218
    Dirk Goldgar
    Jul 18, 2006
  4. Guest

    Newbie: Access SQL and SQL and MS SQL

    Guest, Nov 16, 2006, in forum: Microsoft Access VBA Modules
    Replies:
    4
    Views:
    356
    Granny Spitz via AccessMonster.com
    Nov 18, 2006
  5. ryguy7272

    Access FE with SQL Server BE or .NET FE and SQL Server BE

    ryguy7272, Jun 14, 2009, in forum: Microsoft Access VBA Modules
    Replies:
    14
    Views:
    372
    vanderghast
    Jun 22, 2009
Loading...

Share This Page