Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2005
    Posts
    32

    Unanswered: Pass Thru Queries and ODBC Driver Question

    I have a front end MDB which builds pass thru queries based on user input.

    The pass thrus access an ORACLE database. I have a local table which contains the ODBC connection information. The code reads the value of the sql connect string and uses that connect property when building the query.

    For example:
    sql connect string: ODBC;DRIVER={ORACLE ODBC DRIVER};SERVER=ORAP080;UID=something;PWD=something else;DBQ=ORAP080

    This works fine for all users who happen to be running the same version of ORACLE and have the ORACLE ODBC DRIVER installed. However, a number of PCs are now being upgraded to Oracle 9 so their driver is Oracle in ORAHome92, not ORACLE ODBC DRIVER.

    My code needs to be flexible enough to run on PCs regardless of the Oracle version installed. How can I do this?

    Thanks in advance.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could use try;except or error handlers to keep plugging in a drivers until you make a successful connection. Or you could store the string in an .ini or other configuration file then reference this file for your connection string. Or you could use file dsn's and package those along with the app.

    Depends on your specific environment I guess...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Mar 2005
    Posts
    32
    The application is in Access 2000 and uses DAO. It needs to run on W2K and XP PCs. The W2K PCs have Access 2000 installed. The XP boxes have 2003 installed. As such, the application cannot be upgraded to 2003. The PCs have all different versions of Oracle installed: Oracle 8, 8i, and 9i. The PCs can be upgraded at any time and I don't have visibility to that. As such, I would not be able to maintain a PC specific ini file.

    I just found a folder on my W2K PC that holds the various DSN files. Assuming that's consistent with XP boxes, I may be able to read that file to determine the driver.

    Thanks for your suggestion. If there are any others with ideas, I'm all ears.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Is there anything that consistently distinguishes the PCs? I don't know how to check for oracle but if you use default installs perhaps you can check for existing folders... the filesystemobject (though Izy probably knows a better VBA function) has a FolderExists method. Pseudocode:

    Code:
    Dim fso as New Scripting.FileSystemObject
     
    If fso.FolderExists "C:\Program Files\Oracle8i" Then 
    'Driver = x
    Else
    'Driver = y
    ???
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Mar 2005
    Posts
    32
    No, there is nothing that distinguishes the PCs. An XP box could be running any version of ORACLE listed as could the W2K ones. I did see on MY PC a "c:\program files\common files\odbc" folder that has the .dsn files listed. Perhaps I could interrogate these files but, first, I need to see if all of the PCs use this same folder.

    Do you know the fso code that let's me read the file?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Sorry about the delay - been a bit tied up last few days.

    The below is something I knocked together. Depends on a few things but it might be a starting point for you. There are other methods: quite possibly better - Izy has a habit of trumping my fso code.

    Code:
    Function DSNParse() As String
     
        Dim fso As New Scripting.FileSystemObject
        Dim ts As Scripting.TextStream
        Dim s As String
     
        Set ts = fso.OpenTextFile("C:\Program Files\Common Files\ODBC\Data Sources\MyDSN.dsn", ForReading)
     
        Do While InStr(s, "Driver") = 0
     
            s = ts.ReadLine
     
        Loop
     
        s = Replace(s, "Driver=", "")
     
        DSNParse = s
     
        Set ts = Nothing
        Set fso = Nothing
     
    End Function
    EDIT - you might, for example, want to put in a breaker in the loop just in case (for whatever reason) Driver does not occur in the file. A few other bits of tinkering might be in order too.

    HTH
    Last edited by pootle flump; 02-02-06 at 17:29.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you can use a fallover error trap wonderfull. Personally I'd use a local setting, retrievd from whereever (a local table, local file, registry etc... And implement a mnual procedure that coerces your network trolls to include you in the loop when they go about tinkering without thinking. I'd alos put a helpfull message oin your app suggesting they contact aforementioned trolls to fix their problem.

    Just thinking aloud, when the Oracle client is installed on a particular PC does it leave its fingerprints in the registry, could you retrieve the required information from there? After all somewhere on the client PC there has to be a registration of yoru Oracle subsystem, otherwisae you wouldn't be able to manually set up an ODBC source.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Mar 2005
    Posts
    32
    Thanks Pootle Flump and Mark for your replies. I have a bit of investigating to do with regards to the PC setups and configurations before I decide if reading the dsn files on the c: drive will work or if reading the registry will be a better solution. Either way, you both have me thinking.
    Thanks Pootle Flump too for the code sample. It's very helpful.

    Tater

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you can figure out how to interrogate the registry for this info I would use that - much more reliable than the content of the DSN. Only thing is that, to my feeble mind, it is a bit trickier.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Mar 2005
    Posts
    32
    I just googled for some registry reading code and found this:

    http://www.mvps.org/access/api/api0015.htm

    I can see why you stated it is a bit trickier. No sense reinventing the wheel. This code looks like it will do the trick.

    Thanks again for your timely reply.

    Tater

  11. #11
    Join Date
    Mar 2005
    Posts
    32
    I just googled for some registry reading code and found this:

    http://www.mvps.org/access/api/api0015.htm

    I can see why you stated it is a bit trickier. No sense reinventing the wheel. This code looks like it will do the trick.

    Thanks again for your timely reply.

    Tater

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Handy function (bookmarked )

    Once you have pasted the code in then it is actually easier to get info from the registry than write some fso code (as above). Good ol' Dev!
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •