Solved Migrate Form to 365, sharepoint or web?

[Garren.Shannon]

Hey all,

I am not sure this is the right place for this so let me know if there is a better location.

I have a small ridership database I am crafting for our school district. The tables are now hosted on our MS SQL 2019 server and the system runs. Most of the database is output is for the department admins. But input is almost totally on the drivers and is added via a single input form imaged below. Currently the drivers enter this data on a paper form and transfer the data into a spreadsheet at home base.

The drivers will be able to access the database like they do the spreadsheets. However, I think it would make more sense if I simply allow drivers to access JUST this form rather than the entire system. They really don't need access to everything else.

But I am also thinking maybe this is something they can run from their phone or from a tablet (we are looking into issuing a Surface Go to each driver for just this purpose) so they can start a new trip entry at the start of the trip... on the bus. And "Yes"... busses are wired with Internet so they will have access to the web.

The question is, should I be looking to 365, sharepoint or a web page for this??? I have worked with Access in the past but have never worked with Sharepoint forms, or power apps and have never worked a web page with a data backend. But I am willing to learn and stumble through.

So what would one suggest as the 1. easiest approach and 2. best option.

Thanks...

 

[The_Doc_Man]

A "true" web interface to an Access form is generally a non-starter. Other members have experience with Sharepoint that I do not have so I cannot give advice there. The only way to do a true remote operation involves something called RDP (Remote Desktop Protocol) which EITHER requires you to have a Windows Server 20xx as your central host, OR you need a product like CITRIX that uses RDP to establish a remote session. I cannot address the cost of those features having never been an administrator for either case - but I know they aren't cheap.

Search this forum for "Sharepoint" to get articles on the subject. Note also that in the "Similar Threads" section at the bottom of this thread you can find a couple of starting points. Several of our members have some Sharepoint experience and perhaps could tell you more.

Search this forum for "RDP" or "CITRIX" to see articles dealing with that option. Though others have indeed used it, Pat Hartman is our resident expert on CITRIX. She has considerable experience using it.

The "web" solution is just not the way to go unless you are planning to go ALL-web and convert your database into an SQL engine solution, totally abandoning Access. The reason is that web sites don't normally allow the protocols you need for Access front-end/back-end solutions - and a non-split solution is not merely a non-starter, it is a step into chaos.

Beyond this point I have to demur to my colleagues and hope they can offer more feedback for you.

 

[GPGeorge]

I am aware of at least two potential options, although I do think RDP or Citrix might be in first place as far as preferences.

Here are videos covering the two options. Coincidentally, I hosted one presentation my user group and gave the other presentation to the AUG Europe user group. There is a cost involved either way, so take that into consideration.

By the way, in both presentations, the concept explored is often referred to as a Hybrid Application, which I explain in tedious detail in my PA for Access Developers presentation.

Hosting Access Applications in the cloud with Caspio
PowerApps for Access Developers

 

[Garren.Shannon]

Thanks The_Doc_Man and GPGeorge. I am very familiar with both RDP and Citrix. I will review the video links so thank you.

A follow-on question here. If I extract the form and supportive queries and linked tables, is it possible to deploy just those feathers of this database on the Surface Go's that are attached to the campus wifi... or even deploy that portion of the database to other desktops in the district?

BTW George... you look a lot like me... sounds like you may have watched the Red-Green Show as well. "I'm voting for you. We're all in this together" and "Keep your stick on the ice." were catch phrases I loved.

 

[GPGeorge]

Thanks The_Doc_Man and GPGeorge. I am very familiar with both RDP and Citrix. I will review the video links so thank you.

A follow-on question here. If I extract the form and supportive queries and linked tables, is it possible to deploy just those feathers of this database on the Surface Go's that are attached to the campus wifi... or even deploy that portion of the database to other desktops in the district?

BTW George... you look a lot like me... sounds like you may have watched the Red-Green Show as well. "I'm voting for you. We're all in this together" and "Keep your stick on the ice." were catch phrases I loved.

You should be able to create a specific accdb FE with relevant interface objects, yes. However, Access over WiFi is not a very reliable solution. Also, if the Surface Go can run Access, you could use it. I'm not that familiar, though with that hardware.

Yes, I recite the pledge regularly. "I'm a man. But I can change. If I have to. I Guess."

 

[Garren.Shannon]

You should be able to create a specific accdb FE with relevant interface objects, yes. However, Access over WiFi is not a very reliable solution. Also, if the Surface Go can run Access, you could use it. I'm not that familiar, though with that hardware.

Yes, I recite the pledge regularly. "I'm a man. But I can change. If I have to. I Guess."

 

[Garren.Shannon]

Not sure what 365 is. Access does not run in the cloud, EVER. Even though Outlook, Word, and Excel do.

For a special purpose like this, the Access FE can link to both SharePoint (if your company already has it) as well as ACE or SQL Server as the main data store. You can get the updates and fill the Sharepoint List as necessary.

Thanks Pat... I got 365 and Power Apps mixed. I heard that Power Apps can be tied to a SQL BE though after looking deeper into it, it seems like a heavy lift on my end... one I am not ready for. I will go the FE route and get just the trip form working as a stand-alone app tied to out SQL for our drivers.

 

[Garren.Shannon]

You should be able to create a specific accdb FE with relevant interface objects, yes. However, Access over WiFi is not a very reliable solution. Also, if the Surface Go can run Access, you could use it. I'm not that familiar, though with that hardware.

Yes, I recite the pledge regularly. "I'm a man. But I can change. If I have to. I Guess."

GPGeorge,

Back in the day (1990), I use to support AT&T 3B2 sub-stations for data lift to Data General mainframes when I was in the USAF. It seems maybe a stand-alone app that can "upload" trips to the main DB would not be a bad idea.

Since the buses are often outside network service ranges, maybe a stand-alone data gathering app with an upload option would be better?

Anyone have an opinion on that approach?

 

[Isaac]

Hey all,

I am not sure this is the right place for this so let me know if there is a better location.

I have a small ridership database I am crafting for our school district. The tables are now hosted on our MS SQL 2019 server and the system runs. Most of the database is output is for the department admins. But input is almost totally on the drivers and is added via a single input form imaged below. Currently the drivers enter this data on a paper form and transfer the data into a spreadsheet at home base.

The drivers will be able to access the database like they do the spreadsheets. However, I think it would make more sense if I simply allow drivers to access JUST this form rather than the entire system. They really don't need access to everything else.

But I am also thinking maybe this is something they can run from their phone or from a tablet (we are looking into issuing a Surface Go to each driver for just this purpose) so they can start a new trip entry at the start of the trip... on the bus. And "Yes"... busses are wired with Internet so they will have access to the web.

The question is, should I be looking to 365, sharepoint or a web page for this??? I have worked with Access in the past but have never worked with Sharepoint forms, or power apps and have never worked a web page with a data backend. But I am willing to learn and stumble through.

So what would one suggest as the 1. easiest approach and 2. best option.

Thanks...

View attachment 104637

How about none of those and just use access? Didn't you say you're issuing them a surface?

 

[isladogs]

A few years ago, I released an Access app with similar intentions.
It is designed for use on Windows tablets including offsite use (as well as on desktop computers):

UK Postal Address Finder

The primary purpose of this app is to obtain a full list of postal addresses for selected UK postcodes. It also has many additional features including postcodes from geolocation, nearby place searches, route maps and travel directions

www.isladogs.co.uk

The offsite part can be used for route planners, signature collection, downloading maps etc
That all works well providing you have an internet connection.

However, I made sure that users could only transfer data between the LAN BE & tablet when back at base using a wired connection.
The only exception to that is that customer signatures are emailed back to base as image file attachments

Having said all of that, after watched George's demo of a hybrid app, I'm considering doing something similar with my own app.

Anyway, coming back to the task outlined in post #1, have you considered creating that form in Excel with the intention that it would be used with the online version of Excel. Would that allow staff back at base to transfer the Excel data to the Access database?

 

[Garren.Shannon]

A few years ago, I released an Access app with similar intentions.
It is designed for use on Windows tablets including offsite use (as well as on desktop computers):

UK Postal Address Finder

The primary purpose of this app is to obtain a full list of postal addresses for selected UK postcodes. It also has many additional features including postcodes from geolocation, nearby place searches, route maps and travel directions

www.isladogs.co.uk

The offsite part can be used for route planners, signature collection, downloading maps etc
That all works well providing you have an internet connection.

However, I made sure that users could only transfer data between the LAN BE & tablet when back at base using a wired connection.
The only exception to that is that customer signatures are emailed back to base as image file attachments

Having said all of that, after watched George's demo of a hybrid app, I'm considering doing something similar with my own app.

Anyway, coming back to the task outlined in post #1, have you considered creating that form in Excel with the intention that it would be used with the online version of Excel. Would that allow staff back at base to transfer the Excel data to the Access database?

Sorry for the delay in responding. I have been distracted by a $1million dollar grant I landed to build a fixed wireless network in our area... but I am back at this for right now.

As I noted, it has been many years since I worked with data and things have both changed and stayed the same... I guess data is data. However, the tools to manipulate that data have improved immensely. SO... after giving this some thought and ingesting everyone's input, I am thinking the best way to approach this is to have the Surface Go's run a local copy of just what drivers need to enter their trips, and provide a button to uplift on demand.

This generates a few more questions. 1. How can this be handled? 2. Should I keep an archived copy of the upload on the local tablet or should I add a flag to each record after it has been uploaded?

Again... I really do appreciate everyone's support here so thank you, thank you, thank you.

 

[The_Doc_Man]

I'm going to vote on the question in post #13. Add a flag to uploaded records after a successful upload. I'm staying out of the rest of the discussion because I have no experience with the Surface product.

 

[Garren.Shannon]

I'm going to vote on the question in post #13. Add a flag to uploaded records after a successful upload. I'm staying out of the rest of the discussion because I have no experience with the Surface product.

Thanks The_Doc_Man... that's what I was leaning to as well. Decades ago, this would be done via transaction tracking. In access, I am assuming this is an SQL command and I have to look for a True or False response from VB code to see if the command completed successfully or not. Am I guessing correctly?

 

[Isaac]

- code to re-link back end once the person is in range or connected
- code to run an Insert query (insert to linked table)
- code to run an Update query (update local table, same records updated)

or if you want to get more granular , open a recordset and insert each record individually and update it individually there is no chance that way of an uncertain fraction of records being updated / inserted before a failure of some kind.

that's about as specific as I'll get. the forum is a place to ask narrow questions on specific things, one question/thread at a time.
thus is how you balance your effort with forum help as well as how we all maximize a useful repository of specific labelled help for posterity

 

[The_Doc_Man]

Thanks The_Doc_Man... that's what I was leaning to as well. Decades ago, this would be done via transaction tracking. In access, I am assuming this is an SQL command and I have to look for a True or False response from VB code to see if the command completed successfully or not. Am I guessing correctly?

As to how that flag gets set, there are a ton of ways. But your comment makes me wonder what it is that you are discussing. I would have suggested that to set the flag once the upload worked, you would set this putative flag where the flag is FALSE and the PK is in the table to which the upload occurred.

However, on further review, I would wonder whether I misunderstood your focus. Is there ever a time when you would NOT download data, and under what circumstances should that happen. Because remember that when you use Access to "model" your business, you have to look at the real-world procedures to decide when to do something specific. Otherwise, you aren't modeling, you are muddling.

 

[Isaac]

Pat's comment makes me think of one more thing, which she kind of alluded to by implication.
But anyway my idea was - you could also circumvent the entire challenge of getting the data "from the tablet to the back end database" by emailing CSV's. This would be a snap to accomplish using Outlook on Surface, or CDO if necessary - you could easily create some automation that simply emailed the necessary data files for processing.

A separate process on the receiving side would save the attachments off of incoming emails and process accordingly, also a snap and lots of combined knowledge/resources/examples out there of how to do.

Just another way to think of it ..

 

[Garren.Shannon]

Hey all,

Circling back to this. I decided to create a sub-DB with just the trip database but linked with a dbo to the SQL stored master DB. I added an archive flag (Boolean y/n) field to the local trip DB. MY thoughts now are to add a button to the start page to allow the drivers to copy/append the unflagged (no) records to the dbo master, then change the archive flag to yes. I figure this will leave a control database on each drivers Surface Go that I can go back to if there were a problem.

Should I do this in VBA or try a macro?

 

[Garren.Shannon]

As to how that flag gets set, there are a ton of ways. But your comment makes me wonder what it is that you are discussing. I would have suggested that to set the flag once the upload worked, you would set this putative flag where the flag is FALSE and the PK is in the table to which the upload occurred.

However, on further review, I would wonder whether I misunderstood your focus. Is there ever a time when you would NOT download data, and under what circumstances should that happen. Because remember that when you use Access to "model" your business, you have to look at the real-world procedures to decide when to do something specific. Otherwise, you aren't modeling, you are muddling.

Pat... I think I understand what your asking so let me clarify. The master DB resides on our SQL server. Each driver will have a Surface Go with Access on it. They will have a stripped down version of the program with all the DB's and just a form for entry. On occasion with they are at the transportation center (and on the wireless network) they will click a button (that I provide) triggering a copy/append from the local trips table to the dbo_trips table and, assuming the append worked, then set the archive flag to yes for all selected records.

Does that make sense?

I still need to figure out how best to approach this... not sure I can do it via forms. I think the best way would be via code and using a local query. Just not sure how to verify the transfer worked prior to changing the flags.

 

[Isaac]

to allow the drivers to copy/append the unflagged (no) records to the dbo master

Wait, so you mean all this time the drivers' devices actually DO have connectivity to the SQL database? (I assume that's what you mean by "dbo master" although not really sure what that means)

If they do have connectivity to SQL Server, then you have no issues or problems in the first place, right?!

Confused...

 

[Garren.Shannon]

- code to re-link back end once the person is in range or connected
- code to run an Insert query (insert to linked table)
- code to run an Update query (update local table, same records updated)

or if you want to get more granular , open a recordset and insert each record individually and update it individually there is no chance that way of an uncertain fraction of records being updated / inserted before a failure of some kind.

that's about as specific as I'll get. the forum is a place to ask narrow questions on specific things, one question/thread at a time.
thus is how you balance your effort with forum help as well as how we all maximize a useful repository of specific labelled help for posterity

Hey Isaac,

Thanks for the direction on this. I copied the original DB (prior to migrating it to our SQL server), then removed all forms except the one the drivers will use. This leaves the core of the data individualized as it were. Then I added a dbo link to the SQL trips table. The drivers will be able to connect whenever they are in the office (daily) and I plan to add an "Upload" button so they can start an upload on demand. I don't know if trying to code an automatic upload would be the better way to go so for now, manually when they press the button.

On the local DB table, I added an archive flag and will set the flag as each record is inserted.

Wait, so you mean all this time the drivers' devices actually DO have connectivity to the SQL database? (I assume that's what you mean by "dbo master" although not really sure what that means)

If they do have connectivity to SQL Server, then you have no issues or problems in the first place, right?!

Confused...

Sorry for the confusion Isaac. They sporadically have access. The will have their Surface Go's in hand when they leave the station... they will have it when they return from their routes. The Go's will reside at the station over night for charging and will be carried and mounted in the bus during trips. The will NOT have access when they are on their routes - typically when they are out of cell service on county roads.

Sorry... I am an infant in the Access adult world. DBO is what I see when I link to a table in the SQL so that's what I am called it... but I think its just a linked DB right?

I think the DB drivers will access needs to be somewhat standalone so they can still put in data out on the road and not be required to be in wireless contact to do so. I suspect in normal operations, they will add multiple trips during the day or week and every few days, they will actually click the upload button.

Below is what I see. the file named with dbo prefix are the SQL copies of the tables. The others are local. The local trips table now has an added field called archive (Boolean) with the default set to false.

So to wrap this thread up, it turns out neither 365, sharepoint or web was the answer.

Hope that helps clarify our situation.