Solved

access linked tables conversion to MSSQL

Posted on 2009-07-15
5
338 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
  • 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Database restore 8 78
How to SQL Trace a SPECIFIC query 24 59
Update in Sql 7 30
Question about consuming GB from Comcast 5 37
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now