ACCESS suitability for multiple column "TEMPLATE" concept

[heathball]

I have been reading different opinions on ACCESS. I come from an excel background, need more scale, and will utilize storage options.

i want to develop a "template" concept. Use, record outcomes, use again, many times
large "file" or "table" is created, eg...
700,000 rows/records
perhaps 500 columns/fields
formulas/vba codes remain open (don't need to re-apply etc) (I know this is not database speak)
various reference columns (names etc) are changed each time a "template" is opened (table is changed, Probably copied? but formulas remain same)
I make minor changes if needed to formulas/codes etc.
i then record parts of the result/outcome, which are spread across many columns (database probably has different names for everything)


not super complex, no external data required, simple math/formula connectivity within the database itself.
It seems the direction would be via "queries" if this is to be acheived

I would like to know some opinions on the suitabilty of ACCESS for this type of task.
I need to create a slightly different formula for each column/field. (I don't know how long it takes to create 500 queries? Im assuming i will end up with vba codes for this)
Is a database thought of as a solution for this need?
I have tried some other options. Gigasheet, Power Bi. Only partial suitability for different reasons. Excel does not enjoy template concepts with big files

If anybody is aware of any links to information which could be related to the above, i would greatly appreciate it.

Thanks in advance for any contributions.

 

[Micron]

My first thought is that you should research db normalization. That's because I suspect your "Excel brain" is going to mislead you with respect to good design concepts. The first clue for that is that you would have 500 fields in a table (I have to assume you meant one table). Not only is it not possible in Access (the limit is 255 IIRC) I have yet to see any table that needs 255 fields. Excel and Access use different data layouts; Excel is "wide" (many columns) while Access is "tall" (data in rows, related data is in other tables).

700k+ records would not be a problem in any table, but the rest of your ideas makes me think you might reach the 2GB file limit. The number of queries you expect to need suggests you would be on the wrong design path there as well and that would certainly add to your file size.

How you can expect formulas to deal with field names that change every time you open a file is a bit of a puzzle, not to mention that I don't think that it is possible to make such design changes on the fly.

Whatever the db is for, perhaps someone has already developed a template that is close to what you need. You might try searching for those as well.

 

[heathball]

Thanks @Micron. Some of my assumptions are some way off reality, especially the field limit, and the max file size.

Does the concept of field "limit" work the same way as some other systems, whereby as you get very close to the limit, smooth operation/stability reduces?
In excel for example, the limit is close to 1m rows, but with 1m rows, you don't need many columns for excel to be under pressure/slow etc.

 

[Micron]

A lot depends on how well things are designed. Access can easily handle tables with hundreds of thousands of records under the right circumstances. Put too many indexes (or not enough) on tables or use domain level functions in queries and things will slow down. Build too many forms/reports/queries or put attachments in table fields and the file size will balloon. By too many forms and such I mean like when people have a form for read only, another one for edits, one for the boss, etc. and they all load records from the same table. Same with reports - one for sales for each year/month instead of presenting only the records that apply. Then there is network traffic in business applications, hardware capabilities and the like. If you will be dealing with millions of records one of the free or even licensed versions of sql server type back ends (e.g. Microsoft SQL, MySql, Azure (?not sure about that one) could contain your records and you could use Access to build the front end GUI to connect. If you will be developing for an employer, consider discussing your needs with whoever manages the network or its supporting information services. Note that a lot of IT types don't like Access; mostly because they don't know it and hate ending up being sacked with fixing everybody's Access problems when Access gets unleashed in a corporate environment.

 

[heathball]

thank you @Micron. One paragraph. but there is a lot of information