Is access file really fine as backend database?

[VAer]

I am talking about Access file, not Access server. I know there is 2GB limit for each table, but I am worried if Access file can handle query if the file grows bigger than 30MB...




Does anyone have experience using Access file as backend database ? Just a small application.

It is work PC, and I am not allowed to download anything.


Thanks.

[Micron]

I've never heard of an Access "server" so I can only comment on a file (.mdb, .mde, .accdb, .accde). I left run time files out of that because I don't think it will apply to what you're asking. Those files are Access database files of one type or another. It doesn't matter if you put everything into one db/file or whether you split a file into a front end/back end setup as far as your question goes. As for 30Mb, that is small stuff.

The 2Gb limit is for the ENTIRE file/db and includes overhead. If you reach 2Gb in either a fe or be you have reached the limit for the entire file. Whether or not it performs well when it approaches that limit depends on certain variables. Queries can perform poorly well before that limit if they and/or the tables are poorly designed. Things like too many indexes, lack of indexes, many and complicated calculations in queries, going across a network, etc. all contribute to poor performance. A well designed fe/be setup should be able to handle hundreds of thousands of records - if not millions. One thing that is sure to bloat a db is putting attachments into tables or embedding rather than linking images for controls in forms and reports. Attachments are best handled with links or else use some type of SQL Server platform.

If you want to use some other application to get at data in an Access be, then I'd say any constraint will be imposed by that app.

[ssanfu]

We had an Access FE and BE on a LAN. The BE had approx 35 tables; one table had 22 fields and ended up with approx 1.5 million records. The BE file size was 350mb.
Obviously, the more records, the slower the reports were generated. Same for forms, but the forms loaded faster that the reports. Forms and reports used queries as their record sources.

As Micron indicated, there are many things that would have an affect - How much RAM is available, how large is the HHD, how many other programs running, processor speed, how many users were in the dB at a time, etc.

But, all in all, Access FE/BE worked great for us.

[isladogs]

I've used both Access BEs and SQL Server BEs on a LAN for many years. In more than one case, the Access BE exceeds 1 GB. However once you reach that size it should be a 'flag' to consider upsizing to e.g. SQL Server.
As well as the important factors mentioned by micron & ssanfu, I would also like to stress the importance of:
1. limiting the number of database objects open at once to ensure you have plenty of connections available. If not currently in use close e.g. forms etc and set recordsets to nothing after use
2. indexing all fields commonly used in searches or for filtering data, The performance difference can be dramatic, especially for large recordsets
3. the number of simultaneous users - Access is usually fine up to about 20 users logged in at once but if you regularly reach/exceed that number, upsizing to SS will improve stability as well as security.
However, if you do upsize, performance may initially be worse. You will need to optimise your FE to make best use of the benefits of SQL Server

[VAer]

We had an Access FE and BE on a LAN. The BE had approx 35 tables; one table had 22 fields and ended up with approx 1.5 million records. The BE file size was 350mb.
Obviously, the more records, the slower the reports were generated. Same for forms, but the forms loaded faster that the reports. Forms and reports used queries as their record sources.

As Micron indicated, there are many things that would have an affect - How much RAM is available, how large is the HHD, how many other programs running, processor speed, how many users were in the dB at a time, etc.

But, all in all, Access FE/BE worked great for us.

350 mb? Wow, that is big, so it seems okay for me.

Thanks.

[VAer]

I've used both Access BEs and SQL Server BEs on a LAN for many years. In more than one case, the Access BE exceeds 1 GB. However once you reach that size it should be a 'flag' to consider upsizing to e.g. SQL Server.
As well as the important factors mentioned by micron & ssanfu, I would also like to stress the importance of:
1. limiting the number of database objects open at once to ensure you have plenty of connections available. If not currently in use close e.g. forms etc and set recordsets to nothing after use
2. indexing all fields commonly used in searches or for filtering data, The performance difference can be dramatic, especially for large recordsets
3. the number of simultaneous users - Access is usually fine up to about 20 users logged in at once but if you regularly reach/exceed that number, upsizing to SS will improve stability as well as security.
However, if you do upsize, performance may initially be worse. You will need to optimise your FE to make best use of the benefits of SQL Server

1GB for one single file only? How long does it take you to open the file (if I want to do some maintenance work)?

What if access file corrupted? If that happens, does it mean data lost? Did it ever happen to you? How reliable is using access file as backend database?

Thanks.

[CJ_London]

How long does it take you to open the file (if I want to do some maintenance work)?

assuming you are talking about the BE, then no time at all subject to it's location - on a slow network perhaps a few seconds. Access is not a variation of excel, it initially loads nothing into memory except the schema. Excel on the other hand has to load the entire file.

What if access file corrupted? If that happens, does it mean data lost?

usually, depends on how corrupted. Corruption usually occurs because developers/users do not follow the rules about how to use access.

Did it ever happen to you?

yes - when I didn't follow one of the rules (in my case, working wirelessly from a laptop). This is solved by the standard practice as applied to any data based app - you take regular backups and you regularly compact/repair to keep everything healthy.

How reliable is using access file as backend database?

as reliable as any other file based rdbms. (sql server et al are server based and a different animal). I've developed perhaps a hundred access apps over the last 20 years or so for small 'one man bands' to multi national businesses. It is a very flexible tool but reliability comes with the caveat the developer knows what they are doing

Suggest you google for access reviews if you are trying to find out about reliability and whether it suits your requirements. For example if you have a high data security requirement, then an access back end is probably not for you because it is file based - someone could simply delete the file or copy it for their own purposes. There are things you can do to make this difficult but that is not the topic.

Access is a RAD tool - it is more about the front end (forms/reports etc) communicating with one or more data sources which might be a jet/ace backend (which comes with access and is what many think of as being access) or any odbc rdbms. You can also link to other data sources such as excel/word/outlook/text files to name a few common ones.

[VAer]

Thank you very much!

[isladogs]

1GB for one single file only? How long does it take you to open the file (if I want to do some maintenance work)?

What if access file corrupted? If that happens, does it mean data lost? Did it ever happen to you? How reliable is using access file as backend database?

Thanks.

My largest Access BE in current use is over 1.6 GB with almost all of that taken up by one large Postcodes table of over 2.6 million records (and almost 50 fields):



It loads instantly. Searches on the Postcodes table are also instantaneous due to use of indexing on the most important fields.
Normally a 1.6 GB Access file would be a big red flag...
However, the overall file size isn't an issue in this particular case as its purely a reference database - effectively read only to clients - though periodically I issue an update when UK postcode info changes significantly.
As its never written to, the file never grows. Corruption has never yet happened & highly unlikely to ever do so. In this case, the database could easily be replaced with a fresh copy.

One issue with distributing a database of that size is that I cannot encrypt it before zipping. If I did so, the zipped file would also be 1.6 GB (228 MB unencrypted).
However, as the data is available publicly from a UK government website, encrypting that is pointless anyway.