Solved

access linked tables conversion to MSSQL

Posted on 2009-07-15
5
344 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 500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

728 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