Information needed regarding shifting from excel to access (1 Viewer)

harshadborde

Registered User.
Local time
Today, 17:44
Joined
Apr 10, 2017
Messages
17
Hi Team,

I am a new guy to access.

We are planning to shift all excel reports to MS Access. We have created a big excel template with around 15 tabs which calculates daily sales generated as summary. We manually copy-paste data in each different tabs, which then calculates required fields.


I have few general questions, it will great of you could answer.

1) If I make all data which is getting updated daily as a 'Live connection', will my all queries (data) get updated automatically? I don't know if refresh all works, because its grayed out most of the times.

2) We don't have any backend as such. We just want to replace excel with access cause its difficult to generate daily reports in excel. So is it possible to store daily reports on access? How much is the capacity? I Read somewhere 2GB is limit, so limit is just for 1 access application or entire access on my laptop?

3) In the end we want reports in excel. How easy/ difficult is to generate reports automatically (refresh linked tables, queries, reports and send mails) at specific time daily? I have 4 linked tables, 10 interdependent queries and 5 reports in my app.

4) We choose access not because its free but because it is easy (visual access). Our requirement will never cross 50K records and 5-6 tables. Can we purchase some additional space?

Any help will be much appreciated.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:14
Joined
Jul 9, 2003
Messages
16,245
Please could you explain what you mean by "visual access" and what you are thinking when you suggest you might need to purchase some additional space.

Sent from my SM-G925F using Tapatalk
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:14
Joined
Jul 9, 2003
Messages
16,245
I recently looked at spreadsheet for a national bakery. The spreadsheet was for calculating the mixes required, haulage schedules and the like.

It had many interdependent formulas and wasn't funmentally​ tabular in structure. In other words it was unsuitable for moving to access, not without a significant redesign. I wonder if you have a similar situation.





Sent from my SM-G925F using Tapatalk
 

harshadborde

Registered User.
Local time
Today, 17:44
Joined
Apr 10, 2017
Messages
17
Please could you explain what you mean by "visual access" and what you are thinking when you suggest you might need to purchase some additional space.

Sent from my SM-G925F using Tapatalk

I wanted to say I use 'design view', which is alternative to writing SQL. If lets say 2GB is the limit and daily my report size is 20MB. So Can I only store only 100 reports or can somehow extend it?
 

harshadborde

Registered User.
Local time
Today, 17:44
Joined
Apr 10, 2017
Messages
17
I recently looked at spreadsheet for a national bakery. The spreadsheet was for calculating the mixes required, haulage schedules and the like.

It had many interdependent formulas and wasn't funmentally​ tabular in structure. In other words it was unsuitable for moving to access, not without a significant redesign. I wonder if you have a similar situation.


Sent from my SM-G925F using Tapatalk

Fortunately, I have managed to do all my calculations in access :) . I have designed queries and reports. Now I wanted to update all my data in excel and wants access to run queries and generate reports daily automatically.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:14
Joined
Feb 28, 2001
Messages
27,001
I will offer some answers (maybe).

1) If I make all data which is getting updated daily as a 'Live connection', will my all queries (data) get updated automatically? I don't know if refresh all works, because its grayed out most of the times.

The way ALL of these things work (queries, reports, forms, etc.) is that Access is a different way of storing things. Other than the raw data, Access elements are more like templates (which are relatively empty at the time) and small. The size of the internal report structure is hidden from us, but when the report is closed, there is no difference in size between a report for 100 records and one with the same format for 10,000 records.

The question of "live update" can be a problem. I don't know what you mean by that term. In Access, to update a table, you must be running MSACCESS.EXE (the Access image) at the time. You can tell Access to actively go fetch data from some source, but it won't do that until you tell it. Access is dumber than a box of rocks. It won't do anything you didn't tell it to do.

The "updated automatically" is easy enough to answer. When you open a structure in Access, if you have updated your underlying data and Access "knows" how to get it, everything opens with the data you have at that exact moment. If you update your tables, everything else uses what you just updated. Immediately. There is a wrinkle in that what gets displayed is static once opened. If you are using external sources and they get independently updated, you might have to go through a "Refresh" procedure to capture those updates.

2) We don't have any backend as such. We just want to replace excel with access cause its difficult to generate daily reports in excel. So is it possible to store daily reports on access? How much is the capacity? I Read somewhere 2GB is limit, so limit is just for 1 access application or entire access on my laptop?

As stated earlier, you don't store reports in an OPEN state. You generate them when you need them. That 2 GB limit is extremely hard to reach for the visible stuff like queries, forms, and reports, since they are stored in their collapsed states. If you are then storing data outside of Access (say, for example, in some .XLS files as one possibility), you would have a tremendous compression factor for the Access portion.

As to the direct question, each individual Access application is limited to 2 GB. If you have more than one application, each file is limited to 2 GB but you can have 1 or 10 or 100 separate applications of that size on your PC.

3) In the end we want reports in excel. How easy/ difficult is to generate reports automatically (refresh linked tables, queries, reports and send mails) at specific time daily? I have 4 linked tables, 10 interdependent queries and 5 reports in my app.

This will be difficult for you in a way. Access and Excel both are weak in terms of dealing with time. To do something automatically at a given time, there are two approaches.

Approach A is to assure that Access is open at the time and let some chosen form be your "driver." A timer (individual to a form) can be running that checks time of day and, at the appropriate time, starts the process rolling with code attached to that form.

Approach B is to make a Macro under Access that runs your processes and procedures for you. Then you would use Windows Task Scheduler to run a "command line" to launch Access on a specific database file with the command line /x:macro-name option. If so, that Macro must end with a .Quit so that you don't end up with a dangling copy of Access. You also need to THOROUGHLY debug the Macro because if an error stops the operation abnormally, you might not hit that .Quit action.

4) We choose access not because its free but because it is easy (visual access). Our requirement will never cross 50K records and 5-6 tables. Can we purchase some additional space?

The space requirement has been explained above. With what you describe, I seriously doubt you would EVER run across the problem of blowing out a 2 GB limit, particularly if you are talking about external data.

For Access the thing that "eats your lunch" for you is failing to properly maintain the files so as a result of this neglect, they get "bloated." If you create and delete a lot of internal data, a regular Compact & Repair operation cleans up any vestiges of data structures. This is because Access never deletes things while it is running. It merely marks them for deletion. The aforementioned Compact & Repair then does the actual deletion of marked objects and reclaims the space they took up.

What makes me a bit hesitant (and Uncle Gizmo also commented on it) is that changing from Excel to Access is a totally different way of looking at things. Access is able to manage multi-dimensional relationships. Excel tends to be very two-dimensional. The third dimension is possible since there is such a thing as off-sheet referencing, but there is a big conceptual difference. Not to be a nay-sayer, let's just say I advise caution.

Now there is another issue to mention at least in passing. You can CERTAINLY export a report to Excel from Access. If you do, it will be laid out like it was in Access but just will occupy a spreadsheet. The formatting might not be as expected. Stated another way, you are jamming "square pegs into round holes." Sometimes the fit is better, sometimes it is worse. It MIGHT be better for all concerned to not worry about whether the report is opened via Excel or Access. If everyone has a copy of MS Access, you could simply set up a front-end/back-end split (FE/BE) with data in the BE and all of the table definitions, query definitions, form and report templates, and code in the FE. Then you have some persons who drive the update and everyone else just opens the FE to be able to open the reports directly. They are printable, viewable, etc.

Note that in Excel, if everyone opens the reports, unless you have good discipline among your users, there is a risk of something getting changed. Similarly, in Access, you have the same risk if you share the same app file. (Same risk because the problem isn't the app, it's the people who use it, and THEY would be the same.)

I strongly suggest that you learn how to use the SEARCH features of this forum to look up topics. And you can ALWAYS come back for pointers and other help.
 

harshadborde

Registered User.
Local time
Today, 17:44
Joined
Apr 10, 2017
Messages
17
Thank you very much for providing your valuable guidance. Your answers are helping me a lot in connecting missing dots. :)


I will offer some answers (maybe).


The "updated automatically" is easy enough to answer. When you open a structure in Access, if you have updated your underlying data and Access "knows" how to get it, everything opens with the data you have at that exact moment. If you update your tables, everything else uses what you just updated. Immediately. There is a wrinkle in that what gets displayed is static once opened. If you are using external sources and they get independently updated, you might have to go through a "Refresh" procedure to capture those updates.

I have stored 4 excel files in one folder. The data in all 4 excel will be updated daily. I will refresh those 'linked tables' manually everyday. Now will the queries pick up data automatically from table and process or I need to do something else?
There are almost 10 interdependent queries and 10th is my final summary query. All I want is that query to give updated summary.

For Access the thing that "eats your lunch" for you is failing to properly maintain the files so as a result of this neglect, they get "bloated." If you create and delete a lot of internal data, a regular Compact & Repair operation cleans up any vestiges of data structures. This is because Access never deletes things while it is running. It merely marks them for deletion. The aforementioned Compact & Repair then does the actual deletion of marked objects and reclaims the space they took up.


In Access, we are preparing a summary of daily sales from almost 500 stores. Based on that, I want to streamline the process of daily summary for top management. They will need that in excel only.
But I also wants to store daily sales data. Where I can save this daily data in access if not in excel ?
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:14
Joined
Feb 28, 2001
Messages
27,001
There are two ways to do what you want.

One way is to externally link the to the Excel files. Then you use whatever is in them, but face issues of file locking because Access protects itself. It doesn't want users dinking directly with files that it is using as data tables. But - that way, you have no second step to update Access itself.

The other way is to build working tables into which you would import the four Excel spreadsheets you mentioned. In this case, there WOULD be an extra step, probably requiring some macros or some VBA (and I prefer VBA for various reasons). You would erase the prior contents of the working tables, then import today's data. To see what is involved, look up the "DoCmd.TransferSpreadSheet" command. TransferSpreadSheet occurs often enough in this forum to be pretty common, and MSDN will have a few articles on it too.

Doing it this "Import" way, you are taking a "snapshot" of the data in the Excel tables. What you produce will be correct as of the time of the snapshot.

You have to choose which way you want to go here, and both have their business-related problems. Using a direct-link method, you make the spreadsheets locked to other users while you are performing your summary operations. Using a snapshot method, if a data source arrives late, you have to either wait or make a note that you are not including certain data because of communications or other issues.

As to your management who wants Excel only, if you can build a query to make the Access structure you want, you could then use that TransferSpreadSheet to export the query to a new Excel file. Then if you need to add headers to it before you publish it, you can, because it will be an ordinary Excel workbook. Note that Access CAN produce a workbook with fancy titles in big, bold print - but not via TransferSpreadSheet. That function gives you raw data rows and columns and the first row CAN be your column header data. But that's all it will give you.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 08:14
Joined
Oct 17, 2012
Messages
3,276
I desired, I *CAN* provide a class module I built which allows you to export a recordset (query contents, basically) to Excel. You select the file location, file name, worksheet name, top left corner for the data, and whether or not you want headers and if you want the columns resized to fit the data. it even handles whether or not you want to automatically replace existing files or worksheets (if you don't select to replace automatically, it'll ask you if it encounters one already existing).

The problems here are that I have it at home, not at work, that it requires a reasonable amount of familiarity with VBA and class modules to use, and that if you want to understand how it works, you need to have rather more than a basic understanding of VBA or it'll just be gibberish.

Keep in mind that a LOT of it was basically me going "I wonder if I can have it do THIS", so not only is quite a bit of the functionality a duplicate of what's already done in TransferSpreadsheet, but it comes with quite a few bells and whistles that aren't often used. It was built out of curiosity, not for an employer, and is serious overkill in most situations.
 

Users who are viewing this thread

Top Bottom