I have updated a database I created with additional reports, some UI
enhancements, etc., but have not changed anything about the table structure, field types, etc. The database has been in use for about a week by the user and I was working on my local copy. How do I provide the updated database to the user without incurring key violations on the auto-number PKs when their data is imported into the new version?

First Key Violation has a reason and its not just being difficult

You have taken a local copy and updated it. You now have 2 applications with different data. Which one is correct ?

Duplicated data is always a problem and the only way out is not to duplicate.

You really have no choice but to establish which is the "Real" application and update those records which are now out of date. Probably manually.

Alternatively
remove all PK's, Index's and relationships. Add all records into one application. Check for duplicate candidate PK's . Remove erroneous records, re-instate PK's indexs and relationships and hope you got it right

And do not duplicate again

Good luck

L

I don't quite understand the problem -

but if you have a split Front End and Back End most of this would be solved wouldn't it.

[QUOTE=Len Boorman;587849]"First Key Violation has a reason and its not just being difficult"
-----
Yes, I understand the reason; had I anticipated further work on the db, and known of the capability to split data from application, I would have split it before turning it over. I also might have opted for semantic rather than autonumber surrogate PKs. For example, something based on the item serial number concatenated with the service tag number, which, since service tag numbers are not reused, would have obviated the possibility of duplication between datasets.

The argument between proponents of surrogate versus semantic PKs rages on (well, maybe sizzles is more accurate), but I'm agnostic on the subject, and in this case I think coming down on the side of semantic PKs would have been advantageous.
-----
"You have taken a local copy and updated it. You now have 2 applications with different data. Which one is correct ?"
-----
The user's, of course. All the data in mine is dummy data which can be disposed, but not until development is finished, as some of it is designed specifically to test certain functionality (30-60-90 day, 6-month, 12-month warranty and license expirations, for example)
-----
"Duplicated data is always a problem and the only way out is not to duplicate.

You really have no choice but to establish which is the "Real" application and update those records which are now out of date. Probably manually."
-----
The real application will eventually be the updated version I am currently working on. When I get the changes I am working on now implemented and tested I will ditch the dummy data and import the user's data, then split the db so any further modifications will not entail this issue.

When I posted the question I was hoping that someone might have at one time written some code to, perhaps, reset the two main tables' PKs to a range beyond that likely to exist in the imported data, or something along those lines. If I knew more about the Access object model and Access VBA I would do it myself.
-----
"Alternatively
remove all PK's, Index's and relationships. Add all records into one application. Check for duplicate candidate PK's . Remove erroneous records, re-instate PK's indexs and relationships and hope you got it right

And do not duplicate again"
-----
Yes, sir. Right away, sir. I shall go forth and sin no more.

FWIW, this is my first Access application, but not my first database application. There are eleven related tables with two "primary" tables (hardware and software), and the db was carefully normalized to 3NF. I am an MCP in VB and have heretofore built applications using mdb files with Visual Basic and either DAO or ADO. In such applications, the application and the data are naturally "split," and work on the code end can proceed without these concerns. Had I more experience with Access and realized when I started this that the data could be split from the app in Access I would have done so before turning it over. I didn't, however, hence the reason for my question.

Scott

I don't quite understand the problem -

but if you have a split Front End and Back End most of this would be solved wouldn't it.

Yep, you got it! :)

The operative word there is "if." I wasn't aware that this capability existed in Access, as this is my first Access app (although far from my first db app). When working with data in Visual Basic the code is naturally split from the data. Now that I know, of course, as soon as I get the current issue resolved I will split the db and go forward.

20-20 hindsight...

Scott

Just split the clients data as is - and then link the new FE that you have done?

Was not lecturing in my post so hope you received in the manner in which it was intended

Insight into FE and BE already posted

Also possible of course to develop all the functionality in one application and then in the User application import all the forms, queries, reports etc from your development application

Even with split FE and BE I do mods on a trial FE and then import fresh set of objects into the real FE. All except tables of course

HTH

L

Also possible of course to develop all the functionality in one application and then in the User application import all the forms, queries, reports etc from your development application


Thats not a bad idea either!

Was not lecturing in my post so hope you received in the manner in which it was intended
Well, I have to admit that in reading it I got the feeling that I was receiving a "Tsk, tsk, bad little boy, you should know better, don't do it again" scolding.:)
Insight into FE and BE already posted

Also possible of course to develop all the functionality in one application and then in the User application import all the forms, queries, reports etc from your development application

Even with split FE and BE I do mods on a trial FE and then import fresh set of objects into the real FE. All except tables of course
Yes, now that I am becoming more familiar with Access itself (rather than with simply the mdb format) I am seeing that there are many alternative methods to address this issue. For the time being, at least, I'm going with splitting both versions, then linking the active tables from the user's BE to the updated FE I'm developing. Going forward from here, of course, his data will already be independent of the application.

HTH
L
It has; no offense taken. Thanks.

Scott

Good to have helped.

L