| Author |
Message |
|
KW
#1 / 8
|
 not sure where to begin I've programmed in VB 6.0 quite a bit, but am not quite sure where to start for my little project. Any suggestions or help would be greatly appreciated. basically what I want to do is...
I have 2 tables, tied (one Arch_no for many Serial_no) by an Arch_No.
Arch_no is the primary key of one table. Right now this number is generated
by adding one to the largest number in that field of the database. What I
would like to do is make the Arch_no an autonumber. How do I go about
changing this number in both tables? If I change it in the main table, it
won't tie to the records it should in the table it is tied to. I would like
to do this programmatically if possible. The database consists of 14000
records and I would hate to have to change each one
excuse the cross posts
Thanks
KW
|
| Sun, 30 May 2004 03:43:59 GMT |
|
 |
|
Dirk Goldga
#2 / 8
|
not sure where to begin Here's a procedure to follow: 1. Backup your database!
2. Copy both tables, structure and data, to new "work" tables. Suppose
your original tables are called "tblArch" and "tblSerial", and your work
tables are called "tblArch_WORK" and "tblSerial_WORK". Make sure these
tables have all the data.
3. Delete all records in tblArch and tblSerial.
4. Open the Relationships window and delete the relationship between tables
tblArch and tblSerial.
5. Open tblArch in Design view, rename Arch_no to Old_Arch_no and move it to
the bottom of the field list. After that, add a new field, name it Arch_no,
and set its field type to Autonumber. Make this new field the primary key
instead of Old_Arch_no, and move it to where the "old" arch_no used to be.
Close the table and save the design changes.
6. Create and run an append query to append the records from tblArch_WORK to
tblArch, except that you insert tblArch_WORK.Arch_no into the field
tblArch.Old_Arch_no. Here's a simple example:
INSERT INTO tblArch ( Arch_Desc, Old_Arch_no )
SELECT tblArch_WORK.Arch_Desc, tblArch_WORK.Arch_no
FROM tblArch_WORK;
Note that no value is inserted into the new, autonumber Arch_no field. Its
values will be assigned automatically.
7. Create and run an append query that joins tblSerial_WORK to tblArch on
tblSerial_WORK.Arch_no = tblArch.Old_Arch_no and inserts the records into
tblSerial, picking up tblSerial.Arch_no from tblArch.Arch_no. Here's sample
SQL:
INSERT INTO tblSerial ( Serial_no, Arch_no, Serial_Desc )
SELECT tblSerial_WORK.Serial_no, tblArch.Arch_no,
tblSerial_WORK.Serial_Desc
FROM tblSerial_WORK INNER JOIN tblArch
ON tblSerial_WORK.Arch_no = tblArch.Old_Arch_no;
8. Open the relationships window and re-establish the relationship between
the two tables.
9. Check tblArch and tblSerial to ensure that all records are in them and
properly related.
10. If you don't need it for historical purposes, open tblArch in Design
View and delete the Old_Arch_no field. You may feel safer keeping it,
though.
11. Delete the two work tables. You're done.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(to reply via e-mail, remove NOSPAM from address)
Quote:
> I've programmed in VB 6.0 quite a bit, but am not quite sure where to start > for my little project. Any suggestions or help would be greatly > appreciated. > basically what I want to do is...
> I have 2 tables, tied (one Arch_no for many Serial_no) by an Arch_No.
> Arch_no is the primary key of one table. Right now this number is
generated
> by adding one to the largest number in that field of the database. What I
> would like to do is make the Arch_no an autonumber. How do I go about
> changing this number in both tables? If I change it in the main table, it
> won't tie to the records it should in the table it is tied to. I would
like
> to do this programmatically if possible. The database consists of 14000
> records and I would hate to have to change each one
> excuse the cross posts
> Thanks
> KW
|
| Sun, 30 May 2004 06:20:21 GMT |
|
|
|
John Viesca
#3 / 8
|
not sure where to begin 0) MAKE A BACKUP FIRST 1) Select the table that has Arch_no as its PKey and copy it to the
clipboard.
2) Paste the table into a new name, but ask for Structure Only
3) Open the new table and change Arch_No to AutoNumber and save it (it will
let you do that because the table is empty)
4) Paste the table again to the new name, but ask to Append the data to
existing table
5) Delete the old table (you may also need to delete any relationships
first)
6) Rename the new table to the old
--
John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
I've programmed in VB 6.0 quite a bit, but am not quite sure where to start
for my little project. Any suggestions or help would be greatly
appreciated.
basically what I want to do is...
I have 2 tables, tied (one Arch_no for many Serial_no) by an Arch_No.
Arch_no is the primary key of one table. Right now this number is generated
by adding one to the largest number in that field of the database. What I
would like to do is make the Arch_no an autonumber. How do I go about
changing this number in both tables? If I change it in the main table, it
won't tie to the records it should in the table it is tied to. I would like
to do this programmatically if possible. The database consists of 14000
records and I would hate to have to change each one
excuse the cross posts
Thanks
KW
|
| Sun, 30 May 2004 05:42:05 GMT |
|
|
|
John Viesca
#4 / 8
|
not sure where to begin 0) MAKE A BACKUP FIRST 1) Select the table that has Arch_no as its PKey and copy it to the
clipboard.
2) Paste the table into a new name, but ask for Structure Only
3) Open the new table and change Arch_No to AutoNumber and save it (it will
let you do that because the table is empty)
4) Paste the table again to the new name, but ask to Append the data to
existing table
5) Delete the old table (you may also need to delete any relationships
first)
6) Rename the new table to the old
--
John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
I've programmed in VB 6.0 quite a bit, but am not quite sure where to start
for my little project. Any suggestions or help would be greatly
appreciated.
basically what I want to do is...
I have 2 tables, tied (one Arch_no for many Serial_no) by an Arch_No.
Arch_no is the primary key of one table. Right now this number is generated
by adding one to the largest number in that field of the database. What I
would like to do is make the Arch_no an autonumber. How do I go about
changing this number in both tables? If I change it in the main table, it
won't tie to the records it should in the table it is tied to. I would like
to do this programmatically if possible. The database consists of 14000
records and I would hate to have to change each one
excuse the cross posts
Thanks
KW
|
| Sun, 30 May 2004 21:25:37 GMT |
|
|
|
John Viesca
#5 / 8
|
not sure where to begin Dirk- That's much more complicated than necessary. All he really needs to do is
change the Long Integer field in the "one" table to AutoNumber and load the
data. Make a copy of the structure, change the new structure to AutoNumber
(you can do that because the table is empty), then paste append the old
records. Don't need to touch the "many" table at all.
--
John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
Here's a procedure to follow:
1. Backup your database!
2. Copy both tables, structure and data, to new "work" tables. Suppose
your original tables are called "tblArch" and "tblSerial", and your work
tables are called "tblArch_WORK" and "tblSerial_WORK". Make sure these
tables have all the data.
3. Delete all records in tblArch and tblSerial.
4. Open the Relationships window and delete the relationship between tables
tblArch and tblSerial.
5. Open tblArch in Design view, rename Arch_no to Old_Arch_no and move it to
the bottom of the field list. After that, add a new field, name it Arch_no,
and set its field type to Autonumber. Make this new field the primary key
instead of Old_Arch_no, and move it to where the "old" arch_no used to be.
Close the table and save the design changes.
6. Create and run an append query to append the records from tblArch_WORK to
tblArch, except that you insert tblArch_WORK.Arch_no into the field
tblArch.Old_Arch_no. Here's a simple example:
INSERT INTO tblArch ( Arch_Desc, Old_Arch_no )
SELECT tblArch_WORK.Arch_Desc, tblArch_WORK.Arch_no
FROM tblArch_WORK;
Note that no value is inserted into the new, autonumber Arch_no field. Its
values will be assigned automatically.
7. Create and run an append query that joins tblSerial_WORK to tblArch on
tblSerial_WORK.Arch_no = tblArch.Old_Arch_no and inserts the records into
tblSerial, picking up tblSerial.Arch_no from tblArch.Arch_no. Here's sample
SQL:
INSERT INTO tblSerial ( Serial_no, Arch_no, Serial_Desc )
SELECT tblSerial_WORK.Serial_no, tblArch.Arch_no,
tblSerial_WORK.Serial_Desc
FROM tblSerial_WORK INNER JOIN tblArch
ON tblSerial_WORK.Arch_no = tblArch.Old_Arch_no;
8. Open the relationships window and re-establish the relationship between
the two tables.
9. Check tblArch and tblSerial to ensure that all records are in them and
properly related.
10. If you don't need it for historical purposes, open tblArch in Design
View and delete the Old_Arch_no field. You may feel safer keeping it,
though.
11. Delete the two work tables. You're done.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(to reply via e-mail, remove NOSPAM from address)
Quote:
> I've programmed in VB 6.0 quite a bit, but am not quite sure where to start > for my little project. Any suggestions or help would be greatly > appreciated. > basically what I want to do is...
> I have 2 tables, tied (one Arch_no for many Serial_no) by an Arch_No.
> Arch_no is the primary key of one table. Right now this number is
generated
> by adding one to the largest number in that field of the database. What I
> would like to do is make the Arch_no an autonumber. How do I go about
> changing this number in both tables? If I change it in the main table, it
> won't tie to the records it should in the table it is tied to. I would
like
> to do this programmatically if possible. The database consists of 14000
> records and I would hate to have to change each one
> excuse the cross posts
> Thanks
> KW
|
| Sun, 30 May 2004 21:27:57 GMT |
|
|
|
Dirk Goldga
#6 / 8
|
not sure where to begin John - You're probably right, and I considered the approach you outlined. I
decided on the long way because (a) I don't entirely trust the existing
keys, and (b) often people like to have their autonumbers start out in
sequence starting with 0 for cosmetic purposes -- this despite the fact that
it shouldn't make a particle of difference.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(to reply via e-mail, remove NOSPAM from address)
Quote: > Dirk- > That's much more complicated than necessary. All he really needs to do is
> change the Long Integer field in the "one" table to AutoNumber and load
the
> data. Make a copy of the structure, change the new structure to
AutoNumber
> (you can do that because the table is empty), then paste append the old
> records. Don't need to touch the "many" table at all.
> --
> John Viescas, author
> "Running Microsoft Access 2000"
> "SQL Queries for Mere Mortals"
> http://www.viescas.com/
> Here's a procedure to follow:
> 1. Backup your database!
> 2. Copy both tables, structure and data, to new "work" tables. Suppose
> your original tables are called "tblArch" and "tblSerial", and your work
> tables are called "tblArch_WORK" and "tblSerial_WORK". Make sure these
> tables have all the data.
> 3. Delete all records in tblArch and tblSerial.
> 4. Open the Relationships window and delete the relationship between
tables
> tblArch and tblSerial.
> 5. Open tblArch in Design view, rename Arch_no to Old_Arch_no and move it
to
> the bottom of the field list. After that, add a new field, name it
Arch_no,
> and set its field type to Autonumber. Make this new field the primary key
> instead of Old_Arch_no, and move it to where the "old" arch_no used to be.
> Close the table and save the design changes.
> 6. Create and run an append query to append the records from tblArch_WORK
to
> tblArch, except that you insert tblArch_WORK.Arch_no into the field
> tblArch.Old_Arch_no. Here's a simple example:
> INSERT INTO tblArch ( Arch_Desc, Old_Arch_no )
> SELECT tblArch_WORK.Arch_Desc, tblArch_WORK.Arch_no
> FROM tblArch_WORK;
> Note that no value is inserted into the new, autonumber Arch_no field.
Its
> values will be assigned automatically.
> 7. Create and run an append query that joins tblSerial_WORK to tblArch on
> tblSerial_WORK.Arch_no = tblArch.Old_Arch_no and inserts the records into
> tblSerial, picking up tblSerial.Arch_no from tblArch.Arch_no. Here's
sample
> SQL:
> INSERT INTO tblSerial ( Serial_no, Arch_no, Serial_Desc )
> SELECT tblSerial_WORK.Serial_no, tblArch.Arch_no,
> tblSerial_WORK.Serial_Desc
> FROM tblSerial_WORK INNER JOIN tblArch
> ON tblSerial_WORK.Arch_no = tblArch.Old_Arch_no;
> 8. Open the relationships window and re-establish the relationship between
> the two tables.
> 9. Check tblArch and tblSerial to ensure that all records are in them and
> properly related.
> 10. If you don't need it for historical purposes, open tblArch in Design
> View and delete the Old_Arch_no field. You may feel safer keeping it,
> though.
> 11. Delete the two work tables. You're done.
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
> (to reply via e-mail, remove NOSPAM from address)
> > I've programmed in VB 6.0 quite a bit, but am not quite sure where to
> start
> > for my little project. Any suggestions or help would be greatly
> > appreciated.
> > basically what I want to do is...
> > I have 2 tables, tied (one Arch_no for many Serial_no) by an Arch_No.
> > Arch_no is the primary key of one table. Right now this number is
> generated
> > by adding one to the largest number in that field of the database. What
I
> > would like to do is make the Arch_no an autonumber. How do I go about
> > changing this number in both tables? If I change it in the main table,
it
> > won't tie to the records it should in the table it is tied to. I would
> like
> > to do this programmatically if possible. The database consists of 14000
> > records and I would hate to have to change each one
> > excuse the cross posts
> > Thanks
> > KW
|
| Mon, 31 May 2004 03:46:07 GMT |
|
|
|
John Viesca
#7 / 8
|
not sure where to begin Dirk- Just for the sake of discussion -- between MVP's <s> -- using my shorter
method may be preferable because it preserves the original "key" values. If
documents have been produced from this application that displayed that
number (perhaps an Invoice Number or Part ID?), then it would be preferable
to not lose those numbers. Your method does suggest that you can keep the
"old" numbers around for historical reference, and perhaps that also solves
this problem.
--
John Viescas, author
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
John -
You're probably right, and I considered the approach you outlined. I
decided on the long way because (a) I don't entirely trust the existing
keys, and (b) often people like to have their autonumbers start out in
sequence starting with 0 for cosmetic purposes -- this despite the fact that
it shouldn't make a particle of difference.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(to reply via e-mail, remove NOSPAM from address)
Quote: > Dirk- > That's much more complicated than necessary. All he really needs to do is
> change the Long Integer field in the "one" table to AutoNumber and load
the
> data. Make a copy of the structure, change the new structure to
AutoNumber
> (you can do that because the table is empty), then paste append the old
> records. Don't need to touch the "many" table at all.
> --
> John Viescas, author
> "Running Microsoft Access 2000"
> "SQL Queries for Mere Mortals"
> http://www.viescas.com/
> Here's a procedure to follow:
> 1. Backup your database!
> 2. Copy both tables, structure and data, to new "work" tables. Suppose
> your original tables are called "tblArch" and "tblSerial", and your work
> tables are called "tblArch_WORK" and "tblSerial_WORK". Make sure these
> tables have all the data.
> 3. Delete all records in tblArch and tblSerial.
> 4. Open the Relationships window and delete the relationship between
tables
> tblArch and tblSerial.
> 5. Open tblArch in Design view, rename Arch_no to Old_Arch_no and move it
to
> the bottom of the field list. After that, add a new field, name it
Arch_no,
> and set its field type to Autonumber. Make this new field the primary key
> instead of Old_Arch_no, and move it to where the "old" arch_no used to be.
> Close the table and save the design changes.
> 6. Create and run an append query to append the records from tblArch_WORK
to
> tblArch, except that you insert tblArch_WORK.Arch_no into the field
> tblArch.Old_Arch_no. Here's a simple example:
> INSERT INTO tblArch ( Arch_Desc, Old_Arch_no )
> SELECT tblArch_WORK.Arch_Desc, tblArch_WORK.Arch_no
> FROM tblArch_WORK;
> Note that no value is inserted into the new, autonumber Arch_no field.
Its
> values will be assigned automatically.
> 7. Create and run an append query that joins tblSerial_WORK to tblArch on
> tblSerial_WORK.Arch_no = tblArch.Old_Arch_no and inserts the records into
> tblSerial, picking up tblSerial.Arch_no from tblArch.Arch_no. Here's
sample
> SQL:
> INSERT INTO tblSerial ( Serial_no, Arch_no, Serial_Desc )
> SELECT tblSerial_WORK.Serial_no, tblArch.Arch_no,
> tblSerial_WORK.Serial_Desc
> FROM tblSerial_WORK INNER JOIN tblArch
> ON tblSerial_WORK.Arch_no = tblArch.Old_Arch_no;
> 8. Open the relationships window and re-establish the relationship between
> the two tables.
> 9. Check tblArch and tblSerial to ensure that all records are in them and
> properly related.
> 10. If you don't need it for historical purposes, open tblArch in Design
> View and delete the Old_Arch_no field. You may feel safer keeping it,
> though.
> 11. Delete the two work tables. You're done.
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
> (to reply via e-mail, remove NOSPAM from address)
> > I've programmed in VB 6.0 quite a bit, but am not quite sure where to
> start
> > for my little project. Any suggestions or help would be greatly
> > appreciated.
> > basically what I want to do is...
> > I have 2 tables, tied (one Arch_no for many Serial_no) by an Arch_No.
> > Arch_no is the primary key of one table. Right now this number is
> generated
> > by adding one to the largest number in that field of the database. What
I
> > would like to do is make the Arch_no an autonumber. How do I go about
> > changing this number in both tables? If I change it in the main table,
it
> > won't tie to the records it should in the table it is tied to. I would
> like
> > to do this programmatically if possible. The database consists of 14000
> > records and I would hate to have to change each one
> > excuse the cross posts
> > Thanks
> > KW
|
| Mon, 31 May 2004 03:59:39 GMT |
|
|
|
Dirk Goldga
#8 / 8
|
not sure where to begin John - I agree completely, provided that preserving the original keys is desirable.
I've seen so many posts asking "how can I make my autonumbers start at 1?"
that I thought I'd just head off that whole discussion by making them do so,
initially.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(to reply via e-mail, remove NOSPAM from address)
Quote: > Dirk- > Just for the sake of discussion -- between MVP's <s> -- using my shorter
> method may be preferable because it preserves the original "key" values.
If
> documents have been produced from this application that displayed that
> number (perhaps an Invoice Number or Part ID?), then it would be
preferable
> to not lose those numbers. Your method does suggest that you can keep the
> "old" numbers around for historical reference, and perhaps that also
solves
> this problem.
> --
> John Viescas, author
> "Running Microsoft Access 2000"
> "SQL Queries for Mere Mortals"
> http://www.viescas.com/
> John -
> You're probably right, and I considered the approach you outlined. I
> decided on the long way because (a) I don't entirely trust the existing
> keys, and (b) often people like to have their autonumbers start out in
> sequence starting with 0 for cosmetic purposes -- this despite the fact
that
> it shouldn't make a particle of difference.
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
> (to reply via e-mail, remove NOSPAM from address)
> > Dirk-
> > That's much more complicated than necessary. All he really needs to do
is
> > change the Long Integer field in the "one" table to AutoNumber and load
> the
> > data. Make a copy of the structure, change the new structure to
> AutoNumber
> > (you can do that because the table is empty), then paste append the old
> > records. Don't need to touch the "many" table at all.
> > --
> > John Viescas, author
> > "Running Microsoft Access 2000"
> > "SQL Queries for Mere Mortals"
> > http://www.viescas.com/
> > Here's a procedure to follow:
> > 1. Backup your database!
> > 2. Copy both tables, structure and data, to new "work" tables. Suppose
> > your original tables are called "tblArch" and "tblSerial", and your work
> > tables are called "tblArch_WORK" and "tblSerial_WORK". Make sure these
> > tables have all the data.
> > 3. Delete all records in tblArch and tblSerial.
> > 4. Open the Relationships window and delete the relationship between
> tables
> > tblArch and tblSerial.
> > 5. Open tblArch in Design view, rename Arch_no to Old_Arch_no and move
it
> to
> > the bottom of the field list. After that, add a new field, name it
> Arch_no,
> > and set its field type to Autonumber. Make this new field the primary
key
> > instead of Old_Arch_no, and move it to where the "old" arch_no used to
be.
> > Close the table and save the design changes.
> > 6. Create and run an append query to append the records from
tblArch_WORK
> to
> > tblArch, except that you insert tblArch_WORK.Arch_no into the field
> > tblArch.Old_Arch_no. Here's a simple example:
> > INSERT INTO tblArch ( Arch_Desc, Old_Arch_no )
> > SELECT tblArch_WORK.Arch_Desc, tblArch_WORK.Arch_no
> > FROM tblArch_WORK;
> > Note that no value is inserted into the new, autonumber Arch_no field.
> Its
> > values will be assigned automatically.
> > 7. Create and run an append query that joins tblSerial_WORK to tblArch
on
> > tblSerial_WORK.Arch_no = tblArch.Old_Arch_no and inserts the records
into
> > tblSerial, picking up tblSerial.Arch_no from tblArch.Arch_no. Here's
> sample
> > SQL:
> > INSERT INTO tblSerial ( Serial_no, Arch_no, Serial_Desc )
> > SELECT tblSerial_WORK.Serial_no, tblArch.Arch_no,
> > tblSerial_WORK.Serial_Desc
> > FROM tblSerial_WORK INNER JOIN tblArch
> > ON tblSerial_WORK.Arch_no = tblArch.Old_Arch_no;
> > 8. Open the relationships window and re-establish the relationship
between
> > the two tables.
> > 9. Check tblArch and tblSerial to ensure that all records are in them
and
> > properly related.
> > 10. If you don't need it for historical purposes, open tblArch in Design
> > View and delete the Old_Arch_no field. You may feel safer keeping it,
> > though.
> > 11. Delete the two work tables. You're done.
> > --
> > Dirk Goldgar, MS Access MVP
> > www.datagnostics.com
> > (to reply via e-mail, remove NOSPAM from address)
Quote: > > > I've programmed in VB 6.0 quite a bit, but am not quite sure where to > > start > > > for my little project. Any suggestions or help would be greatly > > > appreciated. > > > basically what I want to do is...
> > > I have 2 tables, tied (one Arch_no for many Serial_no) by an Arch_No.
> > > Arch_no is the primary key of one table. Right now this number is
> > generated
> > > by adding one to the largest number in that field of the database.
What
> I
> > > would like to do is make the Arch_no an autonumber. How do I go
about
> > > changing this number in both tables? If I change it in the main
table,
> it
> > > won't tie to the records it should in the table it is tied to. I
would
> > like
> > > to do this programmatically if possible. The database consists of
14000
> > > records and I would hate to have to change each one
> > > excuse the cross posts
> > > Thanks
> > > KW
|
| Mon, 31 May 2004 04:27:29 GMT |
|
| |
|
