Is Access the right solution?

[zeroaccess]

Hey all,

My team has a lot of functions that are done on spreadsheets and I'm eager to consolidate and simplify our processes with a new database. However, I want everyone to have access to certain things at any time via mobile devices. That may include various lists like a roster or financial log, drilling further to employee or transaction details, and adding records with a form. This is because increasingly we are not at a workstation when we need to access something. Right now, that is accomplished with Google Drive but I may migrate us to Microsoft 365 as we already have a subscription.

This means I'm questioning if Access is the right solution. I know how to make it work, and work well. But obviously there is no mobile compatibility or even cloud; Access is Windows and x86 only. It would be cool if MS were working on development of Access for 365 but I don't know if they are.

I'm not convinced that using MS or Google Forms is the answer, either. Those just save individual records to spreadsheets so are not the correct tool for the job. No relational data structure and no/limited recall of records in the form. And, no code.

What are my options?

 

[theDBguy]

If you're thinking of moving/migrating to M365, then perhaps you can utilize Flow and/or PowerApps (PowerFlow?). Just a thought...

 

[Galaxiom]

A web based application would be better. But the fact you are asking the question suggests it would be outside of your current skill set.

 

[zeroaccess]

A web based application would be better. But the fact you are asking the question suggests it would be outside of your current skill set.

Probably.

 

[Galaxiom]

Last year I wrote a simple Classic ASP site connected to an SQL Server back end. It only had three pages and was designed to download files from the database. It took ages because I had to look up the syntax of everything. At least some the VB code to connect to the database was almost portable from what I had used in Access. Setting up with to display and pass the filter criteria back to the server was quite involved.

It is certainly doable if you have the time and patience. Mine was just for accessing archived documents from a legacy system that was otherwise going to cost us $1000 per week so management were not too concerned how long it took me to build it. Some experienced web developers would no doubt roll their eyes that I used Classic ASP but it supported everything I needed to do.

The site is only used on an our intranet. I studied the security implications a lot before I set it up but I would not like to be building something that connected over the internet as it seems too easy to leave security holes.

 

[Ari]

Hi

virtualization (RDS) can be a good alternative.

https://docs.microsoft.com/en-us/windows-server/remote/remote-desktop-services/welcome-to-rds

Download a sample for kindle, about Access WEB Application. It can be an alternative to be followed.

Professional Access 2013 Programming

 

[zeroaccess]

I have the latest Access and I don't think Access Web is an option anymore.

 

[onur_can]

Currently, there is no mobile support for microsoft access, so if you will have access, you will need to work on Web access, otherwise turn to similar programs that support apk.

 

[Minty]

Access Web (ADP?) was depreciated at least 6 or 7 years ago and is no longer supported at all in the later and current versions of Access.

Remote Desk solutions work well, or some form of hybrid, where all the heavy lifting is done is an Access app in the office or at home, and a power app or flow tasks achieve the more limited mobile requirements.

If the whole thing needs to work in a mobile environment you are into a large amount of development cost with a skilled web designer or team.

 

[zeroaccess]

This might work if I created a function that ran through records in the database and spit out pdfs or whatever the most appropriate format would be that could be stored and recalled from our Google Drive or MS One Drive. Ideally we'd have working forms on mobile but this at least gets everyone access to data.

 

[biofaku]

Maybe using Access but storing the data on SQL Server? Then you could pick up the data from power apps too.

If you need to validate that flow of work it might be a headache, but it could work.

I tried something like that with Sharepoint, and worked almost perfectly.

 

[Isaac]

If you are just wanting mobile apps, and if your company has already accepted that Google storage solutions are OK, I would look into AppSheet, and link it to either dropbox, google sheets, or even sql server if you can get a hold of it.

I've used it with Sheets and been very happy.

 

[zeroaccess]

If you are just wanting mobile apps, and if your company has already accepted that Google storage solutions are OK, I would look into AppSheet, and link it to either dropbox, google sheets, or even sql server if you can get a hold of it.

I've used it with Sheets and been very happy.

Looks promising, but I worry about using spreadsheets as a database. I'd prefer to work with relational data.

I found Quick Base, but their pricing makes it not feasible.

 

[Isaac]

Looks promising, but I worry about using spreadsheets as a database. I'd prefer to work with relational data.

AppSheet connects to a lot of sources, including SQL Server.

 

[Pat Hartman]

The only viable ways to work with Access apps remotely are Citrix and remote desktop. Both run in a browser and so I think will run on a phone. However, who wants to work with a database on a phone???? Certainly not me. If what they need to look up on line is static and does not need to be updated by them by phone, then export data throughout the day to non-updateable spreadsheets. Make them non-updateable to prevent accidents.

 

[CJ_London]

I've used access from my phone - not particularly easy due to the limited screen estate but fine for access apps using simple searches and input but agree would not use it for development. However more importantly is that with citrix/remote desktop you can run it from any device that supports remote desktop - which includes apple and linux devices.

 

[zeroaccess]

Not really interested in remote desktop. We don't need to pipe in the full desktop application.

 

[CJ_London]

You asked for options, up to you whether you want to adopt them.

 

[zeroaccess]

Yes, and I appreciate them, but of course not all options are going to work.

AppSheet and PowerApps sound close to what I'm looking for and will take some further investigation to be sure. I also found Amazon has one called Honeycode. I would like to have both a desktop Access database and mobile access (potentially enabled by the aforementioned apps) that both use the same server such as SQL Server. This is very early days but it seems promising.

 

[Pat Hartman]

If you are going to share a database over the internet, you need to be using SQL Server or some other RDBMS as your BE. Then you can have an Access FE and AppSheet or PowerApps or static spreadsheets or SharePoint lists for the data that needs to be accessible remotely. Of course, the issues with sharing a database in front of and behind a firewall need to be handled. If you don't need the phone people to update anything, I would just create static documents for them to use and refresh them periodically or even on demand. Your Access app can respond to emails and send an updated spreadsheet fairly easily and you don't need to learn a new technology.