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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 '14 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 '14 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 '14 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 '14 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 '14 at 18:58

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.