469,907 Members | 2,214 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,907 developers. It's quick & easy.

how to use alter table and identity

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
Nov 12 '05 #1
0 6039

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Heist | last post: by
2 posts views Thread by me | last post: by
7 posts views Thread by pb648174 | last post: by
7 posts views Thread by Serge Rielau | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.