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."

 

[Pat Hartman]

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.

 

[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?

 

[Pat Hartman]

Whenever the data does not need to be real time, as this does not, there are very easy methods to upload data that can wait until the driver is back in the office or at home with a WiFi connection. The upload is STILL batch. The driver sends a spreadsheet or something else using email or FTP. The Access FE reads the uploaded files from the upload directory, processes them, and then archives them. If the driver has a WiFi connection via a hotspot, he STILL does not need to work directly with the Access FE/BE. He can still upload files to be processed later in batch.

One of my clients ended up spending MILLIONS of dollars to be able to do surveys in real time rather with an Access FE running on the case worker's laptop which could be uploaded later. There was absolutely no reason whatsoever for this process to be real time. NOTHING was going to happen based on the data collected until it had been analyzed by a TEAM of experts which would always take a few days given the queue that was always pending. But NO, the "EXPERTS" and the fools who bit bought new iPads at a thousand dollars a pop plus hot spot capability at the rate of $50 per month, per case worker FOREVER instead of just using the existing hardware and connecting when they got back into the office. So, they could have had the new questionnaire application for ~ $300,000 (probably less but the state is always a real PITA to work with so maybe even more) and have it delivered in 6-12 months. But NO, they needed it to be real time and run on an iPad so they paid over $3 million (of my tax payer dollars), took 3.5 years AND incurred an annual expense that ran over $500,000 to pay for the hotspots and maintenance for the web app which was built on non-normalized tables and so required monthly changes or worse. AND could only handle a SINGLE questionnaire!!!!! The Access app was properly normalized and could have handled any number of questionnaires and been updated by the users rather than requiring FE and BE changes made by a team of programmers.

Access obviously can't be all things to all people but you need to know your tool AND you need to understand the requirements of the project you are building. If all you have is a hammer, everything looks like a nail. Expand your understanding of the available tools and if the one in your hand can't do the job, pick a different tool.

 

[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

 

[Pat Hartman]

There are numerous ways to do this. The driver isn't going to have access to SQL so you would write the code in the remote app. When the Surface is connected to the office LAN (preferably wired although I don't know how to tell), then the driver pushes the button to update the production BE directly. When the upload is finished. the code can log in a table which is linked to the production BE, the details of the upload. Date, Time, user, record range. So, at the start of the upload, the code would have checked the log table to determine if the data had been received. This also assumes you are doing appends rather than updates. The production BE table should keep the ide from the driver's local table and make a Three-field unique index. SourceComputer, UploadDate, RecordID. That will prevent duplicate updates.

Until the FE is backed up, you might not want to acrually delete the data from the driver's PC. So maybe instead of a direct update, you export to a .csv file and then transfer the csv file to the server and run the update using the exported file. This allows you to keep a copy of the .csv on the Surface as well as a copy on the server. Then you can clean out the data from the driver's copy and compact to be ready for the next day. During the upload process, you could check for old transfer files and delete them from the Driver's Surface to keep the clutter down.

You can also run a procedure each day on the in-house FE that checks the log to see if all of the drivers reported in and uploaded the data for the day before.

So, I'm leaning toward keeping a log file in the Driver's FE that isn't cleared when the other data is and using a .csv export. Then running the import from the in-house FE and keeping a log there also. This seems to give you the easiest way to keep backups of the transferred data and to account for missing transfers. If you want to keep the local log in a separate BE, that would allow you to just replace the driver's db rather than having to delete the data and compact.

Anyway, that's just a few thoughts.

 

[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 ..

 

[Pat Hartman]

Email is great if the Tablet will never be able to connect to the LAN where the main app runs. That separates the export process from the import process.