Linked Server to Access - 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

Transact-SQL (2005)

Linked Server to Access

Author

Topic

mlevier
Starting Member

33 Posts

Posted - 2007-09-05 : 15:37:31

This one I am really stuck on and it sounds so simple. I have been able to create a linked server to two different SQL Servers and an Oracle Server however, I cannot connect to Access. I have tried everying.

sp_addlinkedserver 'DataCapture', 'Access 2003', 'Microsoft.Jet.OLEDB.4.0', '\\Server\DatabaseName.mdb'

and

exec sp_addlinkedsrvlogin 'DataCapture', true, null, null, null

I don't have any special permission on my access table. The whole goal of this is to import data from the Access database on a daily basis. I am using SQL Server 2005 for this. Any ideas would be helpful.

Thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-05 : 15:41:41

for hard to setup linked servers on different engines i guess it's easier if you can setup an odbc connection first then use that odbc object in your linked server

--------------------
keeping it simple...

mlevier
Starting Member

33 Posts

Posted - 2007-09-05 : 15:46:25

I already have it setup but how do I use it in my Linked Server?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-05 : 15:50:28

try to use the wizard so you have a guide on where and what to put into the textboxes when presented

I don't do this often so the steps are buried deep within my consciousness

HTH

--------------------
keeping it simple...

mlevier
Starting Member

33 Posts

Posted - 2007-09-05 : 15:54:28

The provider would be Microsoft Jet 4.0 OLE DB Provider, product name: access, Data Source would be the ODBC connection, Provider String:?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-05 : 16:01:42

http://snippets.dzone.com/posts/show/3731

--------------------
keeping it simple...

mlevier
Starting Member

33 Posts

Posted - 2007-09-05 : 16:13:31

It still doesn't work.

I knowe it is simple but IT JUST WON"T WORK.

I FEEL SO STUPID!

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-05 : 22:16:11

Which part didn't work? Did you get any error? Why don't use data import wizard to load data from access?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-09-05 : 22:25:22

Go through each line and Modify where it's noted then run.



EXEC sp_addlinkedserver 
	@server = N'DataCapture',
	@provider = N'Microsoft.Jet.OLEDB.4.0', 
	@srvproduct = N'OLE DB Provider for Jet',
	@datasrc = N'\\Server\DatabaseName.mdb' --CHANGE TO yours

/*Make sure the account that is running SQL has access to the mdb file location.*/
GO
-- Set up login mapping using current user's security context
EXEC sp_addlinkedsrvlogin 
	@rmtsrvname = N'DataCapture',
	@useself = N'TRUE',
	@locallogin = NULL, 
	@rmtuser = N'Admin',
	@rmtpassword =  NULL 
GO
-- List the tables on the linked server
EXEC sp_tables_ex N'DataCapture' --This will confirm it's working
GO

-- Select all the rows from a table
SELECT * FROM 
Datacapture...MyTable 

--Change Mytable to a table name from your access file

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources