Data Getting too Large for Access...Need New Solution

[basebalplayr33]

Hello all,

I currently work for a small firm (<20 employees) that uses MS Access for most of our data needs. We received data files from clients anywhere from 20 MB to multiple GB. Obviously the very large data we have SQL server and 1 SQL programmer that can manipulate it. All of our analysts and managers know Access inside and out. They mainly use the Import/Export wizard, and run basic SELECT, Maket Table, Update, Crosstab, Delete, etc. queries. The problem is that our data is getting to the point where Access' 2 GB limit is becoming an issue. We have tried to import the 2GB+ files into SQL and linking to access through ODBC, but update queries seem to just hang and never complete due to the size of the data.

The other issue is that none of our analysts know SQL language, so changing over to SQL entirely isn't really an option at this stage. My question is this: Is there an alternative solution to MS Access that provides a GUI (like Access does) so that our analysts and managers do not have to learn SQL (which would be a huge speedbump).

Here's an example of something our analysts would do:

1) receive 400 MB text file from client.

2) import text file to an access database.

3) add certain fields (columns) based on what we need to manipulate the data to do

4) execute update, crosstab, Select, etc. queries to maniuplate the data

5) export summaries from the data to excel to be formatted/printed.

We would rather spend money and get a new program/system with GUI where our analysts can drag tables, link them, update them, etc. without having to write SQL code. Thank you all in advance for any help/comments/suggestions.

 

[xenou]

I'm not sure. Access is limited in size. No way around that. But 400MB files should'nt be a problem.

1. Be sure you are compacting databases regularly.
2. Create indexes on fields that are involved in WHERE criteria to improve performance

Given the size of your data, it may be time for your analysts to learn SQL Server fundamentals. It's not a big leap from Access, at least as far as the basics are concerned. You tend to have to do more in "code" rather than in "designers", maybe, but the basics are not hard. Note that once you have the tables set up in SQL Server (with any necessary modifications), you can connect to them with Access and so the data analysis itself can still be done with Access.

Note:
Please don't scare me like this:

The other issue is that none of our analysts know SQL language, so changing over to SQL entirely isn't really an option at this stage

If they are using Access for data analysis they should know the basics of SQL. Period. Invest in training!

 

[HiTechCoach]

I totally agree that anyone doing data analysis needs to seriously consider SQL training.

The other issue is that none of our analysts know SQL language, so changing over to SQL entirely isn't really an option at this stage

What really scares me about this statement is the fact that someone is a paid data analyst and they don't know SQL. How accurate 9s their analysis? Hum??????