CODEWITHSUNDEEP
sql-serversql-server-2008ms-accessssis
SaintWacko
SaintWacko

Reputation: 924

Error on attempting to import data from Access to SQL Server

I am trying to import data from an Access 2003 mdb database using OpenDataSource with the ACE OLEDB driver. I'm getting this error:

 Description: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".

I have no idea what it's talking about, and my internet searches have not helped. They all refer to linked servers, and anything I've tried has done nothing to fix the problem. What does this error mean, and what do I need to do to fix it?

The sproc which does the importing is called from an SSIS package, which goes through a table of file locations and calls the sproc for each of them. I just discovered that the package ran for about half an hour, getting about 80 files imported, before it began throwing this error. I have as yet been unable to find any difference between the files that worked and the ones that didn't.

Upvotes: 1

Views: 4639

Answers (2)

Lloyd Banks
Lloyd Banks

Reputation: 36638

The Access database that SQL Server is trying to access is being used by another user. Go to the folder where the database is stored. With Access 2003, the database file itself will end in 'mdb'. If there is a file with the same name but ending in 'idb', it signals that there is a user currently using the 'mdb' file. Have the user exit the database and then run your package. If you can't find the user using the file, make a copy of the file in another folder and edit your SSIS package to reflect the change.

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

From Understanding the role of workgroup information files in Access security:

The workgroup information file is a required component when you use a Microsoft Access database (MDB). This file is required for both a run-time installation and a full installation of Microsoft Access. This file is an important component of Microsoft Access security.

If you develop database applications, it is important that you have a good understanding of the workgroup information file. It is a good idea to reserve the last phase of the development process for applying security in Access. Until then, you can develop the database application in an unsecured database.

A workgroup is a group of users who share data in a multiuser environment. When security is implemented on a database, the user and group accounts are recorded in the workgroup information file. User passwords are also stored in the workgroup information file.

IMPORTANT: If you establish Access security in a database, Microsoft recommends that you store a backup copy of the workgroup information file in a safe location. If the file is lost or damaged, the only way to recover the workgroup information file quickly is to restore the file from a backup copy. If you do not have a backup copy, you must re-create the User and Group Accounts with the same Personal IDs that were originally assigned. If the new workgroup information file is not created exactly as the original file, you will not be able to open the database with the workgroup file.

Access uses the workgroup information file even when the database has not been secured. The default Admin user account, which is stored in the workgroup information file, is used to open all unsecured databases. If you assign a password to the Admin user, you will receive a logon prompt when you reopen the database.

Upvotes: 2

Related Questions