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.

 

[Uncle Gizmo]

I am Not sure I understand your question correctly so I will tell you what I get from your question and you will be able to correct me if I make a mistake. I assume that you moved some records from MS Access to Excel. These records had a reference number (an ID) I take it this was an auto-generated number, generated in MS Access, with a unique index in other words it was a list of unique numbers. You transferred the records in to Excel edited them and then move them back into MS Access. However you found that some of the records had been deleted in Excel so now there is no link between the record and your data in MS Access. A term occurred to me, for this (which might fit) "orphaned records". One thing you could try is instead of deleting the rows in Excel, add a column with a check box. When a record is deleted, instead of actually physically deleting the record, mark the checkbox indicating a deleted record. Now you will be able to move the records back and too between XL and MS Access without losing rows.

 

[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.

 

[Uncle Gizmo]

Uncle G,
By the way you're up late.

Not for much longer I'm going to go to bed as you should too I reckon as I think you and roughly the same timezone as me... Only trouble is I can now interact with the forum on my mobile phone through the Tapatalk app, so sometimes I'm in bed answering a question. (I can even moderate and ban users from the mobile!) Only trouble is I don't have access to Access from the phone (although I suppose I should leave the PC on and use chrome remote) not being able to double-check my responses in Access is forcing me to learn MS Access much better if you know what I mean.

 

[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.

 

[Uncle Gizmo]

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

 

[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.

 

[Uncle Gizmo]

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

I'm lost.... I don't understand the question, therefore I don't understand the answer. I'm not asking for clarification, I will move on and try and help someone else.

 

[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.