Copy From Access To SQL server

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 2000 Forums

Import/Export (DTS) and Replication (2000)

Copy From Access To SQL server

Author

Topic

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-10-03 : 15:34:37

Hi,

I'm trying to copy data from a table of an Access DB, to a table in SQL server.

I have
SQL server 2000 / Management Studio
Acess 2000 Format DB / Access 2003 software

I used the following and receiving an error.

SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0', 'c:\CPC.mdb', tblPC)

[OLE/DB provider returned message: Could not find installable ISAM.]
OLE DB error trace [OLE/DB Provider 'MICROSOFT.JET.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MICROSOFT.JET.OLEDB.4.0' reported an error.

Aslo I tried using the
sp_addlinkedserver 'AccessPC', 'Access 2000', 'Microsoft.Jet.OLEDB.4.0', 'C:\CPC.mdb'

but didn't work.

I do need to use a SQL statement and no other means as DTS.

Thankyou for your time

Srinika

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2007-10-03 : 16:19:51

Hi I think I found the error, but I need to overcome that:

I'm using the path in the Local Machine ('C:\CPC.mdb).
Is there a way to give the path of the local machine here ?
eg. 'MyMcName\C:\CPC.mdb

Srinika

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-03 : 23:10:49

Use unc name like \\host\share\file, and ensure sql service account has permission on that location.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources