Access file too large for efficient use 1

[mkirros]

We have a strange situation. We're a non-profit with a membership database in an old database system based on FoxPro 2.5 (DOS). We can't change this right now because of budget considerations.

I have an Access database that I put together for our organizers to use, which requires a monthly import of information from the FoxPro database (linking tables won't work in this situation).

The problem is that the organizers' database is getting to be too big to run efficiently in Access across a network (Win2000 AD Server). It would work fine in SQL Server (which we have), but I don't know of any way to make the imports work well in SQL Server. Does anybody know of any other possible work-arounds?

 

[smedvid]

Just some thoughts...
- Consider exporting data from FoxPro to a flat ASCII Text file. That would provide an easy import to SQL Server via a DTS Package.

- Attempt to split the front-end and back-end MS Access database. You may gain some perfomance improvements. In the past, I splt back-end data into 2 or more seperate databases and linked to them.

- Review the overall data model and see if any design changes such as indexes would improve performance.

- Can any data be archived and removed from current production tables?

- SQL Server backend with a MS Access 2003 ADP/ADE Front End would perform well over a network.

-As a non-profit, perhaps solicit software donations from local corporations/companies. If they donate software, I imagine it would be tax deductible. And, consider soliciting donated services (or minimal pay) from HS or College students to perform the MS Access development you may require. The college students would enjoy the references and experience for their resumes.

htwh,


Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[mkirros]

Thanks for the ideas. Unfortunately archiving data is not an option at this point. There's also nothing we can do with the data model right now, because of the old FoxPro-based system that we're using. We're stuck with it for the time being for tracking all of our contribution information, and we can't modify the program.

 

[MichaelRed]

" ... we can't modify ... "

Cant == wont



MichaelRed

 

[mkirros]

In this case, can't means can't. We don't have the source code.

 

[MichaelRed]

Why do you need source code if all you are using from the legacy app is the data, which according to the above, you are already able to get into another aplication anyway? One PRESUMES that the response is that you are still entering the data in the legacy app, but then it would appear that all you need is to replicate that data entry? How hard can it be? What is so esoteric about the internal (legacy app) transform that you can't decipher (e.g. reverse engineer) it?



MichaelRed

 

[mkirros]

I guess I wasn't being clear. The legacy app is actually the main database. Althoug ancient, it's a very sophisticated system. It tracks all of our contribution information, along with all of our monthly processing, including running our call lists (we call our members on a regular basis for contributions). We have neither the staff time nor the budget to either reverse engineer it or create a completely new system. I basically am the tech staff, and I function as dba, network administrator, and help desk.

 

[psemianonymous]

The quick+dirty answer is to make some sort of cheap script that copies the database to their c:\temp folder every time they open it and run from c:\temp\. This solution will take you 15 minutes to code and will even significantly boost everyone's performance, minus the initial multi-minute file copy.

That is all, of course, assuming that you're not actually doing ANY data entry in Access; i.e. it's for reading only.