Link Access tables to SQL server

Question

Link Access tables to SQL server

She Through Whom All Data Flows

Ten Centuries

Points: 1209
More actions
May 28, 2009 at 10:58 am

#221672

I know how to link SQL server tables to Access. That's not the issue.
I use data supplied by others that resides in Access2002 db's. I would like to be able to write queries within SQL2005 directly on these Access tables. I have a foggy idea that it's possible for SQL to link to Access db's, but my IT department has not been able to tell me how to do this. Specifically the say they don't the syntax required to make SQL server find the drives where Access resides.
The access db in question resides on a drive that, in windows, looks like:
workgrps on 'EMC-SNAS:T5.5.30.504 (evtwkgrp.tc.fluke.com)'
Right now I'm doing this Rube-Golderbergish work around where I
1. Have an Access fe that's linked to tables in my SQL server db, and tables in the foreigen Access db.
2. Use Access code to import the data from the foreign Access db to my linked SQL tables.
This means, of course, that I'm making a duplicate copy of all this data on SQL server, so that I can work with it on SQL server. Seems like surely there's a better way!
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]

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

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

Greg Snidow SSCoach Points: 16108 More actions · Answer 1

Look in BOL for 'linked servers' or 'OPENROWSET'.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Lowell SSC Guru Points: 323518 More actions · Answer 2

here's the syntax and example of adding a linked server for an access database, the login for it, viewing the list of tables, selecting from one, and finally updating one of the tablesw.

hope that helps.

--#################################################################################################

--Linked server Syntax for Access

--#################################################################################################

DECLARE @server sysname,

@srvproduct nvarchar(256),

@provider nvarchar(256),

@datasrc nvarchar(100),

@location nvarchar(100),

@provstr nvarchar(100),

@catalog sysname,

@sql varchar(1000)

--add an access Database as a linked server

SET @server = N'AccessDb' --an alias to call it by

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

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

SET @datasrc = N'C:\research\empty.mdb' --the path..if UNC like \\servername, the Account that runs SQL server must have the ability to browse to it, NOT the "logged in user

set @provstr = ''

EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr

-- exec sp_dropserver AccessDb

exec sp_addlinkedsrvlogin @rmtsrvname='AccessDb',

@useself='false',

@rmtuser='Admin',

@rmtpassword=''

--list all the tables and their names

EXEC sp_tables_ex 'AccessDb'

--assuming a table exists in the above linked server named "2006-1 UNLOCODE CodeList"

select * from Accessdb...[2006-1 UNLOCODE CodeList]

--update example.

update Accessdb...[2006-1 UNLOCODE CodeList]

set Name = upper(Name)

--insert example

INSERT INTO Accessdb...[2006-1 UNLOCODE CodeList](Name)

Select 'Bob'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

She Through Whom All Data Flows Ten Centuries Points: 1209 More actions · Answer 3

Thanks ever so much! I don't have permissions to add a linked server, so am forwarding this info on to my IT department. And trying to maintain my optimism.:hehe:

[font="Comic Sans MS"]She Through Whom All Data Flows[/font]

Lowell SSC Guru Points: 323518 More actions · Answer 4

if they are doing the setup, figure out which tables you will be using, and get them to add synonyms for those tables as well...it really makes your sqls cleaner:

IF OBJECT_ID('dbo.CriticalCustomers, 'SN') IS NOT NULL

DROP SYNONYM dbo.CriticalCustomers;

GO

CREATE SYNONYM dbo.CriticalCustomers FOR Accessdb...[Table Name With Spaces];

select * from Accessdb...[Table Name With Spaces]

select * from CriticalCustomers

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

She Through Whom All Data Flows Ten Centuries Points: 1209 More actions · Answer 5

Thanks again, I've added that to my request.

[font="Comic Sans MS"]She Through Whom All Data Flows[/font]

She Through Whom All Data Flows Ten Centuries Points: 1209 More actions · Answer 6

Here is the reply I got from my IT department:

"SQL 2005 x64 does not support the Jet driver needed to make the link server connection you are requesting. Therefore creating a linked server to accomplish your task is not possible. On the other hand you can create an SSIS package to export the data from your access database into a table in your pcamain database and then read your data from there. The SSIS package can be scheduled to run at whatever intervals you need it to run to keep the data fresh."

Does this make sense?

[font="Comic Sans MS"]She Through Whom All Data Flows[/font]