The classical mistake...

[Petros]

I started to use Access 2003 (currently 2007) as an in-house and local tool for 12 users, believing that a large multinational company would not adopt this tool globally...
But the company/department did.. J . Access has proved its value (a small business solution in a large corporate environment).
Currently i am migrating to SQL, and i intend to merge our local application with the one overseas..Access 2007 will remain the front-end.
Each file-record uses the Primary-key (Autonumber) as a file “number”..and of course i ended up merging two databases with “duplicate” file numbers..yes i know it was stupid..
How to proceed? .. i want to assign the “foreign” records with a “prefix”
Example: Record number 5..should read 05..but since this is an autonumber...

Thankful for all suggestions

 

[Petros]

,,and yes.. i will not use the autonumber as a "public" identifier in the future... .. I learn..

 

[Simon_MT]

I would insert Company ([Long] Integer) into your Tables and assign these to the different entities. This may not be a true reflection but it does give the opportunity to be more flexible. I would then look at the issue of duplications.

Simon

 

[gemma-the-husky]

Couple of things

a) you may have problems merging data. You can probably massage the files for one of the datasets by adding a new value that wont clash, and then updating all the autonumbers -

but then, when you merge the data you will probably find duplicates values for certian of the company entities.

b) you may still not get adequate performance for the remote location, even under SQL. its worth testing this to see. before trying to merge the dbs's anyway.
- although you can probably run the remote one via rdp login/ vpn login etc

if not, it may be better to retain two quick systems.

 

[Petros]

Couple of things

a) you may have problems merging data. You can probably massage the files for one of the datasets by adding a new value that wont clash, and then updating all the autonumbers -

but then, when you merge the data you will probably find duplicates values for certian of the company entities.

b) you may still not get adequate performance for the remote location, even under SQL. its worth testing this to see. before trying to merge the dbs's anyway.
- although you can probably run the remote one via rdp login/ vpn login etc

if not, it may be better to retain two quick systems.

Your words are very true, i have conducted tests overseas (one Continent) and received a positive reaction, however, connecting Continent 2, may return performance issues. The current solution, step 1, is to simply place the tables on the SQL, dramatic increase in performance over VPN, perhaps the best solution are two quick systems or deserting the JET engine..placing the queries on the SQL as well..time will show..

Thank you for your feedback!