How to Convert Random Autonumbers into Incremental Numbers?

[Heatshiver]

I am trying to think of a way to convert random autonumbers into incremental numbers. I am going to have a multi-user environment and want to make sure that when they sync their data that nothing is overwritten.

I have a field called Report Number set as a random autonumber. This will help eliminate syncing over the same reports. However, I need to have the numbers be incremental.

My thought is to write some VBA code that turns the random autonumber into the next number of the report.

Example: I have Report Number 3269483. I sync it and the VBA code tells itself that the last Report Number saved was 3 (after the conversion for the last random autonumber), so it now turns 3269483 into Report Number 4.

I have two thoughts on how this might work:

1) A string of code that somehow does this and first checks for the last Report Number and adds a +1 to it.

*2) Populate a list so that it starts from 1 and goes to 2147483647. The code then checks for the next number in the sequence and uses that for the Report Number. This may require a second field for the incremental number to be placed in.

Maybe DLast/DMax could help out here somehow?

If anyone could point me in the right direction or tell me what to look for, that would be awesome!

Thanks.

 

[spikepl]

I have read and re-read your story, and still do not get it. What has random autonumber to do with "eliminating syncing" and overwriting? I have a nasty suspicion that somewhere in there there is an assumption that a random autonumber generated locally for each user somehow guarantees uniqueness.

Try to step back and explain the entire setup in small bits of plain English.

 

[Heatshiver]

From what I have read about problems with syncing for a multi-user environment (that is not local) a random autonumber is a good way to avoid people from overwriting each other since the numbers should be completely different for their ID (Report Number). Although, it is feasible that two people could get the same random number, I was thinking that chances are slim.

I'm trying to figure out a way that two people could write different reports, sync them to an online database, and not have to worry about overwriting each other's data. I am imagining them downloading the front-end and the autonumber (Report Number) chooses the same number for both users.

If there is a method to have the server take the data and ensure that it will be listed as different Report Numbers, I suppose I could avoid random autonumbers altogether.

 

[spikepl]

What is "syncing"?

From what I understand a user generates some data (report) and an ID locally, and you want that report subsequently to be stored centrally, without any ID-collision? You can use a composite key. Some fixed unique UserID (for this given user) and Autonumber locally. In the centreal DB, the UserID and a field holding that user's and the report's autonumber, which would just be a Number field of type Long, and not autonumber.

 

[Heatshiver]

Thanks spikepl, one problem I have though is that one user login might be used by a few different people, so that may not work.

There will also be new people using this DB every so often, so I'd have to try to avoid adding new users since I won't be around.

The composite key idea may be of use! I essentially will have a Report Number, Date, and possibly a Country field. I would really like each Report Number to be different, but if you have any suggestions as to how to differentiate with a composite key it would be much appreciated.

Thanks for the quick response!

 

[spikepl]

You have to think this through. The unique-ID issue is bound to each local db, not to how many users use that particular db. So replace my suggested UserID with SiteID, and that should be it. Locally, a normal autonumber differentiates between each report. Centrally, SiteID and the imported autonumber do that.

A guranteed unique global identifier could be GUID, but I have never worked with those.

 

[Heatshiver]

So if I understand correctly I create a composite key of SiteID (userID) and an Autonumber for the local DB. The central DB takes the SiteID and the imported Autonumber as a number.

I guess what my brain can't wrap around is that I keep thinking that if two users download the db locally and fill out the data, how does the central DB know that the two reports are different if the Autonumber was at the same number? Or are you thinking that the SiteID will be unique for each of the two different users avoiding the problem of having the same Autonumber?

 

[spikepl]

If each local db has a unique SiteID, then a composite key consisting of SiteID and the local Autonumber is unique when presented in your central DB.

So you cannot have copies with the same SiteID floating around.

 

[Heatshiver]

I think I got it.

If I decide to use the SiteID and Autonumber, how do I automate a unique SiteID for each user who downloads the front-end?

Thanks for all the help.

 

[spikepl]

I dunno. If copies are not speifically issued but can be obtained freely, then you do have a problem. NIC (network interface card) have a MAC that is unique worlwide. A GUID is also unique. Both will be a pain in the butt to work with. Perhaps we are back to a random autonumber - but I don't know how that beast is seeded. With the same seed you'd get the same autonumber sequence.

 

[Heatshiver]

I had been reading about someone who suggested that using VBA with either DLast or DMax. The last record value would be looked up and then a +1 would be added to ensure that it was different and next in the sequence.

Would this be a possible alternative?

 

[spikepl]

Alternative to what exactly? In what exact context?

If you are importing data to your global DB, you can always create a new ID column , and assign it you own ID, and not care how that record was originally identified. I have no picture of the entire setup, so it's difficult to say anything clever. Is it a two-way street with data or just inserts from local to global?

 

[Heatshiver]

I apologize, I should explain a bit more.

Users will be able to not only put up data from their downloaded front-ends, but also by using the online interface.

There will multiple users from different locations around the world using the database.

I think your suggestion to create a new column with my own ID could work. I just want the numbers incremental so that they could be searched by in that order if need be. How would I set that up?

I mentioned the alternative as I was thinking if I wanted an incremental number that would not already be in use that DLast/DMax method sounded like it could help out here.

 

[spikepl]

I am no less confused than before. I asked whether this is a two-way data flow or not, and your answer does not clarify that. And now we have an "online interface" also so I am completely in doubt as to what goes on. Also you refer to "front end". A frontend is normally devoid of data, and all frontends would normally be linked to one common backend, and hence the entire issue of "local" vs global IDs would not arise.

I think you need to specify in more details what the entire setup is. Where is the data stored and when? Is there one backend? Do users work offline, so that their data needs to be inserted into the one common backend? Who adds data and who reviews data? Would a data-"adder" want to see other user's data, or is it a one-way flow?

I have to do my own work now - perhaps someone else can jump in.

 

[Heatshiver]

The database backend will be online. The database will be split.

People will be able to work offline and need to have their data inserted in the common backend.

Several people will be adding data as this is for oilfield hands in different countries, or it can be the same country but different rigs.

Users will be able to see other data already inserted. But we plan to have VBA code for permissions to see certain reports and whatnot depending on what user "level" they are given.

 

[RainLover]

I think you need to specify in more details what the entire setup is. Where is the data stored and when? Is there one backend? Do users work offline, so that their data needs to be inserted into the one common backend? Who adds data and who reviews data? Would a data-"adder" want to see other user's data, or is it a one-way flow?

This is where my thinking was going. Is this a split Database with the Back End in a Central Location.

If it is then this would make the whole issue mute.

So where is the Front End, and, where is the Back End.

How does one talk to each other to do this sync thing, and how does this sync work. There is more to this than just One Primary Key. What about all the other data. Where does this get stored.

Did you mention World Wide usage or am I confused. If it is then maybe Access is not the right product unless you have a fast connection. Perhaps something Web Based like PHP would be a better solution.

If you connection speed is good then you should be working on the server not locally. A separate copy of the Front end could be created as each user request one. But I digress.

Back to the original question. Where is the Front End and where is the Back End.

 

[Heatshiver]

Thanks for the help Rain.

The Back End will be on the server. I plan to use EQLData.com to host and they claim to have a fast server.

The Front End can be accessed online, or will be available to download in case an oilhand is somewhere where they do not have the Internet.

The "sync" is actually just the term I was using for uploading offline data to the online Back End. This is actually the problem since I am worried if someone has the same AutoNumber it will overwrite each other. EQLData recommended random AutoNumbers, but I still need to have a Report Number field that is incremental every time new data is uploaded.

I'm using Access because the oil industry relies primarily on Microsoft Office with Windows, so they wouldn't need additional software to use the database offline.

It will be worldwide (only select countries at the moment), but I think they wanted to start off with maybe 30 users or so.

 

[RainLover]

How can you work Offline without a Back End.

Does every user have their own copy of the Front End for online use and is this Front End connected to the Back End on the server.

 

[RainLover]

I plan to use EQLData.com to host

From this statement you have not yet started testing. I looked at the Site.

Shame you did not mention this earlier.

It appears that you are looking for a solution to a problem that may not exist.

Why don't you start testing and if you have problems then refer it to the Vendor..

 

[Heatshiver]

I actually sent them an email first as I didn't want to start the DB and have to redo a bunch of items if I ran into a problem like this. I have started the DB, but this was an issue that I couldn't figure out how to solve.

I had asked them about the "sync" problem and they said there would be a problem with this type of situation and to use random AutoNumbers.

From what their site states you can use a Front End and their plugin will "sync" the data to the tables hosted on their server. I could give out the entire DB but this will still run the risk of people putting in the same numbered reports and uploading to the server.