Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-12-24 : 09:35:18
|
| ramesh submitted "I have a database in sql server with more than 150 tables. Now, i exported the whole of the database in to MS-Access. But all the constraints or relationships existing between sql tables are lost after export. Now, i want, can we export databse from sql server to ms-access without losing the constraints or relations between tables. Can anybody help me??" |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-12-24 : 16:22:53
|
| DTS does not retain these. But why are you going this direction? It seems backwards to me. Usually I'm helping people get out of Access and into SQL Server. If it's a matter of needing a free or distributable database, you might want to look into MSDE instead. It's essentially a free and distributable version of SQL Server.--------------------------------------------------------Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url] |
 |
|
|
mrameshchandra
Starting Member
4 Posts |
Posted - 2003-12-27 : 02:46:39
|
| Thanx for the reply. I wanted the database to distributed along with a product. So, is it possible to include MSDE database along with windows installer or Package and Deployment Wizard, so that no manual configuration at the client site is required?ramesh chandra |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-12-28 : 03:22:50
|
With Windows Installer yes, but with the P&D wizard...I'd say its a tough job  Check out the official site for more information: http://www.microsoft.com/sql/msde/OS |
|
|
|
erici
Starting Member
1 Post |
Posted - 2004-01-09 : 05:03:44
|
| If you want to export data from SQL to an Access MDB file, I would suggest creating the Access MDB file with the relationships BEFORE creating the DTS package, then just specify that MDB file as the destination. The trick with this method is that the order of the transfers then makes a big difference. If you just auto-generate a DTS package that copies data from SQL to Access you'll get errors due to foriegn key constraints (most likely.) If you do this anyway and afterwards tweak the DTS package to specify workflow to control which tables get transfered first and which are dependent on the success of others, you should be fine. You'll also want remove the data from Access before the DTS transfer and to compact the MDB file after the transfer. This is probably easiest done by adding script to the DTS package that first copies a blank version of the MDB file over the top of the destination MDB file, then performs all the DTS transfers, then calls on JET to compact the MDB file from another script after all the transfers complete.I hope that helps!-Eric I |
|
|
|
|
|
|
|
|
Export from sql server to MS-Access