Opitimising database performance

[CravenStu]

Hi

I developed an Access 97 database a year ago to import 120,000 lines of data and create various outputs.
A year on it is now importing 1.4 millions lines of data and the queries that analyse the data are taking forever.
I have run the database analyser nd everthing looks ok.

I have 2 questions

1/ How can I increase the process speed?

2/ How do I compact the database using code and not rename it.

Thanks

 

[mkelly]

Go to
Tools
Options
Click on the General tab
Click "compact on close"

 

[CravenStu]

Thanks mkelly

I tried that, but because it is Access 97 that functionality is not available.

 

[mkelly]

sub compactDB()
dim je as new jro.jetengine

if len(dir("c:\folder name\whatever.mdb")) then
kill "C:\folder name\whatever.mdb
end if

je.compactdatabase
sourceconnection:="data source=c:\folder name\whatever.mdb,_
dest connection:="data source=c:\folder name\whatever.mdb; "&_
"jet oledb:encrypt database=false"

 

[Pat Hartman]

Must you import the data? I presume that you are creating summary reports since printing a detail report with 1.4 million records seems pretty pointless. Can you not run queries against the ODBC source tables? You can make stored procedures if you need even more speed.