Using Access to extend excel

Sounds good. This makes sense. Do you know of any examples where storing data in access is better suited than storing data in excel?
My firm does not use access and I am investigating possible usage.
For example: someone told me that he uses access to compare datasets (that should match) and return differences. This would seem a perfect feature for access, but not necessarely a strenght in Excel.

That's kind of hard to provide. Anything you can store in Excel you can store in Access but, as I mentioned earlier, there are some types of data which might be better stored in Excel than in Access. In general, anytime you need to "persist" different datasets you would be better off using Access.

Let's say you have a Customer database, and you want to store financial information specific to each Customer - perhaps you want to take a "snapshot" of a customer's financial picture on the first of each month. In Excel, you would have to jump through some hoops in order to easily store a particular customer's monthly "snapshot" ... you could certainly use a different workbook for each one, but you would then be hard pressed to compare my account with your account, if you needed to do so.

If, instead, you stored this in Access, you would store the customer info in one table and the financial info in another table. Each "row" of financial info would be "related" back to a specific customer ... and you could have an unlimited (basically) number of rows for each customer. Therefore, for a single record in the Customer table you could have hundreds or thousands of records in the financial table.

This is a very, very simplistic example. A "real" Access application has dozens of tables, forms, reports, etc ... but hopefully this gives you some idea.

nickel2001,

LSMConsulting, has some "Excel-lent" advice here.

You should also consider the "Structure" of your Excel data. The great thing about Excel is that you can put whatever you want, wherever you want. For example "Price" is in cell A1, "Quantity" is in Cell A2, but "Total" could be in cell B4. Access is not designed to work this way.

In Excel blank cells are treated as Zeros, In Access a Blank is a "Null" (You can do math with a Zero, but not with a Null)

When you import Excel to Access, calculated values come in as static values.

Any Access person can probably tell you a horror story about a request like this : "Hey, I heard Access can do this better than Excel, can I convert my Excel sheet to Access?"

Then you open up the Excel sheet, and they have: Blank Rows and Columns, Subtotals, Merged Cells, Linked Values, Embedded Pivot Tables, Numbers Formatted as text, Text Formatted a numbers and "Strange" date Formats ....ect

Yikes!

I have done this quite a few times. Here are some general rules I use for converting (or Linking) Excel to Access:
1.      Lists (tables) consists of two parts: Fields and Records
2.      Records are what you are keeping track of
3.      Records are in the Rows
4.      Fields are Descriptors or Attributes of each Record
5.      Fields are in the Columns
6.      1 Row, 1 Record
7.      Field Names in Row 1
8.      Field Names should be BOLD
9.      Avoid Blank Cells
10.      Do Not Insert “Blank” Rows or Columns
11.      No Miscellaneous Data or Calculations "floating around"
12.      1 List Per Sheet

Many Excel users just starting out, think they will eventually "Outgrow" Excel, and have to upgrade to Access.
Not so.

Also, the learning curve, for new users, is steeper with Access than with Excel.

I hope this helps

Re: Excel vs Access

Both have their own pros & cons, but ultimately the decision to develop in either comes down to:

1) Security Model required (if any)
2) Quantity of data to be stored (rows x columns) rather than actual byte capacities
3) Speed of development (i.e. costs in short term versus long term)
4) Ease of deployment (into user environment)
5) Stability of product
6) Multi-user usage/capabilities
7) Impact (costs) of Downtime due to failure
8) Familiarity with product/development environment
9) Speed of execution
10) User interface

If the speed of execution, stability, storage capabilities and security of your data are all primary concerns, then MS-Access is the route to take, and certainly when MS-Excel worksheets retain data from months/years ago, then archiving these in Access tables would reduce the overall size of the Excel workbook (thus resulting in faster performance).

Also, the multi-user facilities that MS-Access provides far outweigh the "shared" workbook option in MS-Excel (and Access ultimately proves the more stable platform in this respect).

BFN,

fp.

Guys:

Thanks a lot for all your great answers. I'd like to give you a more concrete example of what we currently do with excel.

We currently use excel for daily reconciliation of Traders’ Profit and Loss. Reconciliations consist of: Investigating and resolving differences between the Trading system and the accounting system

Would access be more efficient than excel to find unmatched records and to find missing data between the two data sources.

How would you set this up in access?
Thanks,
Nickel

Increasing question point value

Hi again,

You may use Excel Database Queries to import data from external data repositories, or use SQL statements to interrogate external data supported by (ActiveX Data Objects / ADO) Connection Strings.

An Excel worksheet can be used as a database, and SQL statements can be applied to retrieve data based on WHERE clause criteria.

In essence, an Access data table & an Excel worksheet are both tabular (row by column) storage mediums.

The advantage of Access over Excel is that Access can store in excess of 65,536 rows of data per 'table' (i.e. per worksheet).

I am presuming that you will probably have more rows of data than this limit over time in an Excel worksheet, so Access is the way forward (out of the two tools you mentioned).

Access would be more efficient in actually querying the data, but there will be an overhead imposed by opening a connection in MS-Excel to the Access database file initially.  (The connection could remain open thereafter, of course, so that subsequent queries do not have the same overhead; it is usual to open database connections when a workbook file is opened, and close them again during the Workbook_Close() event).

If using ADO-based SQL statements on Excel worksheets or Access tables, then the syntax for finding unmatched records & missing data will be the same (as opposed to the Excel VLOOKUP, COUNTIF, and similar lookup functions, if the native Excel worksheet facilities are being used).

The speed of execution will be negligible, to be honest.  The speed of development will be slightly faster in MS-Excel worksheets, but you will be limited to Excel's storage capabilities.

BFN,

fp.

great answer. Just to make sure I get it, are you saying to continue using excel and exporting all the data from excel into access tables for reconciliation using SQL queries?

Thanks,
Nickel

Hi Nickel,

If the quantity of rows of data will exceed 65,536 then, yes, you will need to export data into Access to perform SQL queries.

I'm guessing you will export data daily, weekly, monthly, etc and then remove the current content from your worksheets (to make way for new data) so that the physical row limit is not exceeded.

However, if you will only ever have fewer than 65,537 rows, then you can use SQL statements directly within your Excel worksheets.

For example, if your file is called "c:\P&L.xls" and you have a worksheet called [Sales], then you can use an ADO connection string directly in MS-Excel (within c:\P&L.xls) such as:

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\P&L.xls; Extended Properties=Excel 8.0;"

Your SQL statement would then be of the form:

strSQL = "SELECT * FROM [Sales$]"
or
strSQL = "SELECT * FROM [Sales$SheetLevelNamedRange]"
or
strSQL = "SELECT * FROM [Sales$A1:D10]"

By default the Jet provider assumes that the first row contains field names, if this is not true, then...

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\P&L.xls; Extended Properties=Excel 8.0; HDR=No;"


You could, of course, use VBA in Access to perform this function, i.e. "pulling" data from your MS-Excel worksheet(s), or use Excel to "push" data in Access by writing insert queries based on select statements from your worksheets.


BFN,

fp.

Thanks for all your help. Your're answers were great.
I presented some of these ideas to colleagues.
I think we are going to move ahead with this.

Thanks,
Nickel

Glad we could help out, Nickel.

Thanks for the points/grading.

Happy codin'.

BFN,

fp.