Autonumber after splitting database

[asasak]

I have a split database, one on the desktop and other out on the network. My tables are autonumberd to keep the records in a particular sequence. I also created queries one to delete and one to update the tables. After deleting the data in a table I compact and then do an update. This procedure worked just fine until I split the database. Why doesn't access compact the linked/split tables. Can I compact the linked tables without having to do this twice, once on my harddrive and once out on the net. Thanks in advance

 

[SgtJarrow]

First, the reason:

You have a split database....I am going to assume the split includes all tables in the backend on the network and all query, forms, reports on your local end. When you do a compact, it does not compact the linked tables.

Second, a solution:

You can set up a function to do this....you would need to create a connection to the remote database, compact it, then compact the current database.....it would not be easy code, but if you want an example, send me an email and I will forward you something I wrote that includes all the parts....you will just have to put them together in the order you need them to run...

THIRD AND MOST IMPORTANTLY, THE REASON NOT TO DO THIS:

Search Tek-Tips, for autonumber out of sequence, or rolling back autonumber, or something like this....you will find MANY threads on this topic alread have been discussed....The short and the sweet of it is that autonumber should almost NEVER be used for any piece of data that is relevant. It is merely a unique identifier...you you need to enforce sequential numbering or something, create your own function to provide the numbering system....it is COMPLETELY IRRELEVANT if an autonumber is missing numbers, starts at 23421, or whatever.....it should not matter to your data at all..... Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[asasak]

Okay, well that answers that question.

The reason I went to autonumber is because I am pulling in data from a mainframe that stores data by a unique sequence code (alpha/numeric) to keep track of aircraft configurations. Without the autonumber, I could not keep the file in a top down sequence. There are 10 tables that are sorted for lack of a better method by the autonumber instead of the unique alpha/num code already stored on the mainframe. I need to update the database twice weekly and have tried to automate most of the effort. I was looking for a simple solution.

Thanks for your insight

 

[JeremyNYC]

Whoa! I think there may be a misunderstanding here. What Robert means is that autonumbers shouldn't be taken to imply anything other than uniqueness and sequence in which the records were created (in this database, of course). Your autonumbers will still tell you the order in which your records were added.

Basically, you have to compact the back end separately from the front end because the back end is separate from the front end <g>. Really, they're just two different files, so you have to compact them separately.

You can write code to do the backup of the backend from within the front end.

In fact, in most installations, there's no need to compact the front end, as users most often don't make changes to the objects, and the data changes happen in the back end. It's the compacting of the back end that's most important.

I have code to do that, but not here. I'm sure you can find a bunch of code on the web, but if you don't put a note up on this thread and I'll ty to get you the code I use (though it's pretty complex, as I use it for a pretty big application).

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.