Hello all,

I am new to Access and would like to find out if Access is the right tool for me.

About my knowledge: I have very superficial knowledge and experience in Access (I use Access 2007). I know the basics of database design and only very basic knowledge of Access forms and reports. I have no knowledge of VBA/macros.

What I would like to accomplish: I am an IT auditor and I test the controls that IT departments have, such as physical access, logical access, change management etc. Each report has sections (like the ones I mentioned), each section has specific controls, each control could have several instances (i.e. same control, but for different applications or for different operating systems or networks) and each of these instances I test separately. There are added complexities, such as different reports have different applications in scope, which determine which controls appear in which report. Then there's also stuff like who's the client contact, who does the testing, what the population/sample will be etc.

What I would like to do is ideally automate some of the preparation of this. I've got well over 100 Excel spreadsheet templates, that could be populated with information from a database instead of doing it all manually.

I've designed a crude version of the database, but hit a brick wall when I realised (after reading many posts here) that to export my report to Excel and make it fit my preferred template, I'll probably need to export the report (one at a time?) and then write a macro to format it.


My question is:

Is Access the right tool for me? Is there a way that I can take the information in my database and create from it 100-odd excel files in a format of my choosing at the click of a button? (of course I'll need to work hard before that button works...)


I appreciate any assistance/insight I can get.

p.s. I'm not looking for any free code or anything... just wondering if I should continue to pursue this.

Some comments:

1. You can link an Excel spreadsheet to one or more tables/queries in an Access-db. Or, conversely, you can link an Access-table to a spreadsheet.

I have an application where data management and treatment is done in Access, some 20 tables and queries are linked from a spreadsheet, figures are created in the spreadsheet, and the lot is then linked to from Word. In the workbook, the data contained in the linked tables/queries is used all over on other spreadsheets in the same workbook.

2. Are your spreadsheets used for reporting only, or is the data from them processed further down the road and thus required to be in a spreadsheet (to be extractable)? In the first case, Access has quite reasonable reporting facilities, so it may be worthwhile to consider implementing the reports in Access instead. Also, Access reports can also be exported to Excel.

As to your question - difficult to answer. Only you can do the Costs vs. benefits. I would suggest that you identify the salient points and try with one report - to gain the experience and then to be able to make an informed decision.

Thanks, spikepl. That's very insightful. I'll have to see how the whole linking works. Certainly seems like the right approach.

What's this application you mentioned? Is it something you created or readily available for mere mortals like myself?

The spreadsheets unfortunately are a requirement. The software my firm uses can only accept word or excel sheets on which we do our testing. Since a lot of our testing results are best shown in tabular format, we only ever use excel. So documenting in Access is a no-go.

I completely agree with what you say about trying to figure out the cost/benefit of this. I think that if I can somehow run a query (hopefully I'm using the right terminology here), which off the back of that generates the 100 or so templates automatically - then it will be a great time saver and will be most beneficial. But if I need to create each one separately I might as well just roll forward the previous spreadsheets we used and whoever does the testing will manually update the required fields.

The whole linking thing with access working in the background - that sounds like something I could use, though. Any other pointers will be much appreciated. Thanks!

The mentioned application is bespoke development for a customer.

As to automating the creation of Excel-sheets: the first one will be painful, the second one will take less work, and the 100th will be a piece of cake. How much all this is in total depends on how much is shared between them and hence can be reused. The code can be made to run through hoops, according to the wishes of the programmer, so I do not see any technical obstacles.

My general rule of thumb is that complicated tasks are only worthwhile implementing accurately in a database if they are going to be repeated constantly over a long time period. There is a grey area where it will be implemented for a while but may stop at some point in the future.

If you can get the information into a query it is very easy to dump it into a spreadsheet. Quite often odd data structure within excel spreadsheets can be difficult to replicate. Eg if spreadsheets need to have a series of variables in a predefined number of cells / rows at the top of the page followed by the main query / table of data. Or sums at predefined columns etc...

In a number of my applications I have been thinking that I would give users access to a read only view of raw data. This would allow them to cut and paste to excel and would mean the one off tinkerers can tinker to their hearts desire while not on mass deleting / altering data.