access linked tables conversion to MSSQL

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?
acey1981Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
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
acey1981Author Commented:
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
Mark WillsTopic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.