This discussion is archived
5 Replies Latest reply: Oct 18, 2012 7:37 AM by xxsawer RSS

How to connect to a MS Access DB?

xxsawer Explorer
Currently Being Moderated
Hi guys,

this time I have a question how to connect to a MS Access DB?
I have an .accdb file located somewhere on the disk. In the new connection window, I switch to Access tab, fill the Connection Name field, search for the file and click Test.
Then I got an error message which can be translated like:
Status : Failure -Test failed: [Microsoft][Driver manager ODBC] Source data name was not found and no default driver was given.

Any idea what could be the problem?
Thanks, Dan
  • 1. Re: How to connect to a MS Access DB?
    kgronau Guru
    Currently Being Moderated
    Commonly the MS Access ODBC driver is being used to access the MDB file. Do you have it installed on your machine?
    What opearting system (including word size) are you using and which MS Access ODCB driver (version and word size) is installed?
  • 2. Re: How to connect to a MS Access DB?
    xxsawer Explorer
    Currently Being Moderated
    Hi,
    thank you for quick reply and sorry for my delay...
    Well, I never cared about the driver, I suppose this should be installed defaultly in the OS.
    I am using W7 64b
    How can I find which version of MS Access ODBC driver is installed?
    I ran: %WINDIR%\SYSWOW64\odbcad32.exe
    and here are my screens
    http://www.imagebam.com/image/32009d215783077
    http://www.imagebam.com/image/7d8483215783079
    http://www.imagebam.com/image/4785d3215783082
    http://www.imagebam.com/image/89bf8a215783083
    http://www.imagebam.com/image/0fddc9215783085
    http://www.imagebam.com/image/868048215783088

    Dan
  • 3. Re: How to connect to a MS Access DB?
    kgronau Guru
    Currently Being Moderated
    Dan, syswo64 is commonly pointing to a 32bit ODBC driver - when you're using the 64bit SQlDev release on a 64bit platform you also need the 64bit ODBC drivers (and those are not installed by default). You need to get the AccessDatabaseEngine_x64.exe from MS which contains the 64bit ODBC drivers and is available here: http://www.microsoft.com/en-us/download/details.aspx?id=13255.
  • 4. Re: How to connect to a MS Access DB?
    xxsawer Explorer
    Currently Being Moderated
    You are right,
    If I run %WINDIR%\system32\odbcad32.exe
    then I don't see any access driver installed
    http://www.imagebam.com/image/97a3cb215789679

    The problem is, when I try to install the 64b driver from your link, I get this error:
    http://www.imagebam.com/image/02e2cc215789684

    This looks like a funny trap :(

    Dan
  • 5. Re: How to connect to a MS Access DB?
    xxsawer Explorer
    Currently Being Moderated
    Ok, so I found the solution.
    I uninstalled the 32b MS Office and installed the 64b version. The product key was working on 64b version as well and the good thing is that the pakcage I used for installation contained the MS Access as well. So I got also trial version of Access.
    After installation I checked the %WINDIR%\system32\odbcad32.exe again and saw that the ODBC driver is installed. So it was probably installed with MS Access.
    The connection to the accdb file from SQL Developer is working now, perfect :)

    I have just one more question.
    I see that in the file are some views, but they cannot be displayed. Clicking/doubleclicking doesn't have any affect.
    I also can't display the SQL query used for that view. I only can export the view to Excel...
    Is there any way how to display the content of the view or the SQL query?

    Dan

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points