By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

how to use alter table and identity

P: n/a
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
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.