By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,767 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,767 IT Pros & Developers. It's quick & easy.

Linked tables in SQL Sever-ODBC connection

P: 4
Newbie to SQL Sever here.

In access it is very easy to set up linked tables using ODBC, the behavior of which is needed to replicate in sql server.

In SQL Server (2008 in this case), I found it easy to import using OBDC using the import wizard. What is not straightforward is either: 1. automating the actions if the import wizard or 2. creating linked tables similar to access. We need auto-refresh of the data rather than a manual update.

I have found articles dealing with how to link databases--which might also work-- but they all use a query analyzer feature that I haven't used before nor does 2008 seem to have this feature anyhow.

It seems there are a number of ways to solve this issue and that it should be simple as it is very much so in MS access. What am I missing?

Thank you,
John
Jan 25 '10 #1
Share this Question
Share on Google+
1 Reply


P: 3
If you are on SQL 2008 these should work for you. Be sure to enable ad-hoc distributed queries. This brings in the entire DB, and you just query the tables you want. If this is not what your after, can you provide more info?

These are found at http://msdn.microsoft.com/en-us/library/ms190479.aspx

-LD

Native Client --

EXEC sp_addlinkedserver
@server='S1_instance1',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='S1\instance1'

OLE to Access--

EXEC sp_addlinkedserver
@server = 'SEATTLE Mktg',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\MSOffice\Access\Samples\Northwind.mdb'
GO

ACE--

EXEC sp_addlinkedserver
@server = 'SEATTLE Mktg',
@provider = Microsoft.ACE.OLEDB.12.0',
@srvproduct = 'OLE DB Provider for ACE',
@datasrc = 'C:\MSOffice\Access\Samples\Northwind.accdb'
GO
Jan 31 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.