TableDefs.Append to an SQL server - wrong password

I have an MS Access front end to an SQL Server back end.  Everything is working fine except for the way a bad password is handled.

Where a bad password is part of the connection string I get an error message box and then a dialog box that would have me input the correct credentials for the SQL server.  I would like to trap this so I can have my own custom error messge.

Any ideas?
Dim db As DAO.Database
Dim tdf As DAO.TableDef
. 
.
.
db.TableDefs.Append tdf '<---- error here with bad password

Open in new window

AviationAceSole-ProprietorAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok.
you might try to connect first (without the table=xxx) part:
dim cnn as adodb.connection
set cnn = new adodb.connection
on error goto ErrHandleConnectionProblem
cnn.open sConn 
cnn.close
set cnn = nothing
 
...
 
 
ErrHandleConnectionProblem:
  ....

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:

Sub ...
Dim db As DAO.Database
Dim tdf As DAO.TableDef
. 
.
.
on error goto ErrHandleAppendFailure
db.TableDefs.Append tdf '<---- error here with bad password
... 
Exit Sub 
ErrHandleAppendFailure:
. ..  handle the  error here... 
Exit Sub
End Sub

Open in new window

0
 
AviationAceSole-ProprietorAuthor Commented:
I tried your suggestion.  I didn't work.  I still get the SQL error message and the dialog box asking for credientials.  I think (I'm not sure) that the error message is coming from the ODBC driver.  What do you think?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I think (I'm not sure) that the error message is coming from the ODBC driver.  What do you think?
possibly. you are using DSN entry for the linked table?
0
 
AviationAceSole-ProprietorAuthor Commented:
I'm not using a DSN file.  I make a connection string.  When I the variable strPWD has a bad password for the SQL server: I get the error message and dialog box asking for SQL credientials.
Dim sConn As String
sConn = "ODBC;Description=MySQL Tables;DRIVER=SQL Server;" & _
        "SERVER=" & strServerName & ";APP=2007 Microsoft Office system;" & _
        "DATABASE=MyDB;" & _
        "UID=" & strUserName & ";PWD=" & strPWD & _
        ";Network=MyNet;TABLE=" & strTableName
Debug.Print sConn
tdf.Connect = sConn

Open in new window

0
 
AviationAceSole-ProprietorAuthor Commented:
You rock!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.