More of a SQL question ...

[New2VB]

Hi all,

I know this may be more of a SQL question than an Access question but I was hoping that one of the gurus here may have an answer.

Background: SQL 2005 Express with Access 2003 F/E

In order to re-develop my Access FE I did a backup of my always-on B/E, installed SQL2005 Exp. on a second workstation and restored the database there.

Obviously during the redevelopment a months worth of new records were added to the original 'base (lets call it db1) while new tables were added to the second db, some columns were added to the second db' tables and some columns were removed from the second db's tables. I think you can see where I am going with this.

I would like to now add the records from db1 to the structure of db2...

I believe the way to do this is through DTS although that can get complicated where you have similar but not identical tables/columns.

To further complicate issues, the 'bases both have the same names so (after renaming either of them and attempting a backup/restore in either direction) I cannot attach either database to either instance because due to name duplication. I was trying a program called SQLMerger but I think it is openeing the same mdf/ldf files as the tables look identical but I know they are not.

Iwas also thinking of doing an old-fashion Access export/import routine but that seems a long way round of doing things.

Please could anybody give me any ideas, or tell me how to rename db1 to that I can attach it to db2's instance?

 

[HiTechCoach]

Here is an idea for you:

Are both database on the same lan?

if they are, I woudl make a new front end and linked to the tbales in both SQL databases at the same time. Now you can run append queries to copy the records.

 

[New2VB]

Hi HiTech,

No they are not on the same LAN/Domain.

Also, I can't rename them (having tried using Studio Express). I have tried doing a backup and restoring under a different name but I still can't attach the 'base as SQLExpress recognises that they have the same name.

I had thought about creating a second instance and "importing" the dbase into that then getting Access to open both dbases at the same time but then (I think) I will lose all my F/E forms, queries etc. I can live with losing the first db's forms etc. after having made a copy of it but I am unsure whether I can run queries against 2 databases in different SQL instances.

 

[New2VB]

Problem solved...probably not the right way to do it but I installed a second instance of SQL Express on the same machine, restored a backup of the first db, created it as an ODBC source and opened it in Access. From there I could copy and paste to my hearts content.

 

[HiTechCoach]

Problem solved...probably not the right way to do it but I installed a second instance of SQL Express on the same machine, restored a backup of the first db, created it as an ODBC source and opened it in Access. From there I could copy and paste to my hearts content.

Glad you figure out have a working solution.

Thanks for sharing yoiur solution.

 

[Banana]

FWIW, to restore a backup, you can specify that it go into a new database.

On the Restore Database dialog there is a field "To a new database:" where you'd enter the name (e.g. "db1_copy"). Then click on Options in the left hand pane to go to the 2nd page of the dialog - that's where you define the filenames for the .mdf and .ldf. You definitely need to rename those so they'll be also distinct. Once you've done that, then you can restore a copy of database into a new database without need for a 2nd instance and do whatever you need.

 

[HiTechCoach]

FWIW, to restore a backup, you can specify that it go into a new database.

On the Restore Database dialog there is a field "To a new database:" where you'd enter the name (e.g. "db1_copy"). Then click on Options in the left hand pane to go to the 2nd page of the dialog - that's where you define the filenames for the .mdf and .ldf. You definitely need to rename those so they'll be also distinct. Once you've done that, then you can restore a copy of database into a new database without need for a 2nd instance and do whatever you need.

Great tip. Thanks for sharing!