Hi, I'm not familiar at access and at VBA either, so I need some help
or at least some hint please.
I have table "availabledrivers", coming from a query to the table
"drivers". Then I have a table "availablebus" coming from a query to
"bus".
Bus and Drivers have autonumber ID, so the tables I get have the IDs
screwed up..I mean..there are "holes" in them..it's like 1,2,5,7,11,20
etc
Now..I need to link the 2 resulting table...and I have to do it daily.
First record of "avalablebus" linked to first record of
"availablebus", second record of the first table, with second record
of the second one, etc etc until the last available bus. Now..to solve
this problem, I append the "availablebus" and availabledrivers" to
other 2 tables ("uppendBus" and "uppendDrivers"), created before,
having each an own autonumber ID. Then I link the fields with those
autnumber ID. Fine.
My problem shows up now. I have to delete all the records daily and re
run all the queries...but of course the autonumber IDs are going to go
on from the last number, instead of restarting from 1. So..I have to
make a module or something to reset it to 1, with step 1.
I'm supposed to use this code for both of them..for example
CurrentProject.Connection.Execute "ALTER TABLE uppendBus ALTER COLUMN
busID IDENTITY (1,1)"
and
CurrentProject.Connection.Execute "ALTER TABLE uppendDrivers ALTER
COLUMN driversID IDENTITY (1,1)"
My questions are:
what's the code of the whole routine? Should it be a private or public
one? Should I run this routine with a botton or a macro?...
I know what I have to do...but not HOW to do it ...
Thanks in advance
Gianfranco