7

How to import data from Access .accdb file to SQL Server? What should I choose in SQL Server Import and Export Wizard?

enter image description here

Suppose we choose OLE DB Provider for SQL Server Integration Services, then if that is a correct direction, what should be choose next from the following window? What to type in fields Data Source and Location, which mean more or less the same?

enter image description here

I followed instructions described here: Easiest way to import access db (.accdb) into SQL Server 2016? I have installed Microsoft Access Database Engine 2010 Redistributable

However I was hopeless in seeing Microsoft Office 12.0 Access Database Engine OLEDB

Update

If I choose Data Source as Microsoft Access (Microsoft Access Database Engine) I end up getting error message the one shown on picture below. Moreover I think that this data source is tailored for files mdb, not accdb. enter image description here

RDFozz
  • 11,731
  • 4
  • 25
  • 38
Przemyslaw Remin
  • 241
  • 1
  • 3
  • 7

2 Answers2

2

I've had to import Access files into SQL Server before and haven't found the Import and Export wizard to be a very good tool for the job. What I ended up doing was opening up the Access file, finding the table, right clicking and going to Export -> ODBC Database. This does require you to have a User or System DSN setup for the target SQL Server database, and it is a bit slow, but it was the only thing that I found that worked all that well. (It will create the target table for you.)

nateirvin
  • 756
  • 1
  • 6
  • 22
2

Normally an Access Database file is protected with the Admin account and no password.

Solution

Enter Admin into the User name: field and leave the Password: field empty. This should work.

John K. N.
  • 18,854
  • 14
  • 56
  • 117