?
Solved

access linked tables conversion to MSSQL

Posted on 2009-07-15
5
Medium Priority
?
347 Views
Last Modified: 2012-05-07
Hello

I am trying to copy over my database links from MS access to MSSQL08. The links on access relate to DB links to a PROGRESS DB. I would like to keep the links on my MSSQL so that they update my MSSQL tables from the progress DB.

How can i do this?
0
Comment
Question by:acey1981
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24865984
Little bit confused about what you are trying to achieve.

Would you mind spelling it out a bit clearer ?  Not sure if you want to change Access, change Progress, Keep MS SQL and so forth. Maybe a "psuedo code" type example of a mapping as to how it is "now" and how you want it to be "after" and what the change is between "now" and "after" (e.g. upgrade MS SQL, or migrate to MS SQL etc).

If you can, then it might be worth adding a couple of other zones to your question (I can do that for you).

0
 

Author Comment

by:acey1981
ID: 24867111
Thanks for the response. I am trying to migrate access DB into SQL. But the access DB has linked tables to a progress database.

How would i transfer the links for my access db to MSSQL so that my tables auto update every night from info in the progress db.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 24875659
Right, now I am with you...

In SQL there are a couple of ways to get access to other databases.

1) Create a Linked Server (either using SQL Server Management Studio aka SSMS, or using stored procedure sp_addlinkedserver)
2) Create a query using OPENROWSET
3) Create a query using OPENDATASOURCE


options 2 and 3 are OK when there is a specific table / source involved, whereas Linked Server is better when there are a variety of tables involved.

Now, assume you do have a DSN or connection that works from Access, we will probably be using elements of that...

If starting from scratch would be using one of the ODBC drivers out there along the lines of  (an exrtemely verbose example using Merant drivers):


exec sp_addlinkedserver
@server='MY_LNKSVR',                                                        -- logical name and how you refer to it later on...
@SRVPRODUCT='MERANT 3.60 32-BIT PROGRESS',            -- name of product  (ie ole db product)
@provider='MSDASQL',                                                       -- MS ole db driver
@datasrc='MY_DSN',                                                          -- Your DSN
@provstr='DRIVER={MERANT 3.60 32-BIT PROGRESS};UID=;GST=0;SR=1;ASC=0;DBOS=Windows;DBPA=C:\MY_DB.db;DBAM=Direct;OIDH=MY_DB_NAME;OIDS=symixoi b;OIDP=TCP;DB=MY_DSN;DBPR=TCP;PWD=',  
@location='C:\MY_DB.db',                                                   -- Your database location
@catalog='MY_DSN'                                                            -- Your catalogue (really the DSN)


-- Now, you really should also add in the remote user logins / passwords

EXEC sp_addlinkedsrvlogin MY_LNKSVR, FALSE, NULL,'My_Progress_User',My_Progress_Password'


The minimum generic format for you to use with your existing connections would be like :

EXEC sp_addlinkedserver
@server = '<your linked server logical name>',
@srvproduct = '',
@provider = 'MSDASQL',
@provstr = '<connection string>'
@datasrc = '<DSN Name>'
GO

and then add in your logins sp_addlinkedsrvlogin

The above is using code in a Query window, and you can do the equivelent from SSMS

Now, once you have that in place, you can then test using :

EXEC sp_tables_ex MY_LNKSVR    -- note how it show show all the tables in that database - or if an error, then you need more work on your connection string.
GO

And to start using, it is just like any other table - just use the four part identifier....

SELECT * from MY_LNKSVR...MY_TABLE    -- four part identifier using table name from above
GO

Once the linked server is created, you do not have to re-create unless of course there are system changes that may affect that connection.


0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question