Problem linking

[Grant Baxter]

Hello,

I'm still trying to move my friend's Access app from an old computer
running Windows 98 to a new computer running XP.

The application as it's running now is an mde file. The simplest thing
would be to move the mde file from the Win98 machine to the XP machine
an carry on. However, in XP the printer driver is different from the
Win 98 driver. So the margin values that were set in Win98 don't print
properly on XP. I've discovered that print margin values set in an mde
file don't stick from session to session. In the mdb backup, print
margins can be set and made to stick.

I asked a couple questions before about this and was told to get the
mde file and split the data out to a back-end file. Then link the old
backup mdb file to that back-end file. I've looked at the tables and
linkage, and the values in the backup mdb file are indeed from the mde
file back-end. However, when I try to add an order to the database,
the auto-numbering puts an order number on the new order form that is
one more than the value in the old Order Detail Table. IOW - the next
order number in the mdb file is 27000. The next order number in the
mde file is 29000. When I try to add an order to the original mdb file
that is linked to the mde Order Detail Table, it increments to 27000
instead of 29000. How can I fix this so that the next incremented
number is 29000 like it's supposed to be. (Also - is it likely that
there are other functions that don't "line up" the way they're
supposed to.)

I hope I've offered enough info to understand the problem, however, if
you need more info, ask.

I would appreciate any help you folks may offer.

(BTW - I'm not an Access programmer, I'm just trying to do my buddy a
favor. I thought it would be a simple enough thing to accomplish,
however it's turned into a real problem.)

TIA,

grant

 

[Jerry Whittle]

Is the order number field an Autonumber datatype? If so the only thing an
autonumber is guaranteed to be is unique and that's only if the field is also
a unique constraint or primary key. You just can not assume any proper
incrementing of numbers with Autonumbers.

The proper way to increment numbers in Access is to do something like a DMax
+ 1 of the order number field. This only works well when the orders are only
entered by one person at a time.

If you have a multi-user system, you'll have to do something like the DMax
thing at the instant the record is saved or even create another table to
select records from.

I guess that you could create about 2000 bogus order number records then
delete them. That would be the numbers back on track.

 

[Grant Baxter]

Is the order number field an Autonumber datatype? If so the only thing an
autonumber is guaranteed to be is unique and that's only if the field is also
a unique constraint or primary key. You just can not assume any proper
incrementing of numbers with Autonumbers.

Yes, it is an "autonumber datatype" from what I can tell. The word
"autonumber" appears in the Order Number field when I try to enter a
new order.

It sounds like this is something even the original DBA could not fix,
am I right?

Thanks,

grant

 

[Jerry Whittle]

Often Access autonumber's will increment just fine until something like your
situation happens so the original DBA might not have known that it could
become a problem, or at the time the need for precise incrementing wasn't a
requirement.

 

[Grant Baxter]

Is the order number field an Autonumber datatype? If so the only thing an
autonumber is guaranteed to be is unique and that's only if the field is also
a unique constraint or primary key. You just can not assume any proper
incrementing of numbers with Autonumbers.

The proper way to increment numbers in Access is to do something like a DMax
+ 1 of the order number field. This only works well when the orders are only
entered by one person at a time.

If you have a multi-user system, you'll have to do something like the DMax
thing at the instant the record is saved or even create another table to
select records from.

I guess that you could create about 2000 bogus order number records then
delete them. That would be the numbers back on track.

Jerry,

Here's how I think I can solve the auto numbering problem. (Since the
original DBA is not cooperating in helping to get this corrected.)

I've tried this method and it seems to work just fine. Do you see/know
of any gotchas that I may run into down the road? (Besides having to
do this all over again for the next move.)

I plan on putting this web page in its entirety in the database folder
so that if it's not me, the next person will know how to fix this
again.

http://support.microsoft.com/kb/94821/en-us

TIA,

grant

P.S. Please note that this application has not changed in over 7
years, and there is very little chance that it will need to be changed
for the next 7 years. So I feel that this fix, although it may be seen
as a kludge, is probably a good fix for my friend's needs.