Basic Web Access to Small Data

[Thales750]

I'm hoping to start the 12 millionth conversation on SQL, the Web, and of course, MS Access.

Here are the conditions, we have an Access Database that runs on SQL. We use if to management construction projects, it's pretty fully developed with a couple of hundred tables several thousand queries. It manages the entire process.

We want to create the minimum, ultimate minimum, and free or cheep way to create a mobile piece that will provide field workers with a simple list of live data they can read and write in real time. this will represent data exchanges measured in kilobytes.

It does need to be part of a system that can be scaled up, but that comes later.

Minimum Requirements:
List of projects, select one, or GPS selects for you.
Check List
Vendor List
Contact List
Dial phone, send text, and email managements. This can all come later.
Basic with little or no latency on modern cellular networks.

How would you even get started?
Thanks in advance folks.

 

[plog]

The adage is: Cheap, quick or good--you can have at most 2.

If you exclude cheap you can hire someone who's done this sort of thing before.

If you exclude quick you can start learning how to do this yourself.

If you exclude good, you can either slap something together this weekend that poorly does one of those requirements you mentioned or hire the lowest bidder you can find.

So, the question is do you want to do this yourself or hire someone else?

 

[CJ_London]

Agree with plog - nothing to do with the web is free. If you develop yourself (and you work for free?), you’ll still have licensing costs.

might be if you invest in dataverse you can accomplish what you want

 

[Edgar_]

I know no simpler database setup than Firebase.

You basically enter firebase.google.com, then you log in with your Google credentials, click "get started", create new project, wait a few seconds, go into realtime database and boom, a JSON database in less than 2 minutes from beginning to end.

Access can send data to it via HTTP (easy vba) or the web browser control (advanced vba + js), you'd just need a front-end that can consume this data. Choose: web app or mobile app. You can easily launch a web app, in minutes actually, knowing myself, I'd simply go into VS Code, start a Vite + React single page app (that's one command in the terminal), then I'd create a few routes, write the views with antd, fetch that Firebase data, then another command in the terminal to build the app, then another command to deploy with surge and boom, free web app, no hosting. If mobile, that's trickier, requires more setup, but can also be done fairly quick choosing the right tools. Not too sure how to grab GPS data though, I've never used the GPS in my apps, maybe some day.

Access <------> Firebase <------> Web app

 

[GPGeorge]

I'm hoping to start the 12 millionth conversation on SQL, the Web, and of course, MS Access.

Here are the conditions, we have an Access Database that runs on SQL. We use if to management construction projects, it's pretty fully developed with a couple of hundred tables several thousand queries. It manages the entire process.

We want to create the minimum, ultimate minimum, and free or cheep way to create a mobile piece that will provide field workers with a simple list of live data they can read and write in real time. this will represent data exchanges measured in kilobytes.

It does need to be part of a system that can be scaled up, but that comes later.

Minimum Requirements:
List of projects, select one, or GPS selects for you.
Check List
Vendor List
Contact List
Dial phone, send text, and email managements. This can all come later.
Basic with little or no latency on modern cellular networks.

How would you even get started?
Thanks in advance folks.

"We want to create the minimum, ultimate minimum, and free or cheep way to create a mobile piece that will provide field workers with a simple list of live data they can read and write in real time. this will represent data exchanges measured in kilobytes."

That sounds a whole lot like PowerApps to me. It's probably not the cheapest options, but it will do the job very well.

You can create an interface that runs on a tablet, a smart phone or in a browser. You can connect it to your existing SQL Server tables*.

This type of application is often referred to as a hybrid, because the single database works with two or more interfaces.

I've created a number of videos documenting various aspects of this approach. Here's one that seems particularly applicable.

=====
*Depending on whether it's hosted or on premises, this will be easier or harder to implement.

 

[Pat Hartman]

Depending on what data has to be exchanged, you might get by with using SharePoint. SharePoint is not viable for large files however. But, you can recreate SharePoint tables overnight for what your people in the field are working on that day.

@GPGeorge How does that work with locally hosted SQL Server? Don't you have the issue of "inside the wire" vs "outside the wire" With your third party hosting company, everything is "outside the wire"

 

[GPGeorge]

It is possible to create an on-premises Gateway to support it.

Actually, at one point I explored it, but found it was not really useful for my purposes and didn't go very far with it. However, it can be done.

It would also be possible, I think, to implement the method I used for the Bar Code Scanner piece in the Northwind Stock Take demo I created. All of the existing local Access tables were intact. I added one Dataverse "Bridge" table to collect the Stock Take data via a PowerApps application with the scanner. In Access, that "bridge" data is appended to the appropriate Access table. It is a proof of concept to implement what I think of as the real value of hybrid applications. Only that one function which requires off-premises connectivity is supported in that mobile app. It is a data collection tool only. That seems to fit the OP's requirement here.

Licensing costs are a factor, but the technical implementation is there.

 

[GPGeorge]

Depending on what data has to be exchanged, you might get by with using SharePoint. SharePoint is not viable for large files however. But, you can recreate SharePoint tables overnight for what your people in the field are working on that day.

@GPGeorge How does that work with locally hosted SQL Server? Don't you have the issue of "inside the wire" vs "outside the wire" With your third party hosting company, everything is "outside the wire"

Your suggestion of SharePoint as a "Bridge" to support the off-premises data collection is parallel solution, I think. I used Dataverse because SharePoint makes me queasy, but it would work.