Hello All,
Is there any way to close an ODBC connection via DSN without completely
closing the Access front-end?
I'm doing some testing with using Access as a front-end to Oracle 10g
Express edition. When the user ID and password is entered on a form and
after a successful connection it seems Access doesn't change the
connection. Even if I enter an invalid user ID and/or password the
connection continues to work.
I've debug.print the connection string. It is showing the incorrect
password. I've even changed the pass-through queries connection string
via code with bogus UID and password. When I view the query properties,
it shows the bogus info. Afterward if I run the query from code it runs
no problem. If I double click the query in Access I get the ODBC
connection fail error. So for what ever reason, Access holds the
connection open when running from code.
I've done some checking and haven't found a solution. I found this more
recent link:
http://groups.google.com/group/comp....6107a1b1c06900
This link seems to indicate this is an Access problem. I don't know.
I've included the code below. Any ideals? Thanks!
=====================>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
=====================<End Code<=====================================<
=====================>Begin Code>===================================>
Sub SetConStr()
Dim strUID As String, strPswd As String
Forms("frmMain").Refresh
strCnn = ""
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 & ";"
Debug.Print strCnn
End Sub
=====================<End Code<=====================================<
--
Regards,
Greg Strong
