Excel macros vs Access queries

[rhittle]

I thought I would just try to keep in EXCEL to do this set of work I have rather than use Access. I was hoping to record a macro consisting of filtering the data. I can do that and such. But the thought came up - If I run a macro in EXCEL then the speadsheet will stay the way it was after I ran the macro. This is something I do not want. I am only wanting to use the macro of filter actions to retrieve data that I want to focus on and prehapes edit it. When I bring up the sheet again I want the edits but will not want the way the filter macro last left the sheet. So I am thinking if I use Access and link to the Excel sheet I can use queries to do what I want. I can edit any data I want but the original order of the data will stay the same. If I want the order different and remain so, I could save as a different speadsheet or table.

Am I correct?

Uriah

 

[xenou]

Hi,
1) If by filtering (with Excel) you literally mean filtering (autofilter - hiding rows), then the original order/appearance of the sheet is restored by removing the filter or by "showing all".

2) If you link to Access you are correct you can change data by using the queries - a sometimes overlooked fact. But your queries can be set up to allow or disallow such edits in the query view - the recordset property for the query should be "dynaset", not "snapshot". Dynaset is the default on my application. A drawback to using Access is that the datasheet view in Access is not nearly as good as Excel for ad hoc data analysis - or even for viewing data for that matter.

I would base your decision on whether you can meet your needs in Excel - if you can use Excel, you probably want to do so. If you need Access because the dataset is become to large and difficult to work with, then it may be time to move to database for data storage - with the appropriate tools for maintaining and updating the tables (which can involve more work/design but can be very effective if done well).

HTH

 

[rhittle]

Thank you so very much Alexander.

As you can guess I have been just a causul user of EXCEL and ACCESS.

You saved a lot of unnecessary work.

Uriah