Solved

access linked tables conversion to MSSQL

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

I guess that all of us know that caching the data usually increase the performance, but I worried if all of us are aware about the risk that caching the data provides and how to minimize this.  That’s the reason why I decided to write this short art…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
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…

708 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

19 Experts available now in Live!

Get 1:1 Help Now