Import Access into SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Import Access into SQL

Author

Topic

meyercombi
Starting Member

2 Posts

Posted - 2009-06-10 : 02:25:11

I have an Access database, MyAccessDatabase.mdb, which I need to "change over" into a SQL database. I use the following:
EXEC sp_addlinkedserver 'MyLinkedServerName', 'OLE DB Provider for Jet','Microsoft.Jet.OLEDB.4.0', 'C:\PathToTheAccessDataBase\MyAccessDatabase.mdb'
EXEC sp_addlinkedsrvlogin 'MyLinkedServerName', 'false'

When I run the following:
INSERT INTO mySQLTable
SELECT column1, column2, column3
FROM MyLinkedServerName...MyAccessTable
I get the following error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "waterworks_kzn" returned message "The Microsoft Jet database engine cannot open the file 'C:\PathToAccessDatabase\MyAccessDatabase.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyLinkedServer".

I have full permissions in SQL, and the Access database is NOT opened anywhere on my pc.

Can anyone please help?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-10 : 02:27:11

Better you can try DTS!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

meyercombi
Starting Member

2 Posts

Posted - 2009-06-10 : 03:01:59

Thank you senthil for the quick response.
Could you please provide me with a helpful link, or tips/pointers to see how DTS actually works in practise, because the book I have from Microsoft has 2 paragraphs in about DTS.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-10 : 04:23:52

When you run the query, make sure the mdb is closed

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-10 : 05:08:44

quote:
Originally posted by meyercombi

Thank you senthil for the quick response.
Could you please provide me with a helpful link, or tips/pointers to see how DTS actually works in practise, because the book I have from Microsoft has 2 paragraphs in about DTS.

Hi

Navigate this you get some idea..

http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources