Microsoft Access/SQL Server Hosted on Azure

Question

I am wanting to use Microsoft Access to create various dashboards/forms for inventory management, PO requests, and maintenance.  I feel confident in my abilities on the Microsoft Access development end.  I guess what I’m looking for is advice on how to distribute the front-end program to our employees to be able to utilize – this is what I need help with..  The back end would be utilizing SQL server as the database hosted in Azure.

Details

• Microsoft Access 365
• Microsoft Azure (to host SQL Server)
  • Pricing Tier: General Purpose - Serverless: Gen5, 1 vCore
• SSMS v19.0.2
• ~20 people would need to be able to use this, not all at one time. I would say at most 10 people would be using the Access front end at the same time.

I made a trial table & form to test out this process.  I’ve successfully been able to set up Azure with my SQL database and make a connection with SSMS.  I’ve also been able to link a table in Access to the SQL Server and successfully make changes both ways:

 

What I’m confused about doing and running into trouble with is how to build & distribute the front end of my Microsoft Access to users.  I’ve looked up many tutorials and tried many examples.  I’ve went into the VBA in Access to set up a digital signature.  I then saved the document under “Package & Sign”, and a different one as “Make ACCDE”.  I tried trialing the program out on a separate laptop that DOES NOT have Microsoft Suite or Microsoft 365 – I installed Access Runtime.  When I try to open the “Package/Sign” or the “ACCDE” file, it did not work.  I had to install a certificate in the trust directory.  After that, Access Runtime opened, but the screen was blank and did not show my form.  Hopefully all of this makes sense, but basically, I need help in how to close the loop in distributing a final Access “App” to my end user so that it is connected to my SQL server on Azure.

Answer 1

First question I would ask, (sorry if you already mentioned this as I didn't see it in your post although I really just skimmed through it) is where are all your users located? If they are all in the same building, and you're all connected to the same local network, then the approach is simple. It would be more complicated if all the users are spread out at different locations and only have access to the database through the Internet.

Answer 2

First question I would ask, (sorry if you already mentioned this as I didn't see it in your post although I really just skimmed through it) is where are all your users located? If they are all in the same building, and you're all connected to the same local network, then the approach is simple. It would be more complicated if all the users are spread out at different locations and only have access to the database through the Internet.

Thanks for the response! I guess I didn't mention that...mostly on-site, but there are some individuals that would need to connect offsite as well.

Answer 3

Each user needs to have a copy of the Access FE (which I assume will be an accde) on their own computer, along with the Access runtime installed.

To use it, you need to create a shortcut on the user's computer which launches the accde, using the runtime. The runtime by itself doesn't have a way to select and open an accdb/accde.

That means if you can't physically touch a user's computer, you'll need to provide them instructions on installing the Access runtime and copying your shortcut and accde/accdb to the appropriate folder on their computer.

Some Access developers invest in developing an installation package, using a tool such as Inno(there are others; some may be easier or harder to use).

Does that address the question?

Answer 4

After that, Access Runtime opened, but the screen was blank and did not show my form.

When you create an ACCDE, you pseudo compile the application. This removes any ability to access the design elements of the app. This means that you, as the developer, need to set up a user interface that allows the user to navigate within the app. You say it didn't show your form, what did you do to have the form open when the app opens?

Answer 5

Since you are using Azure as your backend, it makes no difference whether users are local or not, part of the same LAN or not, ...

As for it being blank, how exactly are you opening the form?

Have you ensured that your VBA code compiles without errors?

FYI, during development you can use the /runtime command line switch to simulate runtime and see how your database should run. You can learn more at: https://www.devhut.net/microsoft-access-command-line-switches/