sql-serverms-accessssmsms-access-2003

How to run queries on an Access 2003 database on localhost using SSMS

I have an Access 2003 database on my computer (.mdb). I would like to test some queries on it on localhost using SQL Server Management Studio.

I have already configured a local server on SSMS called acer-davide and an empty database called mydb. My first idea was to somehow connect or import the mdb database to mydb but when I do File --> Open --> File: my mdb file, SSMS tries to open it using Access, so I cannot create a linked server.

N.B.: without having Access 2003 installed on my machine.

Any suggestions?

Solution

  • You MUST have at least a copy of the JET data engine installed. So, you don't necessary need full access, or even the free runtime, but you WILL require that the JET data engine is installed on that server where SQL server is running (not your client computer).

    Turns out since windows 98 days, the JET data engine is installed + included on ALL copies of windows - including server based editions.

    PROBLEM!

    JET data engine is ONLY x32 bits. So, you would have to be running a x32 instance of SQL server (good luck doing that today. While 10 years ago, one might run a x32 bit copy of SQL server (or sql express), these days, NEAR ALL instnaces of SQL server installed are x64 bit editions.

    That means you will have to install the newer "ACE" data engine drivers. That driver comes in a x64 bit version flavor, and that ACE data engine does not require you to install access, or office, or even the access runtime.

    But you WILL have to install the x64 bit version of the ACE data engine. The x64 bit ACE data engine can open mdb files (but not the pre 2000 versions (23 years old!!!).

    And the ACE data engine can open the newer accDB access data files.

    While the instance of SQL server is thus x64 bits, and thus the instance of ACE will also have to be x64 bits?

    Once you set this up correctly, then even a x32 bit client via odbc etc. CAN connect to that instance of sql server - even when x32 bits client.

    so, you can do this, but you will require having to install the ACE data engine if you are running x64 bit edition of SQL server. (not even sure that the x32 bit version of SQL server is available anymore - but regardless, you MUST match the bit size of the Access data engine driver to the bit size instance of SQL server that you are running.

    You can download + install the newer x64 bit version of the ACE driver here:

    https://www.microsoft.com/en-us/download/details.aspx?id=54920

    So, if you are looking to NOT have to install any software? yes, you can do this but then the requirement is:

    **

    1. You are ONLY going to open mdb files - not accDB ones.

    And you are

    1. running x32 bit version of SQL server.

    **

    So if the above 2 requirements are met? Then yes, you can do this without having to install ANY software on the server, since as I stated, all windows editions starting around windows 98 includes the JET based driver by default. However, the ACE data engine (which is required for x64 bit operations) is not installed by default.