Close ODBC connection via code?

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

re: Close ODBC connection via code?

On Sun, 09 Jul 2006 01:17:16 -0500, Greg Strong
<news.REMOVE@geedubeeu.com.invalidwrote:
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"
`-----

I've tried MS's driver without luck. So I'm running out of straws to
grasp. Any ideals would be appreciated. Thanks!

The code now reads as follows:

=====================>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
=====================<End Code<=====================================<

=====================>Begin Code>===================================>
Public strCnn As String, strOConn As String, strMSOCnn As String
=====================<End Code<=====================================<

=====================>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
=====================<End Code<=====================================<


--
Best Regards,
Greg

--
Regards,

Greg Strong

re: Close ODBC connection via code?

Greg Strong wrote:
I was dying to try DSNless when I went to A2003 from A97 two years ago.
I was bitterly disappointed in that the ADO set up would not allow me
to use DSNless connections to write Access reports, so I gave up on it
and continue to do all my Oracle stuff in DAO.

I could be wrong on the following and hope someone will jump down my
throat ASAP if I am! 8)

As far as I can tell, your PTQ won't work unless it has a DSN
connection. The ADO DSNless stuff allows you to create ADO recordsets
which are using Oracle SQL in code and have these recordsets assigned to
a form and some other objects as its recordsource (but, as I mention
above, you can't do this for reports!).

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

re: Close ODBC connection via code?

Greg Strong wrote:
I can't help with the code, but a possible suggestion might be to force
your users to enter the Oracle username password when the app turns on.
The connection string could then be checked and that would flag
whatever procedures you have that you're not connected.

As far as users having the Oracle username password, what I've done with
my major apps is create an Oracle user and grant select only access on
whatever tables I want my users to see - some of these might have insert
and other privilges besides select. I then give this user a simple
password that all users know.

Other than for perhaps people working on the app remotely, away from the
Oracle server, I can't see what reason you might have to close an ODBC
connection (that's not to say there's not a valid reason, I just can't
think of a situation in my experience where that might be necessary).
If the remote feature I describe at the beginning of this para is what
you're trying to achieve, then the above might be workable.

Another possibility in this train of thought would be to trap the Access
ODBC errors (4171 or something like that - I'm not near my Access apps
now so can't recall) that occur when the Oracle DB is not available and
have that flag the not connected state throughout the app session.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me