Suggestions for Large Database

Q

Qigong

My Access database is getting larger and larger every month because of the
amount of historical data used and new data imported. I track records that
go back to 1978 and I produce monthly comparison reports. I have already
split the database into front and back ends. Eventually it will be too big
for Access to handle. Has anyone ever encountered this problem in Access and
if so, how did you handle it? Does anyone have any suggestions?

Thanks!
 
D

dlw

That's what I need to show my boss- "Look, compared to 27 years ago, our DVD
sales are great!"
 
A

Albert D.Kallal

You don't mention how large.

For example, after you do a compact and repair, how large is the file?

Also, what is the max number of records you have so far in a table?

I mean, tables of 150,000 records in ms-access is quite small.

As for handling the problem? Well, you could move the back end data to sql
server.

Depending on your application, perhaps the data can be split out into each
year?

How large is your data file(s) now?
 
Q

Qigong

I have a couple, the largest being 750 MB, after doing a compact and repair.
The second isn't as large (it's currently only at 370 MB) but I import data
to it every week and monthly.

I have thought about splitting it into years or a range of years, but I need
to be able to produce ad hoc reports comparing different years. This would
mean a lot of linking but I think that would work.
 
A

Albert D.Kallal

I have thought about splitting it into years or a range of years, but I
need
to be able to produce ad hoc reports comparing different years. This
would
mean a lot of linking but I think that would work.
Click to expand...

Yes, the above is exactly your problem. Trying to piece together data for
different years is really a pain here....

And, your size of 750 megs is rather large.

Hum, I certainly would consider using sql server as a solution here. You can
certainly continue to use ms-access for the reports etc, but your data size
does hint that you are running out of room for ms-access.....
 
A

aaron.kempf

if you use an ADP you should be able to preserve many of your existing
forms and reports.
 
J

Joe

Hello
My question is can we use ADP with MS Access 97.I know ADP
works fine with ACCESS 2000 with a back end sql 2000.

My set up is Access 97 front end and back end SQL SERVER 2000.i am
currently using ODBC.Thinking of ADP any smart idea whether ADP can
work with ACCESS 97.

wait for your reply.
thanks
joe
 
J

Joe

Hello
My question is can we use ADP with MS Access 97.I know ADP
works fine with ACCESS 2000 with a back end sql 2000.

My set up is Access 97 front end and back end SQL SERVER 2000.i am
currently using ODBC.Thinking of ADP any smart idea whether ADP can
work with ACCESS 97.

wait for your reply.
thanks
joe
 
A

Alex White MCDBA MCSE

You don't need an ADP, although personally for a brand new project against
SQL I would use an ADP, for an existing project 'just' upsize the mdb data
into SQL server and then link the SQL data into your front-end, only major
things to consider is SQL,Access differences e.g. Access uses * as it's wlid
card and SQL uses %, Access has the ability to understand true and false in
queries SQL does not it only understands 1 and 0, there are other
differences but they are the 2 major ones that I consider straight away.
 
A

aaron.kempf

nah you can't use ADP against Access 97.

You really should upgrade to 2003 and use SQL Server.. and Access Data
Projects.

but thats not going to help you if you dont have a good-solid design.

what you really need-- once you have that many records-- is SQL Server
Analysis Services. It is black magic; the most wonderful db technology
ever made.

are you in the US?

-aaron
 
A

Albert D.Kallal

Hello
My question is can we use ADP with MS Access 97.I know ADP
works fine with ACCESS 2000 with a back end sql 2000.

My set up is Access 97 front end and back end SQL SERVER 2000.i am
currently using ODBC.Thinking of ADP any smart idea whether ADP can
work with ACCESS 97.
Click to expand...

There is no need to use a adp. If you got sql server, then the issues of
performance can be dealt with. You software will not run faster if you use
ADP, use VB.net, use c++, or in fact use access97. They are ALL going to
perform at the SAME RATE because you are using the SAME database engine.

There is VERY little benefits to moving to a ADP if you got a existing
application. So, you got a robust, scalable and very powerful database
called sql server. What you use to connect to that server is of little
consequences here.

You are NOT going to get better performance by going to vb.net, or c#, or in
fact continuing to use access97.

So, why do you want, or need to convert to a ADP project?
 
Top