Hello All,
The short questions are
1 Do you know how to make DSN connection close in Access to Oracle 10g
Express Edition?
&/or
2 Do you know how to make a DSN-less pass-through query work from
Access 2k2 to Oracle 10g Express Edition?
I'm experimenting using Access 2k2 as front-end to Oracle 10g Express
Edition. I've tried a DSN connection, and it works. The problem is that
once Access creates the connection using a password and UserID from a
form, it does NOT close the connection. The only way to close the
connection is to close Access. This is not good if different users use
the same workstation and have different rights.
Well I've tried a DSN-less connection. I got it to work to convert DSN
linked tables to DSN-less linked tables per Doug Steele's code at
http://www.accessmvp.com/djsteele/DSNLessLinks.html. However, when I
replaced the pass-through query ODBC Connect String with the following:
,----- [ pass-through query ODBC Connect String ]
| "ODBC;Driver={Oracle in XE};Dbq=XE;UID=MyUID;PWD=MyPswd;"
`-----
I get an error. It reads as follows:
,----- [ Error on pass-through query run from Access or code ]
| Reserved error(-7778); there is no message for this error
`-----
The error occurs on the following line:
,----- [ VBA error line ]
| DoCmd.OpenQuery "qrySumInvcTest"
`-----
,----- [ Pass-Through Query "qrySumInvcTest" ]
| select sum(invcamt) as TotInvc from sc.tblRevenuePrYr;
`-----
I've tried MS's driver without luck. So I'm running out of straws to
grasp. So either I find the solution to closing a DSN connectin with
Access, or I find a way to make DSN-less pass-through query work with
Access. So far no luck. The only difference between the 2 types of
connection is the connection string used. The code is below for both
scenarios.
Thanks for any ideals!!!
======DSN============>Begin Code>===================================>
Sub TestQryDef()
Dim wsCur As DAO.Workspace
Dim dbCur As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String
On Error GoTo CheckError
Set wsCur = DBEngine.Workspaces(0)
Set dbCur = wsCur.Databases(0)
Call SetConStr
If strCnn = "" Then
Exit Sub
End If
Set qd = dbCur.QueryDefs("qrySumInvcTest")
qd.Connect = "ODBC;" & strCnn
qd.ReturnsRecords = True
DoCmd.OpenQuery "qrySumInvcTest"
qd.Connect = "ODBC;DSN=OracleXE;DATABASE=4GetU;;UID=NoGo;" & _
"PWD=4GetIt;"
qd.Close
dbCur.Close
wsCur.Close
Set qd = Nothing
Set dbCur = Nothing
Set wsCur = Nothing
Exit Sub
CheckError:
If Err.Number = 3151 Then
MsgBox "You must enter a valid UserID and Password!!!", _
vbOKOnly, "UserID and Password"
Set qd = Nothing
Set dbCur = Nothing
Set wsCur = Nothing
Exit Sub
End If
MsgBox "Error Number: " & Err.Number & " Error Desc: " & _
Err.Description, , "Error"
Set qd = Nothing
Set dbCur = Nothing
Set wsCur = Nothing
End Sub
======DSN============<End Code><=====================================<
======PUBLIC VAR=====>Begin Code>===================================>
Public strCnn As String, strOConn As String, strMSOCnn As String
=====================<End Code><=====================================<
======CONNECT STR====>Begin Code>===================================>
Sub SetConStr()
Dim strUID As String, strPswd As String
Forms("frmMain").Refresh
strCnn = ""
strOConn = ""
If IsNull(Forms("frmMain").Controls("txtUserID").Valu e) Then
MsgBox "Please enter your User ID!", , "Enter User ID"
Exit Sub
End If
If IsNull(Forms("frmMain").Controls("txtPswd").Value) Then
MsgBox "Please enter your Password!", , "Enter Password"
Exit Sub
End If
strUID = Forms("frmMain").Controls("txtUserID").Value
strPswd = Forms("frmMain").Controls("txtPswd").Value
strCnn = "DSN=OracleXE;DATABASE=XE;;" & _
"UID=" & strUID & _
";PWD=" & strPswd & ";"
' strOConn = "Driver={Oracle in XE};" & _
' "Dbq=XE;" & _
' "UID=" & strUID & _
' ";PWD=" & strPswd & ";"
strOConn = "ODBC;Driver={Oracle in XE};" & _
"Dbq=XE;" & _
"UID=" & strUID & _
";PWD=" & strPswd & ";"
' strOConn = "ODBC;Driver={Oracle in XE};" & _
' "DATABASE=XE;" & _
' "UID=" & strUID & _
' ";PWD=" & strPswd & ";"
strMSOCnn = "Driver={Microsoft ODBC for Oracle};" & _
"Server=DEDICATED;" & _
"UID=" & strUID & _
";PWD=" & strPswd & ";"
Debug.Print "strCnn: "; strCnn
Debug.Print "strOConn: "; strOConn
Debug.Print "strMSOCnn: "; strMSOCnn
End Sub
======CONNECT STR====<End Code><=====================================<
======DSN-LESS=======>Begin Code>===================================>
Sub TestQD_DSNless()
Dim wsCur As DAO.Workspace
Dim dbCur As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String
'On Error GoTo CheckError
Set wsCur = DBEngine.Workspaces(0)
Set dbCur = wsCur.Databases(0)
Call SetConStr
If strOConn = "" Then
Exit Sub
End If
Set qd = dbCur.QueryDefs("qrySumInvcTest")
qd.Connect = strOConn
qd.ReturnsRecords = True
DoCmd.OpenQuery "qrySumInvcTest"
'qd.Connect = "ODBC;DSN=OracleXE;DATABASE=4GetU;;UID=NoGo;" & _
"PWD=4GetIt;"
qd.Close
dbCur.Close
wsCur.Close
Set qd = Nothing
Set dbCur = Nothing
Set wsCur = Nothing
Exit Sub
CheckError:
If Err.Number = 3151 Then
MsgBox "You must enter a valid UserID and Password!!!", _
vbOKOnly, "UserID and Password"
Set qd = Nothing
Set dbCur = Nothing
Set wsCur = Nothing
Exit Sub
End If
MsgBox "Error Number: " & Err.Number & " Error Desc: " & _
Err.Description, , "Error"
Set qd = Nothing
Set dbCur = Nothing
Set wsCur = Nothing
End Sub
=======DSN-LESS======<End Code><=====================================<
Thanks again!!!
--
Regards,
Greg
PS. Sorry for double post, but since subject expanded (i.e. DSN-less) I
thought a cross post with more relevant subject appropriate.
--
Regards,
Greg Strong
