Select or copy data from an MSAccess db

Question

Post reply

Select or copy data from an MSAccess db

Ken at work

Hall of Fame

Points: 3426
More actions
October 7, 2010 at 6:56 am

#224824

I don't know if I should place this question here or in the Access forum.
There is a legacy Access db which has some data I need in a TSQL calculation.
Is there an easy way to connect to Access and copy the data? I tried moving just that one table to SQL and had all kinds of problems. The Access programer has Record Locks on forms etc... so moving (and linking) the table itself doesn't work. So if I could somehow access the db and simple read the table that would be great.
I appreciate your help,
Thank again

Viewing 7 posts - 1 through 6 (of 6 total)

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

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 1

Try to use the linked server option.

listed below is a nice article

http://www.aspfree.com/c/a/Microsoft-Access/Configuring-a-Linked-Microsoft-Access-Server-on-SQL-2005-Server/

---------------------------------------------------------------------------------------
It begins by taking the first step.

Ken at work Hall of Fame Points: 3426 More actions · Answer 2

I've been following the directions. Even checked on MSDN and I still can't get in.

Here is the code:

EXEC sp_addlinkedserver

@server = N'My_Test'

, @provider = N'Microsoft.Jet.OLEDB.4.0'

, @srvproduct = N'OLE DB Provider for Jet'

, @datasrc = N'\\Server_03\share\My_Test_Db.mdb';

exec sp_addlinkedsrvlogin

@rmtsrvname='My_Test'

, @useself='false'

, @rmtuser='Admin'

, @rmtpassword='';

It is an access 2002 - 2003 format.

I keep getting

The Microsoft Jet database engine cannot open the file '\\Server_03\share\My_Test_Db.mdb'. It is already opened exclusively by another user, or you need permission to view its data

I checked and no one is using it. It had a password and I removed it. Of cource my preference is to keep the password because it's not my program.

What might I be missing?

Ken at work Hall of Fame Points: 3426 More actions · Answer 3

P.S. I've tried:

execute sp_helplinkedsrvlogin @rmtsrvname='My_Test'

Seems to work

Execute sp_tables 'My_Test'

Seems to work but no records are returned

Both of the following get the error in the above post.

Execute sp_columns_ex 'My_Test','tbl_Transactions'

Select * From OpenQuery(My_Test,' Select * From tbl_Transactions') AS vtbl_Transactions

Leju Geevarghese SSC Eights! Points: 982 More actions · Answer 4

May be this article would help

http://support.microsoft.com/kb/306269

---------------------------------------------------------------------------------------
It begins by taking the first step.

steveb. SSC-Forever Points: 46733 More actions · Answer 5

Does the server that you running SQL Server from have access to the correct filepath,

Can you browse to it from the server?

Ken at work Hall of Fame Points: 3426 More actions · Answer 6

Ken at work

Hall of Fame

Points: 3426

October 7, 2010 at 8:28 am

#1233623

yep,

Good idea though