database size

[Debbie]

I have a database that was getting up in size, so what I
decided to do was Import the data into another database
and delete half from each.

Now at this point I will have two databases that is a
continuous of the other.

The two issues that I have is:
1) after deleting half of the records from each of the
databases it did not reduce the size, why? &

2) I would like to change the auto ID number for each
database: first database starting at 1001 and the second
database starting at 2001, how can I accomplish this?

 

[Ken Snell [MVP]]

Getting up in size? How big is that? Access 2000 and up will handle 2GB
files.

Did you do a compact and repair on the databases after you moved the data?
That regains unused space.

Not sure that it was good idea nor necessary for you to do what you did by
splitting the data into two databases, but .... hope it works!

 

[Debbie]

The database was at a size of 1.4GB and I will be adding
alot more to it, thats why I decided to do two databases.

I am doing the compact and repair right now, but what
about the auto ID number, can this be changed for each
database?

 

[Ken Snell [MVP]]

I'm not understanding...are you referring to the autonumber field? What do
you want to achieve?

 

[Debbie]

Yes the autonumbering, I want to change the numbers that
has already been generated, to my specifications.

Can that be done?

 

[Ken Snell [MVP]]

Assuming that those numbers are used to link to children tables, this can be
tricky. Are you sure you need to do this?

 

[Debbie]

No, I'm not sure, but if you give me some idea of how it
is done, then I will decide from there.

 

[Debbie]

Now I am having another problem with my database.

I have pictures that I am linking into my database, but
when I insert them now it does not put the picture in it
puts the name of the file in the box.

HELP! I had this problem when I first created the
database and can't remember what I did to fix it.

 

[Ken Snell [MVP]]

The easiest way is to ensure that you have Referential integrity (with
cascade update) set between the autonumber field in the main table and the
linking (foreign) fields in the children table. Do this in the Relationships
window. Then, any changes you make to the parent autonumber value will be
propogated to the children.

If you can not set up this referential integrity, then you'll need to go
through a series of steps (make a copy of your database first in case the
data are incorrectly modified!):
(1) add a new field to the parent table that will hold your new "value"
for the autonumber field.
(2) run an update query to populate the new field in the parent table
with your new "value".
(3) run an update query to modify the value of the linking field in the
children table based on the current linkage to the original autonumber field
as the "joining" link.
(4) run an update query to populate the original field in the parent
table with your new "value" from the "new" field.
(5) delete the "new" field from the main table.


You must be very careful when following the above steps, as you don't want
to orphan any children records.

 

[Ken Snell [MVP]]

Is the field a hyperlink field? It needs to be if I'm understanding what you
want to see (picture in the table's datasheet view).