Unique number as text

[snuffi01]

I have an access database wich i sync over to pocket pc .
My problem is the common unique number problem since when a make an new entry in the pocket pc i get the same number as in the access database on the pc.
To overcome this problem i must have my autonumber field as an text field and the in my pocket pc i have the possibility to create an UID in that text field.
But in my access databse on my pc i dont know if its possible to have an textfield as an unique id.
Is it possible?

 

[BNPMike]

You can declare it as a primary key or you can put a unique index on it. I'm not entirely sure how that links in with your autonumber problem, though.

 

[Golom]

When you have an autonumber field in a table its field size may be Long Integer or ReplicationID. Long Integers will have the problem that you are experiencing (i.e. duplication of values on the different platforms and consequent problems when you attempt to sync the copies.)

If you select the ReplicationID field size then the Autonumbers generated on the different platforms are guaranteed to be unique and you can sync them without losing data.

 

[snuffi01]

My problem is that i must have the "Autofield" as an textfield.
Would be nice i anyone had an example of code to make this possible, for example in my forms put some code "on new

 

[Golom]

There's a little terminology to go over here. In Access, a field (variously called "Autonumber", "Counter" or "Identity") is a field type defined by the system and its value is determined by Access ... not by the user (i.e. you cannot set the value of an autonumber field.) An AutoNumber field can have one of two field sizes ... "Long Integer" or "ReplicationID" ... "Text" is not an option.

If you want a Unique field that is of type Text then you will have to create and generate the value yourself.

I'm not sure exactly why you "... must have the "Autofield" as an textfield ..." Do you have some processing that you intend to do with it? Does it serve some purpose other than to create a unique key value for a record?

 

[snuffi01]

The reasen that i want an text field as my unique filed is that my application in my pocket pc cant create an unique id as an number field but only as an text field.
So i have to have the field as an normal text (and as primary key)field in access and process the "unique id" by code when a make an new record.

 

[BNPMike]

Surely you've got much bigger problems than data format. You seem to have a computer and a PDA that are both updating a distributed, disconnected database. How do you propose to manage this? If you got both devices to use numbers you'd then find they would start to have clashes. They cannot allocate unique ids over a common range because - most of the time - they do not know what each other is doing.

I still don't understand why you are concerned about whether a field is number or text. These are dead easy to transform.

 

[snuffi01]

Since Pocket pc manage to sync an access database (compare database on the pocket pc with the one on the pc and add any new entries that is missing and change any entry thats have been changed), I think that it will work only if iget my Access database to have its primary key field as an text filed and by some code populate it so that it gets its uniquenes. (sorry for my bad spelling).
I have seen code examples on how to do an UID by code in this forum but all seems to be abaut numbers and not text...

 

[BNPMike]

Let me explain the problem. You start the day with one record. On your pc it is autonumber 1. On your pocket pc it is text '1'. During the day you add a new record on your pc. As it is autonumber it is 2. You add something else to your pocket pc and now it is '2'. When you come to sync them what is going to happen?. Do you expect the pc to allocate a new number 3 It can't allocate 2 - that's taken.
Then what process would turn the '2' to '3' on the pocket pc?

Worse than this, if you don't have a solid basis for the numbers then how does the pc know whether your pocket '2' is actually the same as the 1 or 2 on the pc?

The bottom line here is you can't use autonumbers in these situations because an autonumber is to do with one environment.

You must have a key that depends on the outside world. If say it were a vehicle registration then no matter whether this goes onto the pc, the pocket pc or both, the sync process can detect whether this an update or insert.

 

[snuffi01]

My ame is to have the access database to develop an text (user,date,time) witch is unique every time i make an new entry (press new).
In my pocket pc i have the databaseprogram to in the text filed develop an GUID (witch this program only can do in an text field) this GUID shold not be dublicated.
Then when pocket pc syncs with the access database it should work without be dublicated.
I know that access has GUID as an option, but this GUID cant sync with my pocket pc, so must have an textfield as the unique field.
Best regards Kristian