|
Subject:
|
Access to SQL Server 2000
|
|
Posted By:
|
jgferguson
|
Post Date:
|
11/30/2006 8:07:18 AM
|
Bit of an old chestnut this one probably but as I am new to SQL Server thought I would ask.
We have several access databases that share a number of linked tables between them. Is it possible to import just one of these Access mdb files to SQL Server and if so how will it affect the links between tables?
Alternatively is it better to rationalise all the Access mdb files into one big mdb file and then import (upsize) to SQL Server.
Your thoughts would be appreciated.
Thanks,
Jerry.
|
|
Reply By:
|
mateenmohd
|
Reply Date:
|
12/1/2006 8:22:25 AM
|
you can import access tables into sql server.
after that you have to re-create retationship/links ie. diagram
with the tables in sql server
Mateen
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/1/2006 8:31:08 AM
|
However, if you integrate the databases into one file, even with no data, and then upsize, SQL will recreate your relationships etc. Then you can upload the data using DTS from various sources.
The problem with moving only one is that you will need to move the database tables, then use the Link Table Manager to point to their new locations. Access may rename them dbo_tblName, in which case all your queries are hosed, etc.
I would consolidate and upsize. You may need to install DSN's on all your user's PCs, but you can launch a script to install the DSN when they open the database.
HTH
mmcdonal
|
|
Reply By:
|
jgferguson
|
Reply Date:
|
12/1/2006 11:52:30 AM
|
Thanks for the suggestions.
It gives me somewhere to start.
Cheers,
Jerry.
|
