Solved

TableDefs.Append to an SQL server - wrong password

Posted on 2009-07-07
6
374 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:AviationAce
  • 3
  • 3
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24797803
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
 

Author Comment

by:AviationAce
ID: 24797919
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24797965
>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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:AviationAce
ID: 24798011
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24798458
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
 

Author Closing Comment

by:AviationAce
ID: 31600786
You rock!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now