Running MSSQL12, VS10 Shell, MSA2013 with local .accdb files, Microsoft Access Database Engine 2010 Redistributable (x86_64) and Microsoft Access Database Engine 2007 Redistributable (x86) installed.
While trying to import to MSSQL2012 from SSDT using the wizard from an Access 2013 DB (.accdb) which is password protected, I get the following error with the username blank and the password field correctly populated:
Cannot start your application. The workgroup information file is missing or opened exclusively by another user. (MicrosoftOffice Access Database Engine)
But according to this link:
MSA10 and beyond shouldn't need workgroup information files, although it's not clear to me if they are created/used or not.
I can point the wizard to some local version of system.mdw at Advanced -> All -> Jet OLEDB:System Database, and then the error becomes:
Not a valid account name or password. (Microsoft Office Access Database Engine)
for any username I can think of.
I have discovered, serendipitously, that using the SQL Server Import and Export Wizard from the start menu (Import and Export Data (64-bit)), this problem goes away and the export proceeds normally.
That is, I select as data source "Microsoft Access (Microsoft Access Database Engine)", then my .accdb file. In this case, it does not even ask for a password with the password field blank. This suggests that it is not looking for a workgroup information file, as expected for a .accdb from MSA07 or later.
Also, I see that this problem is not limited to the wizard inside SSDT. If I try to add a Connection Manager using the "Native OLE DB\MS 12.0 Access DB Engine OLE DB Provider", and then select my .accdb in "Server or File Name", when I "Test Connection" I get
Test connection failed because of an error in initializing provider. Unrecognized database format 'C:\myDB.accdb'.
Why does this data source insist on looking for a workgroup information file even though user-level security should be disabled?
Why does the Open dialog box for the Microsoft Access Database Engine have *.mdb as the only selectable filetype?
How can I get around these errors for an .accdb that is password protected?
Why is SSDT behaving differently from MSSQL? I need SSDT to work to use SSIS.