autonumber problem

[megatronixs]

Hi all,

I had a backup from a table that I saved to excel. Somehow we lost all the records from the table 10 minutes later. There where gaps in the id numbers due to some delete records in the past, and when I did try to put it back in the access table the records some how shift. is there a way of still using the ID nr that is an autonumber in the table and when I set the data back from excel and use the append when paste that they show up correctly? Maybe I can set the autonumber to start from the last record on the table?
Any ideas are more than welcome.

Greetings.

 

[vbaInet]

Uncle G, I think this is what megatronixs is talking about.

Excel backup

Auto_ID
---------
... deleted ...
99
100
... deleted ...
... deleted ...
103

Import back into Access

Auto_ID
---------
1
2
3

megatronixs made a backup of the table and after an unfortunate event s/he is now trying to restore the backup to the table, but because the IDs are not in sequence when s/he imports back into the table the auto ID restarts from 1 and it's out of sync.

By the way you're up late.

 

[vbaInet]

That's one of the reasons why an auto ID field should be a meaningless field or don't auto increment and do it in code.

 

[vbaInet]

Soon I'll retire to bed.

(although I suppose I should leave the PC on and use chrome remote)

... saving a bit of energy

 

[megatronixs]

Hi vbaInet,

That is what happened (post nr 3).
For now I took out the auto number in the table and set back the data. all works fine now and correctly linked with the rest of the tables. The only thing is now I can't add new records from the import macro as the new records will not have incrementing numbers.
Any chance there is a way of setting back the auto number to start from the last id number?
The last number is 4367, and if auto number could start from 4368, that would be great.

To back up the data, I use excel to read the whole table and save it in excel file.
Is there an alternative to backup the records so i don't have the same problem in future?

Greetings.

 

[megatronixs]

Hi Gizmo,

No clue how to do this :-(
I just don't know much about queries.

Greetings.

 

[vbaInet]

Uncle G, the problem is that the table has no records, hence, no auto number IDs.

 

[vbaInet]

Why can't you append the new records to the table? This would sort out the auto numbering without any coding?

Just having a chit chat Uncle Gizmo Your suggestion will work.

 

[JLCantara]

Uncle G goes:

I'm lost.... I don't understand the question, therefore I don't understand the answer.

This issue is so poorly formulated that written in Katakana, it would not be more obscure.
The procedure seems to be wrong, updating records using Excel is strange (to be polite).

This site should have some procedure (or volunteer) to filter out that kind of post...

 

[megatronixs]

Hi all,

I was just saving the whole content of the table to excel file and later if needed I would put all the records back into the table. It worked fine a few times, till there where some records deleted and now when I try to put them back, the autonumber just mix up all and the records are shifted.
Now I know that I should have done it different, but I'm still learning and will be probably I will still make mistakes in the future.
A little help would be appriciated to be able to put back the records that I have now in excel.
When I put back the records using a append query, will it work with the gap of ID numbers?

Greetings.

 

[namliam]

Easiest solution is to insert dummy records to replace the deleted ones, use some data that is easily identified and uniquely identified so the records can be (re) deleted later.
Assuming you have the old autonumber available in excel, it should be quite easy to realize.

P.S. Backups should be made of the database, not of the tables...

P.P.S. another possible route, if your DB resides on a company server that has a backup strategy in place, you can request the access file to be restored from backup to a state before the deletion(s).

 

[megatronixs]

Hi Namliam,

After breackfast I got the same idea It silly I did not think of this 2 days ago.
I will do the trick with the dummy records and then delete them from the tables once all is set back correct. Lessons learnt form me. I will only back up the full database (back-end) so I will not have this in future.

Is there an option that it could be done from Access itself? around every 15 minutes or 30 minutes? This way it could be done automaticly.

Greetings.

 

[namliam]

No option from within access to backup itself, dont really see a need to backup every 15 minutes or even every hour.

Usually once a day is more than sufficiant, unless you are developing in which case it shouldnt contain critical production data.

You can build your own backup-ing database logic, however that will require quite some resources.

 

[megatronixs]

Hi Namliam,

People are making changes to records very often and that is why I wanted to keep an backup with fresh info so not to lose to much time if somenting breaks down.
I just now zip the back-end and keep it where no one can reach it every half hour or one hour. There is a second team member that does the same just in case on is sick.
Maybe not an elegant solution, but at least I don't have to go crazy to figure out what happened and then try to put back the data like last time I did.
I only need to develp the front-end, but from time to time I need to add some more fields to acomodate new things.

Greetings.

 

[RainLover]

The attached will do backups for you.

Just schedule it to run as required.

Delete the stuff I left behind once you figure out how to use it.

 

[ButtonMoon]

If your required recovery point is 15 minutes then use SQL Server or another DBMS with built-in, transactional backups. It's a whole lot easier than trying to achieve the same level of robustness with a Jet/ACE database.

 

[jdraw]

Is there an option that it could be done from Access itself? around every 15 minutes or 30 minutes? This way it could be done automaticly.

If this is critical to your application, then do as has been suggested-Move your backend to a server based dbms that has built in transaction processing. Why try to build your own when other systems have that built in, and you can still use your Access front end.

 

[megatronixs]

Hi all,

Thanks for the tips. We will move our database soon to Oracle and use access as a front-end to be able to add still functionality in an easier way, but till then, I still need to have a backup to avoid any lose of work.

Greetings.

 

[RainLover]

If this is critical to your application, then do as has been suggested-Move your backend to a server based dbms that has built in transaction processing. Why try to build your own when other systems have that built in, and you can still use your Access front end.

Also have you tested the Backup I posted.