Question general question

[Clivej01]

I made a database years ago for a local company, it is slowly dying. It has approx 30000 invoices, and more or less the same in clients and jobs details. I have been instructed to make a new database to do the same thing but in the latest version of office.
the main problem is the number of records in the three main tables.
how would i go about getting the database to create a new table at the end of the financial year to archive records "job completed and paid for" done in that year, delete these records from the table in use.
When searching for a record it will have to be able to search all the records either new or archived.

need help, or my head looked at "havn't used access for about 10 years" so access for dummies reply will not offend.

 

[jzwp22]

Some questions:

In what version of Access was the database created?

Has the company done a compact and repair recently on the database?

Is the database split into front and back ends?

What is the size of the file (after running the compact/repair utility)?

What is the table structure?

Is there any code operating behind the scenes of the database?

 

[Clivej01]

the database was created in 1998
the company is doing a repair and compact on a regular basis
the size and of the database is approx 160,000 kb
there is a small bit of code running, helped with decimal point with 17.5 rate of VAT

 

[jzwp22]

I assume the database was created in Access 97 which has a max size of 1GB, so the size is probably not an issue, but moving to an newer version will allow you more room to grow. You should be able to just import the tables into a newer version without too much trouble; the code on the other hand might need some reworking as there was a fundamental change between Access 97 and the Access 2000-2010 versions.

You did not answer these questions:

Is the database split into front and back ends?
What is the table structure?

 

[Clivej01]

standard enough tables, there are approx 30 tables
not sure wot u mean about front and back end,
I tried to import the database to 2010, just kept corrupting database, button didnt work etc.
I want to remake the software to have additional functionality, emailing linked to word etc. also there was always a problem with invoices with multipule payments, credits have another way to do this now,

 

[jzwp22]

not sure wot u mean about front and back end,

If a database is used by multiple users over a network, the database is split into a front end and a backend. The front end file contains all the forms, queries, reports, modules etc. Each user would have a copy of the front end file on their local harddrive. The back end file will contain only the tables and will be located on a server. You would link the front end to the back end.


Having multiple users directly access 1 file that is on a network can cause corruption of the file.

Can you import just the tables into a new Access 2010 database file?

 

[Clivej01]

I recon i can, but I want to create a new database from scratch. will import tables as required.
Wot im looking for is a way to do, when a job is paid for, at the end of that year when i run a year end it takes the completed jobs, creates a new table "eg jobs 2010" and moves the record here. but the problem is i need this new table to appear if i am looking for a job in 2012 "look through archive in search"

 

[jzwp22]

...creates a new table "eg jobs 2010" and moves the record here. but the problem is i need this new table to appear if i am looking for a job in 2012 "look through archive in search"

Separating the old data into separate tables causes issues because your forms etc. will only work with the tables that hold the current data (i.e. those tables without the year designation). The better way to approach this is to keep all records in the original table and filter your forms to the current year or whatever recent time period you want your users to have access to on a regular basis. When they need to search older records, you can remove the filter or expand the filter's criteria.

 

[Clivej01]

one table is approx 25000 records, and at the end of each session it has to put records in two other tables payment and credits, generally messes up doing this. because of the amount of the update.

 

[jzwp22]

I don't know enough about your process but why would you need to update all the records? Why not filter before doing the update? It sounds like you may have structural issues with the database, but it is hard to say or to help without more detailed information on the table structure.