none
Access 2007 and Windows 7 and No System Data Sources Available in ODBC - (Access Forum)

    Question

  • Had this in a Windows 7 forum, was told to post here. Any and all replies will be appreciated. Thanks.

    --------------

    We are piloting conversion to Windows 7. As a part of the pilot I need to set up a client's computer to use Access 2007 to attach to an Oracle database on a Linux server. The requirements of the 3rd party software are that the ODBC connection be set up as a System Data Source in ODBC. The ODBC set up works. Testing the connection using the Oracle ODBC driver works. Connection is listed under the System DSN tab in ODBC.

    When I went MS Access to try and link to a table from the Oracle db, *no* System DSN's appear in on the Machine Data Source tab in the Select Data Source window; only User DSN's appear. The very *first* time I click on the New button in the Select Data Source window on a fresh boot of Windows 7 I get an error message that says:

         ODBC System DSN Warning

         You are logged in with non-Administrative privileges. System DSN's could not be created or modified.

    But I *am* logged in as an administrator...

    In addition, if I continue to click through and try to create a new User Data Source I get a long list of drivers - much longer that the list that is presented in ODBC in Administrative tools - a totally different list...

    I have the RSAT for Windows 7, but want to ensure that this, in fact, will be the solution. Again I can *create* a System DSN via ODBC, but cannot *access* a System DSN in Access 2007.

    Any and all constructive feedback will be appreciated.

     

    Thanks,

    Verna Lewis

    Tuesday, August 24, 2010 5:38 PM

Answers

  • May not help but try it anyway.

    Why not install Oracle ODBC driver. I never seems to get microsoft oracle driver to work with oracle server in the past.


    Thanks for your feedback.

    But finally, finally, finally got this working like 5 minutes ago with help from Oracle support. Now I did *SO* many things that I may be missing a step here, but when I re-create this for my client I will log the steps more carefully and re-post here.

    1. FIRST - Anything you do for this under Win7, do as Administrator

    2. I downloaded the Oracle 10.2.0.1 client (32-bit even though am running 64-bit Win7 - because am runnign 32-bit Access 2007), but had to patch that client to 10.2.0.5.0

    3. Changed ODBC to use the 32-bit exe in syswow64

    4. Changed the registry dll's

    Voila! Working...

     

    Verna

    Friday, August 27, 2010 9:14 PM

All replies

  • Verna,

    There are issues between 32-bit and 64-bit OSs for configuring ODBC.

    What edition of Win7 are you trying to configure? 32-bit or 64-bit?

     

    Tuesday, August 24, 2010 6:13 PM
  • See if this info helps at all:

    Apparently, Windows 7 64 bit uses a 64 bit ODBC in the Windows/System32 directory, but Access references a 32/64 bit versionin the Windows/SYSWOW64 directory. In order to run SQL Server 2008 R2 64 bit and connect to Access (32 bit) via ODBC, all the registry entries (find with ODBC.INI) need to refernce the SYSWOW64 dirctory. Once I did this, everything worked and was speedy-fast.

    Tuesday, August 24, 2010 6:27 PM
  • Thanks. Am using 64-bit Win7.
    Tuesday, August 24, 2010 7:32 PM
  • Will try this. Changed ODBC to use %windir%\syswow64\odbcad32.exe and got a little farther, but not there yet.

    Thanks

    Tuesday, August 24, 2010 7:35 PM
  • OK here is my update...

    1. Installed the 32-bit Oracle client. Had some a few issues configuring it, but was able to do it so that the tnsnames.ora is accurate and I can connect to the Oracle db with the Net Mgr software when I Run as an Administrator. I am also following up with Oracle about this...


    2. Also had to change ODBC to use the 32-bit version of ODBC using C:\windows\syswow64\odbcad32.exe. This was the only way that I could get the System Dsn's to show up in the ODBC
    connections in Access. The database I need to use must be set up as a System Dsn in order for other things to work.

    3. When I run MS Access as Administrator, and I try to connect to the System DSN that I want, I get

    [Microsoft][ODBC driver for Oracle][Oracle]ORA-06413 Connection not open. (#6413)[Microsoft][ODBC Driver Manager] Driver's SQLSetConnect Attr failed IM006 0 [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed (#0)

    Now I have seen the IM006 in prior years with other problems. I had already gone into the registry and changed HKLM\SOFTWARE\Microsoft\MSDTC\MTxOCI to use the following dll's

    OracleOciLib - oci.dll
    OracleSqlLib - sql10.dll
    OracleXaLib - oraclient10.dll

    The path for these dll's is in the PATH environment variable.

    4. When I run Access as Administrator *and* as Compatibility for XP SP2, or when I run *only* as Compatibility for XP SP2 I get the same error as in #3 above. Same with running *solely* as Compatibilty for XP SP3.

    5. When I run *not* as Administrator I get

    [Microsoft][ODBC driver for Oracle][Oracle]ORA-01019: unable to allocate memory in the user side (#1019)[Microsoft][ODBC Driver Manager] Driver's SQLSetConnect Attr failed IM006 0 [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed (#0)

    Not sure what to try next...

    Thanks, Verna

    Thursday, August 26, 2010 7:48 PM
  • May not help but try it anyway.

    Why not install Oracle ODBC driver. I never seems to get microsoft oracle driver to work with oracle server in the past.

    Friday, August 27, 2010 3:45 AM
  • May not help but try it anyway.

    Why not install Oracle ODBC driver. I never seems to get microsoft oracle driver to work with oracle server in the past.


    Thanks for your feedback.

    But finally, finally, finally got this working like 5 minutes ago with help from Oracle support. Now I did *SO* many things that I may be missing a step here, but when I re-create this for my client I will log the steps more carefully and re-post here.

    1. FIRST - Anything you do for this under Win7, do as Administrator

    2. I downloaded the Oracle 10.2.0.1 client (32-bit even though am running 64-bit Win7 - because am runnign 32-bit Access 2007), but had to patch that client to 10.2.0.5.0

    3. Changed ODBC to use the 32-bit exe in syswow64

    4. Changed the registry dll's

    Voila! Working...

     

    Verna

    Friday, August 27, 2010 9:14 PM
  • Verna,

     

    Cool! at least now we know that there IS an answer! :-)

    Friday, August 27, 2010 9:23 PM
  • Hi Verna,

    i am new to oracle database.

    regarding step2, how did u patch 10.2.0.1 to 10.2.0.5.0

    would you please mention the steps execute step 3 and 4.

     

    thanks in advance...

    Tuesday, February 22, 2011 5:22 AM
  • M_Aamir,

    No:2...
    If you have access to Oracle, download from there else do a google search.

    No: 3 & 4
    Just simply go to the command prompt in windows, like (for 32-bit)

    regsvr32 "C:\Path to file\thefilename"    .....to register the file into windows, you might need to re-start windows.

    If you install the 32-bit version, you should look to that folder (program files syswow64).

    You select the new Oracle ODBC drivers in Access or the create a new Data Source (DSN) with the new driver.

    Tuesday, February 22, 2011 6:04 AM