Update Now() + 1 second

[karatelung]

I created an [EnteredOn] field to give a time stamp of when the record was added, and I plan on converting this field to the primary key. The problem is that I added this field after over 700 records were entered.

For the existing records, I want to run an update query that fills in the [EnteredOn] field with Now(). That's easy enough, but I want each record to increase by 1 second as it's updated so that each record has a unique time stamp. After that's done, the BeforeUpdate event will take care of any new records.

Any ideas?

Thank you.

 

[Rich]

Why would you use this field for a PK?

 

[karatelung]

I'm going to use [EnteredOn] and [ReferralId] as the primary key. An AutoNumber field is the current PK, but two staff members will be using notebook computers with a copy of the front end and backend. I'll be using an append query to append the new records to the back end on our server when they get back to the office. The AutoNumber will get messed up if I leave that field as the PK.

I tried the VPN route, but it was incredibly slow even with the front end only on the notebooks (notebooks connect via analog dial-up).

See my last post for more of an explanation:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=31099

I don't see another way to get this done quickly. The backend is an Access 2k mdb file.

 

[Rich]

Will replication not be a viable alternative?

 

[Alexandre]

If you are sure that your users wil never do anything else than adding new records, your method should be sufficient and replication an overkill.
In any other case (possible updates, deletions, etc.) you will HAVE TO look into replication.

 

[pcs]

karatelung,

just a thought, i don't like a 'time-stamp' as part of a PK.

it is not absolutely unique, as the server time can be re-set!

(unless your server is running a time-base...)

as i said, just a thought...

as to your original question re: existing records
if you created a table with 700+ records with an autonumber, you might be to join that to your update query, thereby adding one second to the time.

al

btw,
Rich, can you see other forum members out of that oscillating eyeball?

 

[Rich]

I like to keep an eye on one or two of the heavyweights

 

[karatelung]

Users will only be adding records, so I think I should stick with the append query. I don't want to use replication if I don't have to.

I don't really like the time-stamp as a PK, but if I make [EnteredOn] and [ReferralID] the PK, I'm sure it will be unique especially when you consider the fact that only two people will be entering data to one table.

Can anyone give me the SQL for the Update query that I mentioned in the top of this thread? I know how to update [EnteredOn] to Now(), but how do I get each record to have a unique time stamp using the update query.

I'm thinking it has something to do with datePart or maybe a loop to increase each [EnteredOn] by one second.

Thanks.

 

[Rich]

Function FillTable()
Dim DB As Database
Dim rst As Recordset
Dim StartTime As Date
Dim I As Integer, Period As Integer
StartTime = Now

Period = 700
For I = 1 To Period
Set DB = CurrentDb
Set rst = DB.OpenRecordset("YourTable")
rst.MoveFirst

rst.AddNew
rst!fldDte = (DateAdd("s", I, StartTime))
rst.Update
rst.MoveNext
Next I


rst.Close
Set rst = Nothing
DB.Close
Set DB = Nothing


End Function

 

[karatelung]

Thanks a lot. I only have one more question. I'm not sure where to place the function and how to call it to update the table. I'm used to working with form modules but haven't worked with functions.

Do I need to create a global module and put the function in there? Then how and from where do I call it?

I know this is the easy part, but I just don't know how to do it.

 

[Rich]

Put the code into a standard module, add a command button to an unbound form, put Call FillTable into the event procedure.
I would use a temp table to hold and check the dates before you update your existing records. Because the new table will be empty you'll have to put a dummy date in there first, delete it before using the update query.
HTH

 

[karatelung]

When I run FillTable, the debugger comes up with "DB As Database" highlighted.

The error message says:

Compile Error:
User-defined type not defined


"Database" does not appear in the pop-up list after typing "Dim DB As".

Any ideas?

 

[Rich]

I assume you have one of the newer versions, I believe it has to be Dim DB As DAO Database

 

[karatelung]

After I type "Dim DB As DAO Database" and try to move to the next line, I get:

Compile Error:
Expected: end of statement

I'm using Access 2k SR-1.