recommendation: is access the best solution?

[patti]

I have been given an opportunity to rework/create from scratch an access db.

I looked over wht the company already has:

Several linked tables including one with over a million records.
Macros that are use executed which would be better served integrated.
The reports they need take up to a day to run.
The user has very little inputting (basically one field) that generates it
all.

Would access be the best solution? Or is this issue better served by
SqlServer, which i am not adept at (yet). Or is there another way to deal w/
this project?

Thanks for any advice.

patti

 

[Kevin3NF]

Not nearly enough info...but your comment on reports makes me think you
ought to move the backend to SQL Server and use the SQL engine to process
that data, then generate a report from the result.

I had a similar situation a few years ago....took one report from 14 hours
to 2 minutes

So...

--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm

Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com

 

[(PeteCresswell)]

Per patti:

Would access be the best solution? Or is this issue better served by
SqlServer, which i am not adept at (yet). Or is there another way to deal w/
this project?

Distinguish between "Front End" and "Back End".

Front End is whatever controls the UI and gives access to the data.

Back End is where the data lives.


I'd want a lot more info before telling this to a client, but my initial
reaction is that this is a case where the back end could be moved to SQL Server
and the front end kept the same except for changing the ODBC links.

If that didn't help enough, the next thing I'd try would be to write some stored
procedures in the back end to deliver data to the front end via ADO instead of
ODBC.


Seems to me like the first move could be done with very little cost: just
migrate the JET tables to a SQL Server DB and give it a try...probably could be
done in less than 8 manhours.

 

[patti]

thanks kevin & pete for the insight.

i have only worked within the confines of access (which includes writing sql
queries and vb code). I understand the need for the backend to be separate
from the front;
i'm not sure how to communicate between access and sql server.

for example:

Tables from different access db linked
Table A is list of sites
Table B is list of items

My user inputs the items they require and the sites they
thay will be delivering to.

How would i get those parameters into sql server?

And then after sql server crunches the data, how do i
bring
it back into access to use for reports?
(this part i think i get: sql server would create a
table based
on the parameters and i would link to that info??)

Thanks for all your help.