Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Close ODBC Connection / DSN vs DSN-less Connection / Access 2k2 Front-End

Discussion in 'Information Technology' started by Greg Strong, Jul 28, 2006.

  1. Greg Strong

    Greg Strong
    Expand Collapse
    Guest

    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").Value) 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
     
  2. Loading...

    Similar Threads Forum Date
    India Who says we have skeletons only in our closets? Breaking News Aug 22, 2014
    India After Badrinath, Kedarnath now Sikh shrine Hemkunt Sahib closed temporarily Breaking News Jun 28, 2013
    Grammer / Vyakarn Fateh multimedia forced to close for lack of funds Language, Arts & Culture Nov 21, 2012
    General The Closer Videos Jul 26, 2012
    Close Brush with the Law, a Personal Story Business, Lifestyle & Leisure Sep 24, 2011

  3. MGFoster

    MGFoster
    Expand Collapse
    Guest

    Re: Close ODBC Connection / DSN vs DSN-less Connection / Access 2k2Front-End

    -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    You need to close the QueryDef before running the OpenQuery command and
    you don't need the QueryDefs' ReturnsRecords property set.

    Set qd = dbCur.QueryDefs("qrySumInvcTest")
    qd.Connect = strOConn
    qd.Close
    DoCmd.OpenQuery "qrySumInvcTest"

    --
    MGFoster:::mgf00 <at> earthlink <decimal-point> net
    Oakland, CA (USA)
    ** Respond only to this newsgroup. I DO NOT respond to emails **

    -----BEGIN PGP SIGNATURE-----
    Version: PGP for Personal Privacy 5.0
    Charset: noconv

    iQA/AwUBRLF344echKqOuFEgEQK6jgCfdqq+an5DA+1Q1kLJ9N30xdVoEOgAn279
    4kKfJEG75td11TtjwyO+lHkZ
    =uOPb
    -----END PGP SIGNATURE-----

    Greg Strong wrote:
    > 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?
    >


    < SNIP >

    > 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"
    > `-----


    < SNIP >

    > 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


    < SNIP >
     
  4. Greg Strong

    Greg Strong
    Expand Collapse
    Guest

    On Sun, 09 Jul 2006 21:40:55 GMT, MGFoster <me@privacy.com> wrote:

    >You need to close the QueryDef before running the OpenQuery command and
    >you don't need the QueryDefs' ReturnsRecords property set.
    >
    >Set qd = dbCur.QueryDefs("qrySumInvcTest")
    >qd.Connect = strOConn
    >qd.Close
    >DoCmd.OpenQuery "qrySumInvcTest"


    I now receive an error that reads as follows:

    ,----- [ Error ]
    | Run-time error '3000':
    | Reserved error (-7778); there is no message for this error.
    `-----

    The error still occurs on the following line:

    ,----- [ VBA error line ]
    | DoCmd.OpenQuery "qrySumInvcTest"
    `-----


    Thanks for the help!

    --
    Regards,

    Greg Strong
     
  5. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    What value do you have for strOConn?

    In your previous post, you listed a connection string "ODBC;Driver={Oracle
    in XE};Dbq=XE;UID=MyUID;PWD=MyPswd;"

    Just curious as to where that came from. Carl Prothman doesn't list that
    particular driver anywhere at
    http://www.carlprothman.net/Technology/ConnectionStrings/ODBCDSNLess/tabid/90/Default.aspx

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)


    "Greg Strong" <news.REMOVE@geedubeeu.com.invalid> wrote in message
    news:nf23b25632l21jf51rcej2tb09f1tkoroa@4ax.com...
    > On Sun, 09 Jul 2006 21:40:55 GMT, MGFoster <me@privacy.com> wrote:
    >
    >>You need to close the QueryDef before running the OpenQuery command and
    >>you don't need the QueryDefs' ReturnsRecords property set.
    >>
    >>Set qd = dbCur.QueryDefs("qrySumInvcTest")
    >>qd.Connect = strOConn
    >>qd.Close
    >>DoCmd.OpenQuery "qrySumInvcTest"

    >
    > I now receive an error that reads as follows:
    >
    > ,----- [ Error ]
    > | Run-time error '3000':
    > | Reserved error (-7778); there is no message for this error.
    > `-----
    >
    > The error still occurs on the following line:
    >
    > ,----- [ VBA error line ]
    > | DoCmd.OpenQuery "qrySumInvcTest"
    > `-----
    >
    >
    > Thanks for the help!
    >
    > --
    > Regards,
    >
    > Greg Strong
     
  6. Greg Strong

    Greg Strong
    Expand Collapse
    Guest

    Hello Douglas,

    On Sun, 9 Jul 2006 19:51:58 -0400, "Douglas J. Steele"
    <NOSPAM_djsteele@NOSPAM_canada.com> wrote:

    First thanks for the help!

    >What value do you have for strOConn?


    ODBC;Driver={Oracle in XE};Dbq=XE;UID=MyUID;PWD=MyPswd;

    The above is copy & pasted from a debug.print of strOConn. Obviously
    I've modified the actual UID & Password.

    >In your previous post, you listed a connection string "ODBC;Driver={Oracle
    >in XE};Dbq=XE;UID=MyUID;PWD=MyPswd;"


    Same thing as above except not actual values for MyUID& MyPswd.

    >Just curious as to where that came from. Carl Prothman doesn't list that
    >particular driver anywhere at
    >http://www.carlprothman.net/Technology/ConnectionStrings/ODBCDSNLess/tabid/90/Default.aspx


    The actual file is "SQORA32.dll" dated 2/9/2006 for the driver with the
    "Oracle in XE" name. The actual driver must be installed with Oracle 10g
    Express Edition, since I did NOT download and install it. It is located
    in the following directory:

    X:\oraclexe\app\oracle\product\10.2.0\server\BIN

    This is sub-directory of where I installed Oracle 10g Express Edition.
    You are correct in that he doesn't really list it under "ODBC Driver for
    Oracle (from Oracle)". So I kind of ad lib from the instructions for
    "ODBC Driver for Oracle - from Oracle" which state "Where: The DBQ name
    must be defined in the tnsnames.ora file". This may be where I have a
    problem since I haven't really worked with the "tnsnames.ora" file
    previously, and there is no direct cross reference with "DBQ" in the
    file. The really strange part is this same connection string works with
    your "FixConnections" function located at
    http://www.accessmvp.com/djsteele/DSNLessLinks.html. Now I modified it
    to call the "Sub SetConStr()" in the original post. The modifications
    are as follows:

    Sub FixConnections()
    ....
    Call SetConStr

    tdfCurrent.Connect = strOConn
    ....
    End Sub

    I didn't have to pass the ServerName & Database name because they are
    included in "strOConn". The just of it is your "FixConnections" sub
    works and my test sub on a pass-through query does NOT for what ever
    reason. The interesting part is the ODBC connection remains open with
    the DSN-less connection as well unless you close Access altogether. So
    even though I might eventually solve the DSN-less connection, I will
    still have the open connection problem that exists with the DSN. I do
    like the DSN-less solution better.

    This test is actually part of solution on building a sample DB
    that I had working in SQL Server using an Access ADP file for the front
    end. I thought using an Access MDB file as the front end would add some
    flexibility for temporary tables to build combo boxes as well as
    learning Oracle and interface issues with Access. I am sure I have some
    higher hurdles down the road. :)

    For the benefit I will include my tnsnames.ora file below:

    =====================>Begin File>===================================>
    XE =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = gws-p4-2-4)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = XE)
    )
    )

    EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
    (SID = PLSExtProc)
    (PRESENTATION = RO)
    )
    )

    ORACLR_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
    (SID = CLRExtProc)
    (PRESENTATION = RO)
    )
    )
    =====================<End File<=====================================<

    Again thanks for all of the help!

    --
    Regards,

    Greg Strong
     
  7. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

    DoCmd.OpenQuery "qrySumInvcTest"
    qd.Connect = "ODBC;DSN=OracleXE;DATABASE=4GetU;;UID=NoGo;" & _

    You're trying to open the query before you set the connect?
    That's never going to work.

    > form, it does NOT close the connection. The only way to close
    > connection is to close Access. This is not good if different


    You're using Application.DoCmd to display the data. Once
    Application opens the connection, it stays open until you
    close Application (or it times out or ...).

    If you want to close the connection, you are going to have
    to use a different display method (an unbound form), with
    a seperate ADO object or DAO.dbEngine object.

    (david)

    "Greg Strong" <news.REMOVE@geedubeeu.com.invalid> wrote in message
    news:v4l2b2pd2pfe239e4khno2spdclgomici4@4ax.com...
    > 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").Value) 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
     
  8. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    > You're using Application.DoCmd to display the data. Once
    > Application opens the connection, it stays open until you
    > close Application (or it times out or ...).


    With odbc to sql server, the connections remain open until you shut down the
    application.

    I also would like to be able to close the connection also....

    As far as I know, this behaviors does not change if you use dsn-less, or
    not..

    To close the connection...you have to shutdown ms-access....which often I
    don't want to do....

    There might be another way, but not spent the time looking for, or trying
    solutions.

    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal





    >
    > If you want to close the connection, you are going to have
    > to use a different display method (an unbound form), with
    > a seperate ADO object or DAO.dbEngine object.
    >
    > (david)
    >
    > "Greg Strong" <news.REMOVE@geedubeeu.com.invalid> wrote in message
    > news:v4l2b2pd2pfe239e4khno2spdclgomici4@4ax.com...
    >> 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").Value) 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

    >
    >
     
  9. Greg Strong

    Greg Strong
    Expand Collapse
    Guest

    On Mon, 10 Jul 2006 14:09:02 +1000, "david epsom dot com dot au"
    <david@epsomdotcomdotau> wrote:

    >DoCmd.OpenQuery "qrySumInvcTest"
    >qd.Connect = "ODBC;DSN=OracleXE;DATABASE=4GetU;;UID=NoGo;" & _
    >
    >You're trying to open the query before you set the connect?
    >That's never going to work.


    If you look a few lines above you will find:

    qd.Connect = "ODBC;" & strCnn

    I found the connection string which includes the UID and password is
    saved in the PTQ's properties, so the code you quote above is to
    actually change it to some bogus value.

    --
    Regards,

    Greg Strong
     
  10. david epsom dot com dot au

    david epsom dot com dot au
    Expand Collapse
    Guest

    > With odbc to sql server, the connections remain open until you shut
    > the application.



    Or until they time out, or are shut by system that limits
    the number of open connections, or, if you use a separate object,
    until the object that you used to open them closes.

    (david)

    "Albert D.Kallal" <PleaseNOOOsPAMmkallal@msn.com> wrote in message
    news:uTY4Bl9oGHA.4116@TK2MSFTNGP03.phx.gbl...
    >> You're using Application.DoCmd to display the data. Once
    >> Application opens the connection, it stays open until you
    >> close Application (or it times out or ...).

    >
    > With odbc to sql server, the connections remain open until you shut down
    > the application.
    >
    > I also would like to be able to close the connection also....
    >
    > As far as I know, this behaviors does not change if you use dsn-less, or
    > not..
    >
    > To close the connection...you have to shutdown ms-access....which often I
    > don't want to do....
    >
    > There might be another way, but not spent the time looking for, or trying
    > solutions.
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    > pleaseNOOSpamKallal@msn.com
    > http://www.members.shaw.ca/AlbertKallal
    >
    >
    >
    >
    >
    >>
    >> If you want to close the connection, you are going to have
    >> to use a different display method (an unbound form), with
    >> a seperate ADO object or DAO.dbEngine object.
    >>
    >> (david)
    >>
    >> "Greg Strong" <news.REMOVE@geedubeeu.com.invalid> wrote in message
    >> news:v4l2b2pd2pfe239e4khno2spdclgomici4@4ax.com...
    >>> 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").Value) 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

    >>
    >>

    >
    >
     

Share This Page