Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 30th, 2006, 08:07 AM
Registered User
 
Join Date: Nov 2006
Location: Glasgow, , United Kingdom.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access to SQL Server 2000

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 With Quote
  #2 (permalink)  
Old December 1st, 2006, 08:22 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 With Quote
  #3 (permalink)  
Old December 1st, 2006, 08:31 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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 With Quote
  #4 (permalink)  
Old December 1st, 2006, 11:52 AM
Registered User
 
Join Date: Nov 2006
Location: Glasgow, , United Kingdom.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the suggestions.

It gives me somewhere to start.

Cheers,
Jerry.


Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL 2000 SERVER+ACCESS ADP DLOOKUP MHONG Access ASP 1 October 25th, 2005 08:36 AM
Access 2000 "Connect to SQL Server" cjdphlx Access VBA 1 July 17th, 2005 02:09 AM
looking for access 2000 to sql server 2000 sql/que method SQL Server 2000 0 July 7th, 2005 12:46 PM
SQL SERVER 2000 AND ACCESS 2000 ckentebe SQL Server 2000 3 June 17th, 2004 08:50 PM
Migrate Access XP to SQL Server 2000 Walden Access 1 September 21st, 2003 01:54 AM



All times are GMT -4. The time now is 06:07 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.