Share via

What is the easiest way to import Access database files (.accdb) into SQL Server 2017?

Anonymous
2023-01-16T23:33:24+00:00

We are using Access database files in the newer *.accdb file format. We are trying to import data from these files into a SQL Server 2017 Standard edition instance.

Regardless of whether we use the x32 or x64 version of SQL Server Import Wizard installed on our production server, we are only able to choose the "Microsoft Access (Microsoft Jet Database Engine" data source from the drop-down list. This option only reads the old Access *.mdb file format.

While I found many posts online discussing using import/export data tools in SQL Server, as well as the SQL Server Migration Assistant for Access, none of the posts have worked. Most posts have provided little useful information, or the instructions were so poorly written. It seems this question and problem has been around for more than a decade, and has affected SQL Server users right through SQL Server 2022.

Does anyone know how to easily import data from an Access database to SQL Server? Specifically the "*.accdb" file type?

  • We are using the most current version of Microsoft 365 Access
  • We are using the most current version of Windows 10
  • We are using SQL Server 2017.

Note to Microsoft Access/SQL Server development team - guys, could you please kindly help solve this nauseating problem once and for all and provide us SQL Server IT staff with a new built-in driver for the newer Access .accdb file type for all versions of SQL Server Import Wizard? It would save us a HUUUGE amount of time and frustration.

Ruperstland,

Canada

Microsoft 365 and Office | Access | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

22 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-01-18T20:44:17+00:00

    it not at all clear, but we have MULTIPLE posters having suggested that on your workstation, you install ACE x32, and use the /passive switch. Is there some REALLY BIG huge issue why you ignoring this suggestion that been given here multiple times?

    I mean, it might not work, but to save hassle, world poverty and a bunch of hand stands?

    It would seem that the simple approach here is to try the /passive switch when installing ACE x32, and that should work.

    Now, it might not work for you. However, for everyone else in the future, then that information ALSO would be of GREAT value to the community here.

    I am 100% fine and happy if the /passive switch suggestion did not work, (or can't work). but, it should.

    So, I am somewhat perplexed why you have this suggestion multiple times and that simple suggestion is being ignored? why?

    (or maybe it did/does not work for you!). But, that would help everyone here in the future looking at this post.

    So, a simple install of ACE with the /passive switch on your workstation is all that should be required here as far as I can tell.

    Using the /passive switch will "remove" and get you "past" the installer refusing to install ACE x32 due to you already having office/access x64 on that workstation.

    It looks like you have this working, but as noted, a simple less cost and less hassle solution was suggested here multiple times.

    I also stated that it don't matter what you install on the server side UNLESS you directly using and running SSMS on the server, and I often do this, since everything these days for me is a vm that I remote into during typical work day.

    So, you don't have to install anything at all on the server - UNLESS you working directly on that server.

    You don't have to install, choose to launch a different version of the wizard either.

    You ONLY require to install ACE with the /passive switch on YOUR workstation , and you should be good to go.

    R

    Albert

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-01-17T22:13:52+00:00

    Was this answer helpful?

    0 comments
  3. Anonymous
    2023-01-17T22:03:17+00:00

    And did you see my comments and suggestion?

    I stated this:

    And if you are having trouble installing the ACE x32, due to you having office x64?

    Try installing from the command line:

    $yourPath$\AccessDatabaseEngine.exe /passive

    So, YES, you have to install the ace driver ON the same workstation running SSMS.

    However, I did suggest doing this on the server, but that assumed that you working on the server directly (say via remote desktop).

    So, do give my instructions a try. You do NOT need to install the ACE driver on the server unless you are running SSMS from/on the server.

    R

    Albert

    Was this answer helpful?

    0 comments
  4. Anonymous
    2023-01-17T22:01:02+00:00

    For the time being, we are using the *.mdb file format to read the data into SQL Server.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2023-01-17T21:58:46+00:00

    Hi Albert.

    I was unable to install the x32 bit version on my workstation, because all our Office apps are x64 bit and the x32 bit driver is not compatible. See error message below.

    I would be able to install the x32 bit version on our production server where SQL Server lives, because we do not have any Office apps installed. As you mentioned earlier, it helps NOT to have Office installed. So, that's okay with me. I will get the IT guys to install the x32 bit driver and see what happens.

    So, essentially, for the 64-bit redistributable to work, the SSMS version installed on the machine must also be 64-bit. It's not only the import wizard that must be 64-bit, is this correct?

    Rupertsland,

    Canada

    Was this answer helpful?

    0 comments