MS Access Over The Internet (1 Viewer)

[lhooker]

I have a MS Access database that has a form that collects data (i e. survey). I want to make this form available for use on the internet. The form creates an entry in a table, then the entry is appended to a summary table. How will MS Access work when this form is activated (on the internet) at the same time ? Will there be any conflicts when there are multiple users taking the survey over the internet ?

 

[Edgar_]

You need a website to collect that data, Access won't be able to do that. Access can, however, process the data you collect through the website. You would need to figure out how you want to gather that data from Access. There are many options for this.

 

[The_Doc_Man]

The protocols used by Access generally do not play well on web-based servers. If you actually make that connection between an end-user running Access locally on his/her workstation/tablet/whatever, the SMB protocol will have lots of trouble and might not be able to connect at all. You will invite corruption with high probability because web sites use protocols that don't care that the web connection might go down momentarily. They use a different method to maintain awareness of web-based connections than Access does for its LAN-based connections.

If this is something being done for money, a possible (and viable) cost of doing business would be some variant of RDP connections or use CITRIX to manage the connections - but neither one is free. People frequently claim to have something that works correctly over the web with Access, but Access on the Web is a deprecated product that was removed from standard Access over a decade ago.

 

[Gasman]

I would suspect a Google Form that places the data into a Google sheet, and Access accesses that sheet in someway, would be one way?

 

[GPGeorge]

The protocols used by Access generally do not play well on web-based servers. If you actually make that connection between an end-user running Access locally on his/her workstation/tablet/whatever, the SMB protocol will have lots of trouble and might not be able to connect at all. You will invite corruption with high probability because web sites use protocols that don't care that the web connection might go down momentarily. They use a different method to maintain awareness of web-based connections than Access does for its LAN-based connections.

If this is something being done for money, a possible (and viable) cost of doing business would be some variant of RDP connections or use CITRIX to manage the connections - but neither one is free. People frequently claim to have something that works correctly over the web with Access, but Access on the Web is a deprecated product that was removed from standard Access over a decade ago.

"... Access on the Web is a deprecated product that was removed from standard Access over a decade ago."

Actually, AWA's lasted until 2016, but otherwise, yeah, what you said.

 

[GPGeorge]

I have a MS Access database that has a form that collects data (i e. survey). I want to make this form available for use on the internet. The form creates an entry in a table, then the entry is appended to a summary table. How will MS Access work when this form is activated (on the internet) at the same time ? Will there be any conflicts when there are multiple users taking the survey over the internet ?

Check out this video, in which Shane Young talks about MS Power Platform tools, one of which includes "Forms", similar to Google Forms, but inside the MS eco-system. Given their pricing model, it might or might not be an option.

 

[The_Doc_Man]

Actually, AWA's lasted until 2016, but otherwise, yeah, what you said.

Out of sight, out of mind. Guess I forgot when it vanished since I never really used it much anyway.

 

[lhooker]

You need a website to collect that data, Access won't be able to do that. Access can, however, process the data you collect through the website. You would need to figure out how you want to gather that data from Access. There are many options for this.

Mmmmmm . . . I was told in past that SQL Server could provide access to a MS Access database application on the internet. It seems that SQL Server no longer (or never was) an option. Would someone provide me with the best option for a simple MS Access database application that only ask a user questions, then write their answers to a table in an internet environment. This MS Access application would accumalate the user's answers to a table. Lastly, it will also provide printing capabilities. Thank you ! ! !

 

[tvanstiphout]

> I was told in past that SQL Server could provide access to a MS Access database application on the internet
Not exactly, but backwards:
SQL Server can be on the internet (e.g. in some data center like MSFT Azure), and Access on the desktop can connect to that.
You will run at internet speed rather than LAN speed, but that may be good enough for your purposes.

 

[The_Doc_Man]

Access cannot do what you want with regard to remote data collection. SQL Server as a back end can be used by a web page designed to gather data. The web page will do this gathering but it is not Access. Once the data gathering is done, a copy of Access can connect to the SQL Server tables locally (i.e. via LAN) and perform your computations and reporting. Access as a front-end just does not do web stuff.

 

[Edgar_]

@lhooker
If you want your users to use their browser to respond your surveys, you need a website for that, any workaround for this is a headache and a hack. If you want your users to download an Access application into their desktops and send information to some database on the internet, that can be done.

 

[Pat Hartman]

@The_Doc_Man It is possible to connect an Access database on your local computer to an Access BE hosted by a third party provider. I would never to it but people create web pages to put data in Jet databases. I have also tried using Access connected to SQL Server hosted on a third party site. It is like watching paint dry but it works. People have reported better success with using Azure but the app is still running at internet speeds rather than lan speed so it is at least 10 times slower than an Access FE connected to any type of BE running on your LAN.

The question the OP hasn't addressed is - will these be KNOWN users or anonymous users? With anonymous users, your only option is a web page FE running in a browser. With known users, an Access FE can be used, albeit slowly.

@lhooker an email form or an excel form sent via email or a google spreadsheet or a shared Excel spreadsheet might be better options and not require paying monthly fees for a hosted solution.

 

[The_Doc_Man]

It is possible to connect an Access database on your local computer to an Access BE hosted by a third party provider. I would never to it but people create web pages to put data in Jet databases.

If you use ODBC methods that is possible. It is SMB protocol end-to-end that causes the issue - native access FE to native Access BE on the web. If there is an intermediary to handle the networking issues, then in theory it should be possible to manage a connection. My objections to "Access on the web" are narrow and specific.

 

[GPGeorge]

Depending on what your specific situation is, I would consider a hybrid application, using a combination of SQL Azure or hosted SQL Server, a web page for the data collection piece, and a standard Access accdb FE running on the desktops of each of your users. It is true that there is a performance penalty involved when moving data down from the database, but if you are not gathering hundreds of inputs a day, or gathering large amounts of data, that should not have a huge impact overall.

If the performance is unacceptable for an Access FE linked to the remote database involved in things like aggregating data for reports, or analyzing that data, or whatever purpose you have for gathering it, then I would look at a variation of Pat's suggestion. You can link the webpage gathering data to a SQL Server or SQL Azure table for data collection. Once an hour, or once a day, or on whatever schedule you need, you can initiate a passthrough query from Access to retrieve any new records added since the last previous transfer. I.e. pulling records based on a date/time stamp to pull in only new records. Once those records are stored locally in an Access accdb BE, you are working at LAN speeds.

I designed exactly that sort of process for a client many years ago. We retrieved data once a day from a website that collected inquiries from potential clients. In that case, in fact, we didn't even control the SQL Server database. The website admin gave us read-only credentials to connect to one non-normalized table and retrieve the data we needed. We then parsed it into our local properly normalized tables.

Again, the key is latency in getting the data back from the website. If it has to be immediate, then this approach is possibly not viable. It really depends on whether you need data from 3 seconds ago, 30 seconds ago, or if data from 30 minutes ago is adequate.

All of this, though, is somewhat academic. You can sign up for a free SQL Azure account and do some testing yourself.

 

[Gasman]

FWIW, we used to use an app on the wb called https://cloud.witsifa.co.uk/

This happened to be an app in an Access DB presented by Windows Server I believe?
After that I have no knowledge.

However it was more than acceptable for our usage.