Share via

How to suppress SQL Server Login prompt if append/connection fails?

Anonymous
2016-10-03T23:23:41+00:00

I have been figuring out how to link my SQL Server back end tables to my Access front end using VBA (not via DSN).  I think I worked all of that out in the following post:

https://answers.microsoft.com/en-us/msoffice/forum/msoffice\_access-mso\_winother/how-to-connect-access-front-end-to-sql-server/fe7ff42a-f21d-4ffa-bf1a-bb961064f575

I have it up and running, and it works great.  I'm just thinking through how to handle some errors, and have worked through a lot of that.  But one that I haven't figured out how to handle is if the SQL Server connection fails.  When I test this (by putting in an incorrect password), 

And then, it dumps them out into a dialog box where they can try new passwords!

I don't like this at all!  How can I suppress these dialog boxes?!  I already have error handling defined and SetWarnings = False.  My full code (simplified/censored for posting) is as follows:

Public Function CreateSQLLinkedTable(strSourceTableName As String, strNewTableName As String) As Boolean

On Error GoTo OnError

Dim tdf As TableDef

Dim strConnect As String, strMsg As String

Dim fRetval As Boolean

Dim myDB As Database

    ' turn system warnings off

    DoCmd.SetWarnings False

    ' set database vars

    Set myDB = CurrentDb

    Set tdf = myDB.CreateTableDef(strNewTableName)

    ' define connect string and source table

    With tdf

        .Connect = "ODBC;Driver={SQL Server};server=MyServer;database=MyDatabase;uid=MyUserID;Pwd=MyPassword;"

        .SourceTableName = strSourceTableName

    End With

    ' execute appending the table

    myDB.TableDefs.Append tdf

    ' set bool var to True

    fRetval = True

    ' turn system warnings back on

    DoCmd.SetWarnings True

ExitProgram:

    ' this block of code will run if there are no errors

    ' set the return value = to fRetval (which was set to True above)

    CreateSQLLinkedTable = fRetval

    Exit Function

OnError:

    MsgBox "There was an error connecting to the SQL Server data source. Error = " & Err & ", Description: " & Err.Description

    'exit

    Call CloseFormsAndQuit

End Function

Thanks!

Emily

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2016-10-05T00:29:57+00:00

Keep in mind that the code not supposed to "show" any tripped errors - but it is supposed to run the on-error part if the uid/pass is NOT valid.

Sorry, I did a bit of “air” code in that sql logon, 

You have to change the sql to say something like this:

    qdf.SQL = "select 'abc' "

My sorry for messing that up. (so change the code to above line).

So modify the code to above. You can use “any” valid sql – and I used one that does NOT reference or select any table. So this routine can be used for all databases and you don’t have to use some “known” table name.

So change the above, and then you can go:

   Dim strCon     As String

   strCon = "ODBC;DRIVER={sql server};" & _

            "SERVER=ALBERTKALLAL-PC\SQLEXPRESS;" & _

            "DATABASE=test3;" & _

            "UID=SA;" & _

            "PWD=MyPassWord;" & _

            "APP=Microsoft Office;" & _

            "WSID=AlbertKallal-pc;"

Debug.Print TestLogin(strCon)

NOTE:

If you do at ANY TIME provide a VALID logon to the above routine, then AFTER that point you can send any BAD or non-valid logon, and it will CONTINUE to return true and continue to WORK!!!! This is because of that “caching” system that access uses and outlined by Ben. However even if not using that caching trick, you still find that Access will cache passwords.

What above means is that once you logged on, then that’s quite much it – you can’t logoff. You are 100% done! You will have to exit the database for that given UID/Password to stop working.

And passing “different” UID/Password will not fail - even if wrong!. This is EXTREMELY important during development, since if you are developing some code, logon with a valid UID/pass, and then run some re-link code to re-link the tables with a different uid/pass? You can actually wind up re-linking the table(s) with a BAD uid/pass word that does not work, and yet you find the application runs fine – fine that is until you exit and re-enter.

For the above reason, if you are playing and testing code to link to a database and then must re-link – I STRONG recommend you exit and then re-enter the database to flush out any cached uid/password you been using or testing with. This occurs FAR MORE common then you realize, since for example you might be testing code while linked to the “test” or “developer” database, and right before deployment you now want to link to the production database. You in this case REALLY REALLY REALLY want to exit the database to flush out that cached uid/password before you re-link to production (else as noted you have linked tables that “seem” to work, but were actually linked and working using an incorrect + older uid/password. You also find this behavior if you trying to re-link existing tables that included the uid/pass, and the new re-linking is to remove the uid/pass. You find that you can freely click on and open those tables without a uid/logon, yet the linked tables dont' have a uid/pass!!!!

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

******@msn.com

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2016-10-05T10:32:52+00:00

What is your connection string? You can't really use the same one you're using for relinking to make an ADO connection. I use this resource:

https://www.connectionstrings.com/access/

FWIW, I've used this method often to ensure that my connections are live. It will work, if your connection string is correct.

Sorry about the ConnectionString snafu - mine was air code as well.

Was this answer helpful?

0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-10-05T15:14:28+00:00

    Hi Albert,

    I think the caching phenomenon was my issue.  I tried again this morning after restarting my database, and now it trips on incorrect connection strings.  

    Also, as you suggested, I was indeed able (once I had a good connection set up as part of this code) to use this cached connection to link up my tables without specifying any passwords there.  So thanks for that insight that helped increase the security of my database!

    For posterity, I pasted the code I ended up with finally (with redactions) below.  This is in a sub that is called right after the database opens, before any of the SQL tables are linked up (obviously).

    Thanks!

    Emily

    Public Sub CheckSQLConnection()

        '' Checks SQL connection by attempting to create an DAO connection, and handling any errors that occur."

     On Error GoTo TestError

       Dim dbs As DAO.Database

       Dim qdf As DAO.QueryDef

       Dim strErr As String

       Set dbs = CurrentDb()

       Set qdf = dbs.CreateQueryDef("")

        ' Define connection string (customize to your database)

        qdf.Connect = "ODBC;Driver={SQL Server};server=MyServer;database=MyDatabase;uid=MyUserID;Pwd=MyPassword;"

    qdf.ReturnsRecords = False

        'Any SQL valid statement will work below.  Customize this to your database (obviously)

        qdf.SQL = "SELECT FieldName FROM TableName"

        qdf.Execute

        Exit Sub

    TestError:

        ' Build an error message that describes/identifies the error and display it.  Then close the database.

        strErr = "MyApplicationName was not able to connect to the SQL Server database.  Please try again shortly.  If this problem " & _

            "persists, please take a screenshot (Alt-PrtScn) of the errors " & _

            "listed below, and paste it into an email to [enter names of admins]."

        strErr = strErr & vbCrLf & vbCrLf & "Error: " & err.Number & vbCrLf & "Description: " & err.Description

        ' Just in case an error message ever identifies any of the sensitive information related to the location or

        ' login/password for the database, replace that info with a mask.

        strErr = Replace(strErr, "MyServer", "<Server>")

        strErr = Replace(strErr, "MyUserID", "<Uid>")

        strErr = Replace(strErr, "MyPassword", "<Pwd>")

        MsgBox strErr, vbOKOnly, "SQL Connection Error"

        'exit database by calling sub for safe close

        Call CloseFormsAndQuit

        Exit Sub

    End Sub

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2016-10-04T18:17:30+00:00

    You can't really suppress the failure, but you FIRST can check if the connection is ok.

    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 1 FROM FakeZoo"

    qdf.Execute

    TestLogin = True

    Exit Function

    TestError:

    TestLogin = False

    Exit Function

    End Function

    [/CODE]

    To call the above, I would go:

    strCon = "ODBC;DRIVER={sql server};" & _

    "SERVER=" & ServerName & ";" & _

    "DATABASE=" & DatabaseName & ";" & _

    "UID=" & UserID & ";" & _

    "PWD=" & USERpw & ";" & _

          "APP=Microsoft Office;" & _

    "WSID=" & WSID

    If TestLogin(strCon) = True Then

    ' logon is ok!

    So, the above allows you to “test” your logon. Its nice due to how well it “times out” and does not toss up any error messages.

    I also suggest that you do NOT include the user name and password in table links. How you can acheive this is outlined here:

    Power Tip: Improve the security of database connections

    http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2016-10-04T12:22:40+00:00

    If you must do this, I'd try to open a connection to the SQL Server BEFORE running the relink routines. You can try to create an ADO connection, and if that fails alert the user. You'd have to set a reference to the Microsoft ActiveX Data Objects library before doing this:

    On Error GoTo ErrorHandler

    Dim con As New ADODB.Connection

    con.Connection = "Your connection string here"

    con.Open

    ErrorHandler:

    '/ alert the user here

    The ADODB.Connection object also provides you with an Error collection if this occurs, which can give you more information about why the connection failed. To access that collection you'd do this:

    Dim err As ADODB.Error

    For each err in con.Errors

      '/ get info here

    Next err

    ADODB.Connection info: https://msdn.microsoft.com/en-us/library/ms681519(v=vs.85).aspx

    Error example: https://support.microsoft.com/en-us/kb/167957

    Was this answer helpful?

    0 comments