Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

http://office.microsoft.com/en-us/access-help/what-happened-to-user-level-security-HA010342116.aspx

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'.

Questions:

  1. Why does this data source insist on looking for a workgroup information file even though user-level security should be disabled?

  2. Why does the Open dialog box for the Microsoft Access Database Engine have *.mdb as the only selectable filetype?

  3. How can I get around these errors for an .accdb that is password protected?

  4. Why is SSDT behaving differently from MSSQL? I need SSDT to work to use SSIS.

Thanks.

share|improve this question
    
Is the SSIS project in 32- or 64-bit mode? –  Jon Seigel Feb 3 at 18:36
    
VS10 Shell seems to be a 32-bit app. However, I see a flag in Project properties -> COnfiguration Properties -> Debugging -> Run64BitRuntime == True. –  jordanpg Feb 3 at 18:49
    
Also, I have noticed that by uninstalling the 64-bit 2010 redist and installing the 32bit version, this problem is solved. But then I can no longer use the 64-bit Import/Export Wizard because it refuses to install both 32- and 64-bit 2010 redists. –  jordanpg Feb 3 at 18:50
    
Visual Studio is always 32-bit. If you have the 64-bit redist installed, does the SSIS package work if you execute it using the Execute Package utility? –  Jon Seigel Feb 3 at 18:53
    
@JonSeigel It's already uninstalled. Executing the package with the utility works as expected. I can live with 32-bit MSA drivers for what I'm doing, though I'm still unclear about what was happening here. –  jordanpg Feb 3 at 18:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.