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.