Deployment Access FE and SQL Server BE (1 Viewer)

[JohnPapa]

I am about to deploy by first application that uses Access as a FE and SQL Server as a BE.

In the products that I have developed that use Access as a FE and ACE as a BE, I use the Runtime in this case for Access 2013. Installation is fairly easy and if I want to add a new field in the ACE BE I can do it programmatically, during an update.

For development of my current application I use Access in Office 365. Some thoughts and questions,
1) It may be unreasonable to expect the client to have Office 365, although this would simplify the installation process. Does it make practical sense to deploy a .accde created from an instance of Access 365 and risk incompatibilities, since Access 365 will continue to change?
2) It would be better to have a stable software development environment. Can I freeze a specific Access 365 version and use a corresponding Runtime, or would it be better to use say Access 2021 and use the corresponding Runtime?
3) Can I add new fields in SQL Server programmatically from the Access FE?
4) Can I have one installation script to install the Access FE Runtime and the BE SQL Server, or would the SQL Server need to be installed separately?

 

[sonic8]

1.) & 2.):
Use the oldest version of Access you expect any of your users to have to compile the ACCDE file.
All the rest is of minor importance. Of course, you shouldn't use any feature during development that not all of your users will have supported.

3.)
Sure. Send a DDL query to the server to perform any structural change and then relink the affected table(s).
However, there are two reasons why I would rather discourage this. 1. The user account running the FE must have permission to change the database structure. 2. What about concurrency with other users? One user/FE changes the DB structure. How do other connected users/FEs know about his and how should they respond if they know?

4.)
You can. I guess, there are a lot of examples of wrapping a SQL Server setup in your own setup and install it as part of your own software. This might be the way to go if you're deploying a single user application and install SQL Server (Express) on the local machine. However, I would strongly advise NOT to install SQL Server on any computer that is supposed to be used by multiple users as a sever. You must get some human administrator involved into the process to ensure there are proper backups configured and the network configuration is a sensible compromise between security and your application's requirements.

 

[JohnPapa]

Thanks for your comments.

From your reply for 1.) & 2.) I did not understand whether it would be advisable to deploy a .accde using Access 365 or use a Runtime with say Access21?

For 3) it is understood that an update would take place when only one FE is connected to the BE and that all FEs will be replaced. Can you please elaborate what you mean by DDL query?

For 4) I do not understand what you mean by

However, I would strongly advise NOT to install SQL Server on any computer that is supposed to be used by multiple users as a sever.

Isn't this goal?

 

[sonic8]

From your reply for 1.) & 2.) I did not understand whether it would be advisable to deploy a .accde using Access 365 or use a Runtime with say Access21?

I'd say that is of minor importance in a general context.
Of course your solution needs to take the situation of your users into account. If you deploy to a corporate environment where you can expect every user to have an existing installation of Access 365, then I would just use this existing installation and just deploy the ACCDE. If you target users that will most likely not have any existing Access installation, then you should either bundle the Access Runtime with your setup or make it easy for users to install that as a prerequisite.

For 3) it is understood that an update would take place when only one FE is connected to the BE and that all FEs will be replaced. Can you please elaborate what you mean by DDL query?

DDL = Data Definition Language. A DDL query is a SQL statement that is modifying the structure of objects in the database.

For 4) I do not understand what you mean by

However, I would strongly advise NOT to install SQL Server on any computer that is supposed to be used by multiple users as a sever.

Isn't this goal?

Yes, this might indeed be the goal.
I wrote this in the context of your question, whether it is advisable to include SQL Server in the setup of your application. In this context I advise against including SQL Server when it is to be installed on a computer in a server role.
A multi-user SQL Server installation involves ...
- Licensing
- Backup
- Storage Layout/Distribution
- Network configuration for general connectivity and for security
- User authentication / AD-Integration
All this needs to be carefully considered and should be done by a human administrator being competent and responsible for this task. It cannot be done by the setup of your application. So, don't even try.

 

[Pat Hartman]

In an installation where the BE is SQL Server, there is almost always an IT department and DBA's who would not ever allow you to update the BE this way. Yes it is technically possible but don't bank on being able to do it.

I would separate the installation of the FE from the BE. If you are using SQL Server as the BE, you are probably not installing a single user application. For a single user app, I would use ACE as the BE and I would wrap the installs into one script since both databases would be installed in the same folder.

For my clients, If the BE installed is SQL Server, I send them a script with DDL statements that they can run Off hours, after doing an appropriate backup. In a multi-user environment, you would never risk running such a script whether with passthourgh DDL queries or as a SQL server script. If the BE installed is ACE, I send an Access database that ensures the BE is the version I think it is and that then runs DDL queries. It also ensures that the update database is the only active user. It renames the BE before applying any updates to prevent accidental logins while the update is running.

So in both cases, the updates are done via DDL. It is just a matter of whether the script is run by a DBA or by an Admin user running my update database.

Because I do not have direct control over the BE in this situation, the FE must always ensure that the BE it links to is the BE it is expecting. You don't want the user to update the BE but not install your new FE.

 

[JohnPapa]

Maybe I can shed some light on my scenario. I will have direct control over the SQL Server. I will be able to log in remotely and ensure that no one is logged in when I make any changes to either the SQL Server or the .accde.

I will ask again whether it is advisable to make sure that all development is done using say A21 and the .accde is generated from A21 and the A21 Runtime is used, instead of relying on A365, which may be a moving target, due to changes that may be done when updating O365.

Also, for small installations, say 5 pcs, can I get away with not configuring the pc which will host the SQL Server database as a full fledged server and employ a powerful pc, but as a file server?

 

[Pat Hartman]

I would install the next to most recent run time. Stay off the bleeding edge when possible but stay as current as you can. You can work one version ahead when developing so you have some forewarning of impending issues.

Rename the .accde to .accdr before distributing. Use other lockdown options as you feel are necessary.

As long as you install SQL Server on a PC not being used as an individual workstation, it doesn't need to be configured as a server. You just have to make sure that the backup strategy is sound and is being implemented as you have scheduled it.

 

[JohnPapa]

I would install the next to most recent run time. Stay off the bleeding edge when possible but stay as current as you can. You can work one version ahead when developing so you have some forewarning of impending issues.

Rename the .accde to .accdr before distributing. Use other lockdown options as you feel are necessary.

As long as you install SQL Server on a PC not being used as an individual workstation, it doesn't need to be configured as a server. You just have to make sure that the backup strategy is sound and is being implemented as you have scheduled it.

Thanks Pat, very sound advice.

 

[GPGeorge]

Maybe I can shed some light on my scenario. I will have direct control over the SQL Server. I will be able to log in remotely and ensure that no one is logged in when I make any changes to either the SQL Server or the .accde.

I will ask again whether it is advisable to make sure that all development is done using say A21 and the .accde is generated from A21 and the A21 Runtime is used, instead of relying on A365, which may be a moving target, due to changes that may be done when updating O365.

Also, for small installations, say 5 pcs, can I get away with not configuring the pc which will host the SQL Server database as a full fledged server and employ a powerful pc, but as a file server?

Many years ago, before I retired, I set up a small office insurance with exactly the environment you describe. One PC, the most powerful in the office, was designated as the "server" with SQL Server installed on it. I also used it for development work for their application, and I think others logged onto it from time to time. It's main purpose was to support the SQL Server database, though.

There were four or five Access FEs in the office, used by different people for different tasks. Because it was not on a domain, we used a workgroup. It ran well for a decade or so. Then they migrated to SalesForce, spent a lot more money, and had a web-browser based application that could also support field agents.

That was also the most effective backup strategy I ever saw. The owner had two external hard drives that he swapped out every morning. One went home with him at night and stayed off-premises until it had its next turn on site. He was dogmatic about it, and of course, we never had to use one of the backups.

I never bothered to deploy accdbs accdEs although I might do so if I had to repeat the project.

 

[JohnPapa]

Thanks Pat, very sound advice.

Also I would like to mention that I am still using the A13 Runtime, which has served me very well. It supports Windows 7, 8, 10 and 11. One of my dentist clients has 15 Pcs running from an ACE db and the "server" is a $300 pc. He has 15 years of data and the db is on;y 200Mb. It is also interesting that the fields are bound, which makes development very easy. I also have the same software developed on SQL Server on Azure, which uses Web services, but that is a different story.

The reason for exploring SQL Server as a BE is a special property management software which I am developing which will create a lot of transactions, which ACE may be challenged in a few years, based on my calculations. I will see how it goes.

 

[Pat Hartman]

Also I would like to mention that I am still using the A13 Runtime,

You asked for my advice. I gave it. It was free so don't feel you need to take it if you know better.

The reason for exploring SQL Server as a BE is a special property management software which I am developing which will create a lot of transactions,

I have Access applications with ACE tables containing millions of rows. Of course, the most important thing is proper normalization and indexing. You also need to optimize your queries and form operations. Forms should NEVER be bound to naked tables or queries without criteria. Always include criteria so that all queries return the minimum number of rows and columns. At some point, you end up with too many users or too much data. It is never wrong to go straight to SQL Server. But, I have a an application that I sell. Some of my clients have only a few users and don't want their IT people involved but others have more than 30 so they will generate enough data to make SQL Server a better choice. However, the app has a single code base. It is delivered as one FE which can be installed with an ACE BE or SQL Server. All the code and queries work for both BE's. There is only one place where I had to code around the different BEs and that was an unusual situation. The app does healthcare insurance audits and comes with a test audit. In order to provide a consistent testing and training platform, the client can replace the audit data with the initial values each time he needs to train new people so that the training materials make sense. Because of the way SQL Server handles identity columns, I had trouble deleting the audit data and then reinserting it with the original identity column values. This is no problem with ACE but I had to use a different technique with SQL Server. I built it almost 20 years ago and there might be a better technique but I've never had to do anything like this for any other app because typically autonumber/identity columns have no meaning.

So, This application has the ability to hot-swap between ACE and SQL Server, not that the client would ever want to do that. Although, if he originally installed as ACE, he might later switch to SQL Server. That allows you to distribute now using ACE and upsize later without having to change any code.

I also told you how I handle distributing updates in a situation where I sell the application and so don't have direct control of the BE database whether it is ACE or SQL Server or any other RDBMS (I've also done custom installs for Oracle and DB2). You can create two methods, one where you do have control and one where you don't OR you can create ONE method that works in either case. Your choice.

 

[JohnPapa]

You asked for my advice. I gave it. It was free so don't feel you need to take it if you know better.

I have Access applications with ACE tables containing millions of rows. Of course, the most important thing is proper normalization and indexing. You also need to optimize your queries and form operations. Forms should NEVER be bound to naked tables or queries without criteria. Always include criteria so that all queries return the minimum number of rows and columns. At some point, you end up with too many users or too much data. It is never wrong to go straight to SQL Server. But, I have a an application that I sell. Some of my clients have only a few users and don't want their IT people involved but others have more than 30 so they will generate enough data to make SQL Server a better choice. However, the app has a single code base. It is delivered as one FE which can be installed with an ACE BE or SQL Server. All the code and queries work for both BE's. There is only one place where I had to code around the different BEs and that was an unusual situation. The app does healthcare insurance audits and comes with a test audit. In order to provide a consistent testing and training platform, the client can replace the audit data with the initial values each time he needs to train new people so that the training materials make sense. Because of the way SQL Server handles identity columns, I had trouble deleting the audit data and then reinserting it with the original identity column values. This is no problem with ACE but I had to use a different technique with SQL Server. I built it almost 20 years ago and there might be a better technique but I've never had to do anything like this for any other app because typically autonumber/identity columns have no meaning.

So, This application has the ability to hot-swap between ACE and SQL Server, not that the client would ever want to do that. Although, if he originally installed as ACE, he might later switch to SQL Server. That allows you to distribute now using ACE and upsize later without having to change any code.

I also told you how I handle distributing updates in a situation where I sell the application and so don't have direct control of the BE database whether it is ACE or SQL Server or any other RDBMS (I've also done custom installs for Oracle and DB2). You can create two methods, one where you do have control and one where you don't OR you can create ONE method that works in either case. Your choice.

Many thanks Pat. My mention to A13 was merely information on what I use today and had nothing to do with your suggestion. I cannot stay with A13 forever and I am making plans to move to a later version, per your suggestion

I never thought that a common (switchable) SQL Server - ACE backend would be possible.

 

[JohnPapa]

Many years ago, before I retired, I set up a small office insurance with exactly the environment you describe. One PC, the most powerful in the office, was designated as the "server" with SQL Server installed on it. I also used it for development work for their application, and I think others logged onto it from time to time. It's main purpose was to support the SQL Server database, though.

There were four or five Access FEs in the office, used by different people for different tasks. Because it was not on a domain, we used a workgroup. It ran well for a decade or so. Then they migrated to SalesForce, spent a lot more money, and had a web-browser based application that could also support field agents.

That was also the most effective backup strategy I ever saw. The owner had two external hard drives that he swapped out every morning. One went home with him at night and stayed off-premises until it had its next turn on site. He was dogmatic about it, and of course, we never had to use one of the backups.

I never bothered to deploy accdbs accdEs although I might do so if I had to repeat the project.

Thanks for your input.