Click here to Skip to main content
15,884,177 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I got error like this 'Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ClientAc901".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ClientAc901" returned message "The Microsoft Office Access database engine cannot open or write to the file '\\Pc09\data3\accountdata.mdb'. It is already opened exclusively by another user, or you need permission to view and write its data.".' while importing msaccess data to sql.


Here the msaccess database connection state is closed still i am getting same error.

I have written this code to copy tables from msaccess to sql

C#
void copytable()
      {
          try
          {
              cmd = new SqlCommand("SP_Create");
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.Parameters.AddWithValue("@servername", acc);
              cmd.Parameters.AddWithValue("@datasr", path);
              cmd.Parameters.AddWithValue("@table", sourcetable);
              cmd.Parameters.AddWithValue("@table1", desttable);
              cmd.Parameters.AddWithValue("@db", db);
              cmd.CommandTimeout = 180;
              cmd.Connection = cn1;
              connection.Close();
              string state = connection.State.ToString();
              if (cn1.State != ConnectionState.Open) { cn1.Open(); }
              cmd.ExecuteNonQuery();
              cn1.Close();
          }
          catch (Exception exc)
          {
              connection.Close();
          }
      }


The name connection is msaccess connection i.e oledb and cn is sql connection
Posted
Updated 12-Nov-15 17:22pm
Comments
Sreekanth Mothukuru 10-Nov-15 1:21am    
Have you opened the MS Access file and viewing the DB tables while performing/running this operation?
TarunKumarSusarapu 10-Nov-15 1:23am    
No I have n't opened i closed the connection in the query still got same error i am using 64 bit version of windows.
TarunKumarSusarapu 14-Nov-15 1:59am    
I got this error while running on Network service 'OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ClientAc484" returned message "Unspecified error".' Can u pls solve this error

1 solution

The message has nothing to do with code...It is about access and permission you may not have here...
It states clearly what you have to check...
1. The file may be opened from elsewhere
2. You (the SQL user) may have no access to it

To check your setup, first go SSMS and check there the linked server...
 
Share this answer
 
Comments
TarunKumarSusarapu 12-Nov-15 0:07am    
Can u pls explain this briefly in 32 bit pc it is working fine in 64 bit it is getting like this. How to modify the folder permissions or other wise how to alter the linked server permissions
TarunKumarSusarapu 14-Nov-15 1:59am    
I got this error while running on Network service 'OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ClientAc484" returned message "Unspecified error".' Can u pls solve this error

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900