Access vs Comercially available databases

[DickyP]

Security is all relative. Access is weak but probably good enough in most situations, whereas keeping all your financial details on your mobile phone is never safe.

As we were told at GCHQ - security doesn't need to be good - it needs to be good enough. At its simplest if your threat horizon is for 30 minutes then anything you can complete in 20 is OK.

 

[AccessBlaster]

The two main reasons people choose Access are its ease of use and the fact that it’s already installed at their organization. Security isn’t even considered until the IT department says “NO.”

 

[NauticalGent]

The two main reasons people choose Access are its ease of use and the fact that it’s already installed at their organization. Security isn’t even considered until the IT department says “NO.”

Exactly, even though Excel is being used for the same thing and holds the same "sensative" data.

I dont mind people being stupid, but be consistent with your stupidity

 

[Isaac]

Each user has an individual account but I pick the password so he doesn't know it. His windows login won't work. He logs into the Access app using whatever password he choose and the app logs into the server and links the tables using a password that only the DBA and I know. There are two ways to do this. Either a hardcoded password which the DBA and I must change frequently or a generated password. I give the DBA a database he can use to generate a password for the user and I use the same code. There are lots of ways to generate a password that is based on a string that is the user's login. Corporate logins are assigned by IT and so I start with that value. Then I make the password 30 characters and append a string to the end of the login string with enough characters to bring it up to 30. Then I pick some method of choosing characters from that string in some order to create the password. So even from the left and odd from the right or whatever. The appended string should contain numbers and any special characters that the server password rules allow.

When the app closes, the tables are deleted to remove the links although the password isn't saved in the link.

Yes, that's called SQL authentication as far as the auth method, and also known as service accounts, esp. if they don't know the PW.

 

[AccessBlaster]

Access was never designed to be or to create enterprise level applications. This is why many users feel frustration about scalability. It's awesome if people recognize It's limitations.

 

[Isaac]

Access was never designed to be or to create enterprise level applications. This is why many users feel frustration about scalability. It's awesome if people recognize It's limitations.

I have found the multi-user aspect to be more or less unlimited as long as the back end is SQL server

 

[AccessBlaster]

I have found the multi-user aspect to be more or less unlimited as long as the back end is SQL server

Isn't it SQL at that point, with an Access UI.

 

[The_Doc_Man]

Access was never designed to be or to create enterprise level applications. This is why many users feel frustration about scalability.

Maybe, but by splitting into an Access front-end and an SQL engine on the back end, you CAN very EASILY reach enterprise-level apps. Such as the Navy App I mentioned earlier... one of MANY possible actions. It is only the native Access back-end that would interfere with enterprise solutions.

 

[AccessBlaster]

Maybe, but by splitting into an Access front-end and an SQL engine on the back end, you CAN very EASILY reach enterprise-level apps. Such as the Navy App I mentioned earlier... one of MANY possible actions. It is only the native Access back-end that would interfere with enterprise solutions.

Once you hook Access up to SQL Server, it’s no longer “just an Access database,” it’s a hybrid Access/SQL Server application.

 

[The_Doc_Man]

Once you hook Access up to SQL Server, it’s no longer “just an Access database,” it’s a hybrid Access/SQL Server application.

Even when you DON'T hook up to SQL Server, it's not "just" an Access DB, it is an Access GUI front end on an ACE DB, which turns out to be something used by Windows itself.

 

[DickyP]

Even when you DON'T hook up to SQL Server, it's not "just" an Access DB, it is an Access GUI front end on an ACE DB, which turns out to be something used by Windows itself.

Agreed - at root the Excel spreadsheet sheet that you see is still dao technology.

 

[Isaac]

"Unlimited" if SQL Server edition is not Express, and running on Windows Server.

Well yes, definitely the business is not expected to be running a trial version of ... anything

 

[AHeyne]

It should be mentioned that SQL Express does not include SQL Server Agent. You have to script database maintenance and backup yourself. That's a disadvantage in a professional environment.

 

[GPGeorge]

It should be mentioned that SQL Express does not include SQL Server Agent. You have to script database maintenance and backup yourself. That's a disadvantage in a professional environment.

True, but there are other backup solutions available, both free and paid.

For example, here are two selected at random, with no recommendation implied:

Free SQL Server Express Scheduled Backup Utility

Free SQL Server Express Scheduled Backup Utility

www.sqlbackupmaster.com

SQLBackupAndFTP - SQL Server, MySQL and PostgreSQL Backups

SQL Server, MySQL and PostgreSQL Backups made easy with SQLBackupAndFTP. All Backup types, Encryption, Compression, Scheduling, Email notifications, Sending to Folder, FTP or Cloud

sqlbackupandftp.com

 

[Isaac]

SS Express is not a trial version. Some of our customers have been using it since year 2016 with the following server config we put together for them for under $5K. The servers run Linux with multiple KVM's. For the past 9 years, none of these customers have experienced any hardware issues. These servers have on_board, battery backed, hardware based RAID 10 controllers.

View attachment 121430

It's basically a trial version , in that any serious business is not expected to be running it - although I'm fully aware that you can, and I have, too.