How To: Suppress ODBC Connection Dialog?

[Scott A. Jones]

I have an Access front end connecting to an SQL Server back-end. When the
application opens, I run a small test to determine if the SQL Server is
available. If it is not, I have code to trap the 'On Error' event.
Unfortunately, a 'SQL Server Login' dialog appears before my 'On Error' code
has a chance to run. Does anybody know how to prevent the dialog from
appearing? Suppressing warnings (DoCmd.SetWarni ngs FALSE) has no effect.

Thanks,
Scott

[Trevor Best]

Scott A. Jones wrote:

I have an Access front end connecting to an SQL Server back-end. When the
application opens, I run a small test to determine if the SQL Server is
available. If it is not, I have code to trap the 'On Error' event.
Unfortunately, a 'SQL Server Login' dialog appears before my 'On Error' code
has a chance to run. Does anybody know how to prevent the dialog from
appearing? Suppressing warnings (DoCmd.SetWarni ngs FALSE) has no effect.

I have the same problem trying to open a database in DAO, no matter what
options I put into OpenDatabase() it will still prompt if the password
is wrong, etc. So I pop in a little bit of ADO...

Function CanOpenSQLDbLB( pstrServer As String, pstrDb As String, pstrUser
As String, pstrPassword As String, Optional pfReportError As Boolean =
True) As Boolean
' Error Trapped: 04/02/2003 09:50:00 Admin
On Error GoTo CanOpenSQLDbLB_ Err
' try late binding

Dim objConn As Object
Dim strConn As String
Dim strError As String, lngErr As Long
Const cstrSQLErr = "[Microsoft][ODBC SQL Server Driver][SQL Server]"

Set objConn = CreateObject("A DODB.Connection ")

strConn = strConn & "DRIVER=SQL Server"
strConn = strConn & ";SERVER=" & pstrServer
strConn = strConn & ";APP=" & Application.Nam e
strConn = strConn & ";WSID=AWorksta tion"
strConn = strConn & ";DATABASE= " & pstrDb

objConn.Open strConn, pstrUser, pstrPassword

' Q'PLAGH
CanOpenSQLDbLB = True

CanOpenSQLDbLB_ Exit:
On Error Resume Next
objConn.Close
Set objConn = Nothing
Exit Function
CanOpenSQLDbLB_ Err:
lngErr = Err.Number
strError = Err.Description
'MsgBox strError
If InStr(1, strError, cstrSQLErr) Then
strError = "Error reported by server" & vbCr & vbCr &
Replace(strErro r, cstrSQLErr, "")
End If
'Debug.Print strError
Select Case lngErr
Case Else
If pfReportError Then
MsgBox strError, 16, "Error #" & Err & " Attempting to
open server database"
End If
End Select
Resume CanOpenSQLDbLB_ Exit

End Function
--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

[Alec]

Try linking to the SQL table with an ODBC object, and when you do, click
'Save Password'. That way you wont be prompted for the password, and you
can use the table as an access table.

HTH

Alec Christie
alec_e_christie (at)hotmail(dot )com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Scott A. Jones]

On 23-Sep-2004, Trevor Best <nospam@localho st> wrote:

Function CanOpenSQLDbLB( pstrServer As String, pstrDb As String, pstrUser
As String, pstrPassword As String, Optional pfReportError As Boolean =
True) As Boolean
' Error Trapped: 04/02/2003 09:50:00 Admin
On Error GoTo CanOpenSQLDbLB_ Err
' try late binding

Dim objConn As Object
Dim strConn As String
Dim strError As String, lngErr As Long
Const cstrSQLErr = "[Microsoft][ODBC SQL Server Driver][SQL Server]"

Set objConn = CreateObject("A DODB.Connection ")

strConn = strConn & "DRIVER=SQL Server"
strConn = strConn & ";SERVER=" & pstrServer
strConn = strConn & ";APP=" & Application.Nam e
strConn = strConn & ";WSID=AWorksta tion"
strConn = strConn & ";DATABASE= " & pstrDb

objConn.Open strConn, pstrUser, pstrPassword

' Q'PLAGH
CanOpenSQLDbLB = True

CanOpenSQLDbLB_ Exit:
On Error Resume Next
objConn.Close
Set objConn = Nothing
Exit Function
CanOpenSQLDbLB_ Err:
lngErr = Err.Number
strError = Err.Description
'MsgBox strError
If InStr(1, strError, cstrSQLErr) Then
strError = "Error reported by server" & vbCr & vbCr &
Replace(strErro r, cstrSQLErr, "")
End If
'Debug.Print strError
Select Case lngErr
Case Else
If pfReportError Then
MsgBox strError, 16, "Error #" & Err & " Attempting to
open server database"
End If
End Select
Resume CanOpenSQLDbLB_ Exit

End Function

Trevor,

Thanks for the reply. Your code worked great! Problem solved!!

Question: What is the purpose of the pfReportError boolean? I couldn't
figure it out from reading your code.

Thanks,
Scott

[Trevor Best]

Scott A. Jones wrote:

On 23-Sep-2004, Trevor Best <nospam@localho st> wrote:

Function CanOpenSQLDbLB( pstrServer As String, pstrDb As String, pstrUser
As String, pstrPassword As String, Optional pfReportError As Boolean =
True) As Boolean
' Error Trapped: 04/02/2003 09:50:00 Admin
On Error GoTo CanOpenSQLDbLB_ Err
' try late binding

Dim objConn As Object
Dim strConn As String
Dim strError As String, lngErr As Long
Const cstrSQLErr = "[Microsoft][ODBC SQL Server Driver][SQL Server]"

Set objConn = CreateObject("A DODB.Connection ")

strConn = strConn & "DRIVER=SQL Server"
strConn = strConn & ";SERVER=" & pstrServer
strConn = strConn & ";APP=" & Application.Nam e
strConn = strConn & ";WSID=AWorksta tion"
strConn = strConn & ";DATABASE= " & pstrDb

objConn.Open strConn, pstrUser, pstrPassword

' Q'PLAGH
CanOpenSQLDbLB = True

CanOpenSQLDbL B_Exit:
On Error Resume Next
objConn.Close
Set objConn = Nothing
Exit Function
CanOpenSQLDbL B_Err:
lngErr = Err.Number
strError = Err.Description
'MsgBox strError
If InStr(1, strError, cstrSQLErr) Then
strError = "Error reported by server" & vbCr & vbCr &
Replace(strEr ror, cstrSQLErr, "")
End If
'Debug.Print strError
Select Case lngErr
Case Else
If pfReportError Then
MsgBox strError, 16, "Error #" & Err & " Attempting to
open server database"
End If
End Select
Resume CanOpenSQLDbLB_ Exit

End Function

Trevor,

Thanks for the reply. Your code worked great! Problem solved!!

Question: What is the purpose of the pfReportError boolean? I couldn't
figure it out from reading your code.

Thanks,
Scott

If true, a MsgBox will appear with the error message, if false then the
function quietly exits and returns 0 (IOW cannot connect).
--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

[Ricardo Hernández Sáez]

Do it in reverse order: Get user name and password and then establish
connection.
Insert the ODBC connection string (with user name and password) in a SQL
sentence. This wil avoid the popup when you use the linked tables:

strOdbcCon="ODB C" _
& ";DRIVER=SQ L Server" -
& ";SERVER=YourSe rver" _
& ";DATABASE=Your Database" _
& ";uid=" & strUserName _
& ";pwd=" & strPassword

strSQL="SELECT * FROM [" & strOdbcCon & "].OneTable WHERE FALSE"
set db=currentdb
set rs=dbs.openreco rdset(strSQL,db OpenNapshot)

It works.

"Scott A. Jones" <sa*****@gmx.ne t> escribió en el mensaje
news:ci******** *@enews1.newsgu y.com...

I have an Access front end connecting to an SQL Server back-end. When the
application opens, I run a small test to determine if the SQL Server is
available. If it is not, I have code to trap the 'On Error' event.
Unfortunately, a 'SQL Server Login' dialog appears before my 'On Error' code has a chance to run. Does anybody know how to prevent the dialog from
appearing? Suppressing warnings (DoCmd.SetWarni ngs FALSE) has no effect.

Thanks,
Scott