C
Christine
I have a function OpenADOConnection that I use to Open an ado
connection. I will include the code at the end of this post for
reference, but will explain my issues in English first.
This function is usually called to open an exclusive connection so
that some ado DDL can be performed to change a table structure in the
mdb being connected to. My error handling in the function traps the
cnn.Open error and handles different situations such as the need for
UserID, password, Workgroup name etc. For the most part, it is working
fairly well.
Where my problems arise is the fact that error -2147467259 can have
different causes, which I would like to isolate from each other in a
case statement and handle according to whatever the specific problem
is. Two such causes are:
1) You attempted to open a database that is already opened exclusively
by user 'xx' on machine 'yyyyyy'. Try again when the database is
available.
2)You do not have the necessary permissions to use the
'C:\xx\yy\dbname.mdb' object. Have your system administrator or the
person who created this object establish the appropriate permissions
for you.
Probably there are many more errors that would trigger this same error
number.
The Err.descriptions in 1 and 2 are informative, but do not lend
themselves directly to use in a Case statement. In reading through
posts, I got the idea that I might be able to get more specific
information from the cnn.Errors collection. But that just gives me the
same Error Number -2147467259.
So my question is, is there any way to get a more specific error
number so my error handler can be more specific? I guess I could
search for key words within the Err.Description and come to a decision
from that. For example, if the Err.Description contains 'exclusively'
then it is my first situation, or if it contains 'necessary
permissions' it is my second, etc. I assume I can deduce the correct
order for this sequential Error interpretation. However, this seems
like a very roundabout way to go about it and if someone else has
figured out a more elegent, clean, direct solution, I really
appreciate knowing about it.
Much appreciation to any one who will engage with me on this.
Thanks,Christine
---------------- Code ---------------------------------------
Function OpenADOConnection(sDBPath As String, _
Optional eConnMode As ConnectModeEnum = adModeShareExclusive, _
Optional fSilent As Boolean) As ADODB.Connection
'' Returns an open Connection to an external Database.
'' If workgroup is needed, and a workgroup is currently in use it is
used.
'' If necesary, the User is prompted for Workgroup,UserName and
Password.
'' The User's password is held for future connections.
On Error GoTo ErrOpen
Call mProcStack.EnterProc(mcModName, "OpenADOConnection")
Dim cnn As ADODB.Connection
Dim sUser As String, sWrkGroup As String, sOpen As String
Dim fExit As Boolean
Dim iTry As Integer
sWrkGroup = SysCmd(acSysCmdGetWorkgroupFile)
sUser = CurrentUser
sOpen = "Data Source=" & sDBPath & ";"
Set cnn = New ADODB.Connection
cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
cnn.Mode = eConnMode
OpenIt:
On Error GoTo ErrOpen
cnn.Open sOpen
Set OpenADOConnection = cnn
Ex:
Debug.Print "OpenADOConnection Open String:" & sOpen
Call mProcStack.ExitProc(mcModName, "OpenADOConnection")
Exit Function
ErrOpen:
Debug.Print ErrMsg(err.Number)
Select Case err.Number
Case -2147217843 'Needs Workgroup
If sWrkGroup = "" Then
sWrkGroup = InputBox( _
"Please enter full pathname of Workgroup for " &
sDBPath)
If sWrkGroup = "" Then
fExit = True
End If
End If
If Not fExit Then
cnn.Properties("Jet OLEDB:System database") = sWrkGroup
End If
Case -2147467259 'Needs UserID & password or cannot open
exclusive
DbgAdoErrors cnn
If sUser = "" Then
sUser = InputBox("Please enter your User Name for access
to " & sDBPath)
End If
If sUser <> "" Then
If gsUserPwd = "" Then
gsUserPwd = InputBox(sUser & ", Please enter your
Password for access to " & sDBPath)
End If
If gsUserPwd <> "" Then
sOpen = sOpen & ";User ID=" & sUser & ";Password=" &
gsUserPwd & ";"
Else
fExit = True
End If
Else
fExit = True
End If
Case Else
Debug.Print "OpenADOConnection: Unknown " &
ErrMsg(err.Number)
Err_Handler err.Number
End Select
iTry = iTry + 1
If iTry = 5 Then
If Not fSilent Then
MsgBx "Sorry, unable to establish connection to
" & _
sDBPath & ".@Numerous errors", vbExclamation,
"OpenADOConnection Error"
End If
fExit = True
End If
If Not fExit Then
On Error GoTo ErrOpen
Resume
Else
GoTo Ex
End If
End Function
Sub DbgAdoErrors(cnn As ADODB.Connection)
Dim iCount As Integer, i As Integer
iCount = cnn.Errors.count
Debug.Print iCount & " Ado ConnectionErrors:"
For i = 0 To iCount - 1
Debug.Print cnn.Errors(i).Number & ":" &
cnn.Errors(i).Description
Next
End Sub
Function ErrMsg(lErrNum As Long) As String
ErrMsg = "Error: " & lErrNum & " " & Error(lErrNum)
End Function
connection. I will include the code at the end of this post for
reference, but will explain my issues in English first.
This function is usually called to open an exclusive connection so
that some ado DDL can be performed to change a table structure in the
mdb being connected to. My error handling in the function traps the
cnn.Open error and handles different situations such as the need for
UserID, password, Workgroup name etc. For the most part, it is working
fairly well.
Where my problems arise is the fact that error -2147467259 can have
different causes, which I would like to isolate from each other in a
case statement and handle according to whatever the specific problem
is. Two such causes are:
1) You attempted to open a database that is already opened exclusively
by user 'xx' on machine 'yyyyyy'. Try again when the database is
available.
2)You do not have the necessary permissions to use the
'C:\xx\yy\dbname.mdb' object. Have your system administrator or the
person who created this object establish the appropriate permissions
for you.
Probably there are many more errors that would trigger this same error
number.
The Err.descriptions in 1 and 2 are informative, but do not lend
themselves directly to use in a Case statement. In reading through
posts, I got the idea that I might be able to get more specific
information from the cnn.Errors collection. But that just gives me the
same Error Number -2147467259.
So my question is, is there any way to get a more specific error
number so my error handler can be more specific? I guess I could
search for key words within the Err.Description and come to a decision
from that. For example, if the Err.Description contains 'exclusively'
then it is my first situation, or if it contains 'necessary
permissions' it is my second, etc. I assume I can deduce the correct
order for this sequential Error interpretation. However, this seems
like a very roundabout way to go about it and if someone else has
figured out a more elegent, clean, direct solution, I really
appreciate knowing about it.
Much appreciation to any one who will engage with me on this.
Thanks,Christine
---------------- Code ---------------------------------------
Function OpenADOConnection(sDBPath As String, _
Optional eConnMode As ConnectModeEnum = adModeShareExclusive, _
Optional fSilent As Boolean) As ADODB.Connection
'' Returns an open Connection to an external Database.
'' If workgroup is needed, and a workgroup is currently in use it is
used.
'' If necesary, the User is prompted for Workgroup,UserName and
Password.
'' The User's password is held for future connections.
On Error GoTo ErrOpen
Call mProcStack.EnterProc(mcModName, "OpenADOConnection")
Dim cnn As ADODB.Connection
Dim sUser As String, sWrkGroup As String, sOpen As String
Dim fExit As Boolean
Dim iTry As Integer
sWrkGroup = SysCmd(acSysCmdGetWorkgroupFile)
sUser = CurrentUser
sOpen = "Data Source=" & sDBPath & ";"
Set cnn = New ADODB.Connection
cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
cnn.Mode = eConnMode
OpenIt:
On Error GoTo ErrOpen
cnn.Open sOpen
Set OpenADOConnection = cnn
Ex:
Debug.Print "OpenADOConnection Open String:" & sOpen
Call mProcStack.ExitProc(mcModName, "OpenADOConnection")
Exit Function
ErrOpen:
Debug.Print ErrMsg(err.Number)
Select Case err.Number
Case -2147217843 'Needs Workgroup
If sWrkGroup = "" Then
sWrkGroup = InputBox( _
"Please enter full pathname of Workgroup for " &
sDBPath)
If sWrkGroup = "" Then
fExit = True
End If
End If
If Not fExit Then
cnn.Properties("Jet OLEDB:System database") = sWrkGroup
End If
Case -2147467259 'Needs UserID & password or cannot open
exclusive
DbgAdoErrors cnn
If sUser = "" Then
sUser = InputBox("Please enter your User Name for access
to " & sDBPath)
End If
If sUser <> "" Then
If gsUserPwd = "" Then
gsUserPwd = InputBox(sUser & ", Please enter your
Password for access to " & sDBPath)
End If
If gsUserPwd <> "" Then
sOpen = sOpen & ";User ID=" & sUser & ";Password=" &
gsUserPwd & ";"
Else
fExit = True
End If
Else
fExit = True
End If
Case Else
Debug.Print "OpenADOConnection: Unknown " &
ErrMsg(err.Number)
Err_Handler err.Number
End Select
iTry = iTry + 1
If iTry = 5 Then
If Not fSilent Then
MsgBx "Sorry, unable to establish connection to
" & _sDBPath & ".@Numerous errors", vbExclamation,
"OpenADOConnection Error"
End If
fExit = True
End If
If Not fExit Then
On Error GoTo ErrOpen
Resume
Else
GoTo Ex
End If
End Function
Sub DbgAdoErrors(cnn As ADODB.Connection)
Dim iCount As Integer, i As Integer
iCount = cnn.Errors.count
Debug.Print iCount & " Ado ConnectionErrors:"
For i = 0 To iCount - 1
Debug.Print cnn.Errors(i).Number & ":" &
cnn.Errors(i).Description
Next
End Sub
Function ErrMsg(lErrNum As Long) As String
ErrMsg = "Error: " & lErrNum & " " & Error(lErrNum)
End Function