access decentralized mdb files

[suklee64]

I am a business analyst. I am currently thinking about the best way to access decentralized mdb files. We actually have 9 different analysts. their monthly jobs are usually with following processes:

1) Run different queries from Oracle DB to make specific raw data
2) Download it to their PC
3) Export to MS Access format
4) Copy and Paste it to Excel spreadsheet
5) Make Pivot table from EXcel

And we recognize that these processes overlapped and waste our time and resources. We are currently thinking if one of us does above process 1),2), and 3) like his monthly job. The other analysts can do the remaining processes 4) and 5).

At this point, My questions are:
Q1) Can we make MS Access DB sizes more than 1GB?
Q2) Can we use two different DB files at the same time?
Q3) Any suggestion for improving above process within MS Access?

Thanks,
Eric

 

[georgedwilkinson]

Q1) Can we make MS Access DB sizes more than 1GB?

Access db files can be up to 2GB.

Q2) Can we use two different DB files at the same time?

Why couldn't you?

Q3) Any suggestion for improving above process within MS Access?

Steps 1-3 can be consolidated into one step. It could also be automated. Just connect from the Access DB directly to Oracle and run your update/append queries in Access. Step 4-5 can be done directly in Excel (from Access). Just create your pivot table using your Access database as the data source. And I'm not sure why on earth you would be using analysts time doing any of this except automating it.

Also, I'm not sure why you don't just skip all the steps and use Oracle as your pivot table source (in Excel). Seems odd to move all your data from one of the finest reporting DBMSs in the world to a database that really primarily used as an OLTP database.