Migrating MS Access to MS SQL and merging with existing db

17th March 2006, 11:34

I realise this might not be entirely the right forum, but I'm kinda short on ideas.

This is my current situation:

There are 2 programs and 2 databases running, one on MSSQL 2000, another on MS Access 2002. They dont have any shared subjects except one: members.

The idea is to insert the MS Access DB into SQL, while keeping most of the logic on clientside. Of course, both member db's dont have the same structure (my life isn't quite that easy). Yet I still have to merge them & guarantee the functionality of both programs without having to modify the programs code.

The main problems (that I can identify at least) are these:
- Sex is defined in SQL by a bit (1 = male, 0 = female), in Access it's defined as text (M = male, V = female).
- The SQL links postalcode & city to a different table, using PCid as a key, linking to a table containing PCid, code & city. This is not the case in the Access db, the postalcode and city are part of the table and simply defined as text.

The possible solutions I can think of are:
- Run 2 SQL db's (making the Access db easy to export) & let em synchronise member tables by using a trigger that listens for updates (or something like that).
- Merge them into a single db & modify a program to accept the table structure & properties of the other. (I doubt I can get hold of the full source codes, but I can try. This option probably would require too much work & time, which I don't have.)
- Merge them into a single db & use a special db-link driver that converts the tables to look like their old configuration. (Is this even possible? Don't know, but it sounds plausable

)

Any help or comment on this subject are highly appreciated. I'm just a student that has to do this for his internship. I'm by no means a real programmer or database expert.

17th March 2006, 11:34	#1
SuAside Posts: n/a	Migrating MS Access to MS SQL and merging with existing db I realise this might not be entirely the right forum, but I'm kinda short on ideas. This is my current situation: There are 2 programs and 2 databases running, one on MSSQL 2000, another on MS Access 2002. They dont have any shared subjects except one: members. The idea is to insert the MS Access DB into SQL, while keeping most of the logic on clientside. Of course, both member db's dont have the same structure (my life isn't quite that easy). Yet I still have to merge them & guarantee the functionality of both programs without having to modify the programs code. The main problems (that I can identify at least) are these: - Sex is defined in SQL by a bit (1 = male, 0 = female), in Access it's defined as text (M = male, V = female). - The SQL links postalcode & city to a different table, using PCid as a key, linking to a table containing PCid, code & city. This is not the case in the Access db, the postalcode and city are part of the table and simply defined as text. The possible solutions I can think of are: - Run 2 SQL db's (making the Access db easy to export) & let em synchronise member tables by using a trigger that listens for updates (or something like that). - Merge them into a single db & modify a program to accept the table structure & properties of the other. (I doubt I can get hold of the full source codes, but I can try. This option probably would require too much work & time, which I don't have.) - Merge them into a single db & use a special db-link driver that converts the tables to look like their old configuration. (Is this even possible? Don't know, but it sounds plausable ) Any help or comment on this subject are highly appreciated. I'm just a student that has to do this for his internship. I'm by no means a real programmer or database expert.