SSIS: Can't import data from MS Access: The workgroup information file is missing or opened exclusively by another user

Question

Post reply

SSIS: Can't import data from MS Access: The workgroup information file is missing or opened exclusively by another user

Maxer

SSCrazy Eights

Points: 8964
More actions
September 1, 2008 at 1:18 pm

#197721

I need to use an MS Access database as my datasource to do some work on it to migrate it to SQL Server.
The MS Access database has workgroup security, so when I enter the username and password in Integration Services and go to test the connection I get:
"The workgroup information file is missing or opened exclusively by another user" (Well that's part of the error, the full error is basically saying the test connection failed because it can't initialize the provider)
Anyway, I can't be the only person who has these issues, how can I get SSIS to pull data from a workgroup secured Access database?
I provided the workgroup user and login but that doesn't do anything.
Is there some sort of trick/workaround for this?
Thanks!

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Pardon me for asking the obvious

The workgroup information file is missing or opened exclusively by another user"

Is it missing? Opened exclusively by another user?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Maxer SSCrazy Eights Points: 8964 More actions · Answer 2

No on both accounts...

If I remove security from the database it works fine (http://msdn.microsoft.com/en-us/library/aa141436(office.10).aspx)

Otherwise I get that error.

I don't get it, I provide SSIS with my the admin login, I tried creating a login in the admin group and giving it that user and pass, still nothing.

I don't know if maybe SSIS can't load the workgroup security .mdw file or something?

Maybe you HAVE to open the database with Access and Access itself goes out and searches to find that file and then opens it to authenticate and SSIS can't do that?

Not a clue... 🙁

Maxer SSCrazy Eights Points: 8964 More actions · Answer 3

I received an email about this post by someone asking how it was resolved.

Anyway here is what I did:

I gave up... and just exported the table data to a NEW Access database and pointed SSIS to that... 🙁

I basically followed the instructions for how to unprotect an Access database that is password protected.

Also I made a LOCAL copy of the workgroup security .mdw file and adjusted the permissions on that. (So not to interfere with other users of the network database).

Basically I copied it to my desktop along with the Access database, made the adjustments, exported the table to a new Access database I had setup.

Then I setup some simple cleanup queries and a Macro to kick them all off in a row.

So when it came time for the migration I copied the Access database to my machine, exported the table, kicked off the Macro and then a minute or two later once all my cleanup queries ran that I wanted to run in Access (deleting bogus rows, rows with no actual content, etc.... as there was no point in sending those to SSIS) then I kicked off my SSIS package and it did the rest.

darth vodka SSCrazy Points: 2367 More actions · Answer 4

i had a similar problem, but have got it to work

connection manager>add a new OLE db connection>

new>Microsoft Jet 3.51 OLE db provider>

put the db name in the Database file name and the workgroup login/password in user name & password

then click "All" on the left and stick the workgroup path in "Jet OLEDB:System Database"

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 5

Oliver: Could you show us an example, or perhaps a properties window? I don't understand from your description what goes where. Was yours a multi-user .mdw file, or the default one local to the user after simply assigning a password to the Access db?

Thanks!

darth vodka SSCrazy Points: 2367 More actions · Answer 6

Could you show us an example, or perhaps a properties window?

ok...attached

[Quote]Was yours a multi-user .mdw file, or the default one local to the user after simply assigning a password to the Access db?

[/quote]

it's not the default one...it's a bespoke one and the database has the permissions switched off for the "Admin" user so you can't log into it without a username/password that is in the workgroup

technically, the default one isn't assigned when you give the db a password, it's always there and everytime you open an Access db there is a workgroup file associated

http://support.microsoft.com/default.aspx?scid=%2Fsupport%2Faccess%2Fcontent%2Fsecfaq.asp

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 7

Jim Russell-390299

SSCrazy Eights

Points: 9333

November 12, 2008 at 7:59 am

#897553

Thanks. That might help.

darth vodka SSCrazy Points: 2367 More actions · Answer 8

darth vodka

SSCrazy

Points: 2367

January 8, 2009 at 5:01 am

#923205

quick point, you need 3.51 for Access 97

miro_rad SSC Enthusiast Points: 132 More actions · Answer 9

miro_rad

SSC Enthusiast

Points: 132

March 19, 2010 at 6:42 am

#1136660

Thanks very, very much. :w00t::w00t:

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 10

Sounds like you got it to work with a password protected .mdb.

If Darth's suggestion helped ("quick point, you need 3.51 for Access 97") I gotta show my stupidity and ask:

What is 3.51??

darth vodka SSCrazy Points: 2367 More actions · Answer 11

3.51 is the version of the Jet driver

http://www.devx.com/tips/Tip/13652

mikecandlewood SSC Journeyman Points: 87 More actions · Answer 12

mikecandlewood

SSC Journeyman

Points: 87

April 14, 2010 at 1:54 pm

#1151702

Thank you. This helped me today.

man007f Valued Member Points: 56 More actions · Answer 13

Hi, I got the same issue, and I fixed it with Darth's solution. But I met another issue as below. Any thought of it, expert?

I had configured the .MDB, .MDW, UserID & PWD in the connection Manager. But this same configuration had brought me 2 different result when I tried to connect to Access Database via the SSIS.

1.At first I could setup the connection, and pass the Test Connection.

2.but very soon later, when I executed the Test Connection again, or use it to open the Access Database Tables, the connection was broken, and showed me the below error.

3.And it was very strange that, when I wait for “some time” and tested the connection again, it may succeeded, or may failed.

4.And I have checked the Access .MDB & .MDW file, both are in a good condition.

5.And I have tried both OLEDB & ADO.Net connection provider, both were met the same issue.

6.And I have tested in different machine, failed as well.

darth_vodka Default port Points: 1489 More actions · Answer 14

man007f (11/16/2010)
Hi, I got the same issue, and I fixed it with Darth's solution. But I met another issue as below. Any thought of it, expert?
I had configured the .MDB, .MDW, UserID & PWD in the connection Manager. But this same configuration had brought me 2 different result when I tried to connect to Access Database via the SSIS.
1.At first I could setup the connection, and pass the Test Connection.
2.but very soon later, when I executed the Test Connection again, or use it to open the Access Database Tables, the connection was broken, and showed me the below error.
3.And it was very strange that, when I wait for “some time” and tested the connection again, it may succeeded, or may failed.
4.And I have checked the Access .MDB & .MDW file, both are in a good condition.
5.And I have tried both OLEDB & ADO.Net connection provider, both were met the same issue.
6.And I have tested in different machine, failed as well.

what were the error messages?

is it an Access 97 db?

as a guess, the difference may be whether someone is in the db...if they are there is an ldb locking file created...