Is Access the right solution? (1 Viewer)

CJ_London

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Feb 19, 2013
Messages
16,553
if you want a web based database with a variety of different front ends doing different things, take a look at sql azure
 

Minty

AWF VIP
Local time
Today, 13:51
Joined
Jul 26, 2013
Messages
10,354
We have a couple of clients that have a hybrid solution on Azure.

Access FE for production and office based, DB intensive operations, and a Web client for road based sales warriors for reports and simple expense tracking, and some of their client portal data.
 

zeroaccess

Active member
Local time
Today, 08:51
Joined
Jan 30, 2020
Messages
671
So as I was writing the original post here it appears Microsoft has been hard at work on this problem.

 

Isaac

Lifelong Learner
Local time
Today, 06:51
Joined
Mar 14, 2017
Messages
8,738
So as I was writing the original post here it appears Microsoft has been hard at work on this problem.

Agreed! I'm waiting for my corporate admins to unlock Power Apps for us and then going to have a go. They say it is coming soon.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Sep 12, 2006
Messages
15,613
It's not a question so much as whether "access" or indeed any relational database is the right solution.
If you are only used to using spreadsheets, then you have to appreciate that database development is a world away from a spreadsheet. In a spreadsheet you can produce a good tool without a line of code. You just can't do this with a database. You need a lot of defensive code to make it fool proof. You need to prevent all users, including yourself doing things that would be ill-advised, especially as there is no "undo" feature. You insulate yourself and other users from some of the more dangerous things that can be done to data. So there are potentially 2 new skills to learn. Database development, and Web development. As @Galaxiom pointed out earlier in his posts, it's still hard even if you know what you are doing.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Feb 19, 2013
Messages
16,553
In a spreadsheet you can produce a good tool without a line of code. You just can't do this with a database. You need a lot of defensive code to make it fool proof.
you need defensive code in excel as well! I've lost count of the number of times I see data where 'required' fields are left blank or fields are filled with inappropriate data - typically text in what is supposed to be a numeric type field
 

zeroaccess

Active member
Local time
Today, 08:51
Joined
Jan 30, 2020
Messages
671
Agreed! I'm waiting for my corporate admins to unlock Power Apps for us and then going to have a go. They say it is coming soon.
I discovered an unfortunate limitation yesterday. Apparently using Power Apps via the MS 365 license comes with some limitations to data connectors, one key one being SQL server. Therefore creating a Power App for mobile use to connect to the same back end db you're using with your desktop Access db is not possible without a separate and costly "Premium" Power Apps license.

I must find a workaround so I can have my desktop and mobile talk to the same db.
 

Isaac

Lifelong Learner
Local time
Today, 06:51
Joined
Mar 14, 2017
Messages
8,738
I discovered an unfortunate limitation yesterday. Apparently using Power Apps via the MS 365 license comes with some limitations to data connectors, one key one being SQL server. Therefore creating a Power App for mobile use to connect to the same back end db you're using with your desktop Access db is not possible without a separate and costly "Premium" Power Apps license.

I must find a workaround so I can have my desktop and mobile talk to the same db.
Interesting. That's disappointing. I'd probably be overlaying them to sharepoint lists to start with, since my company makes us suffer without SQL server currently.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Sep 12, 2006
Messages
15,613
I discovered an unfortunate limitation yesterday. Apparently using Power Apps via the MS 365 license comes with some limitations to data connectors, one key one being SQL server. Therefore creating a Power App for mobile use to connect to the same back end db you're using with your desktop Access db is not possible without a separate and costly "Premium" Power Apps license.

I must find a workaround so I can have my desktop and mobile talk to the same db.
How much is the "premium" license, out of interest?
 

zeroaccess

Active member
Local time
Today, 08:51
Joined
Jan 30, 2020
Messages
671
Interesting. That's disappointing. I'd probably be overlaying them to sharepoint lists to start with, since my company makes us suffer without SQL server currently.
I've heard there can be performance issues when relying on Sharepoint instead of SQL, which worries me. I'm also not sure how we would create a relational database in SP.
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 06:51
Joined
Mar 14, 2017
Messages
8,738
I've heard there can be performance issues when relying on Sharepoint instead of SQL, which worries me. I'm also not sure how we would create a relational database in SP
Assuming modest amounts of data, I've had no trouble using Sharepoint lists as tables. As to their "relational"-ness, you simply enforce it in forms and code. You represent the relations with tables just like you would in Access, really.
 

zeroaccess

Active member
Local time
Today, 08:51
Joined
Jan 30, 2020
Messages
671
Assuming modest amounts of data, I've had no trouble using Sharepoint lists as tables. As to their "relational"-ness, you simply enforce it in forms and code. You represent the relations with tables just like you would in Access, really.
I suppose you don't have to define relationships at the table level, though I do so I have things mapped out for myself and to provide defaults throughout the database.

Power Apps can't do code, though. So I'm unsure how you'd go about building a Power App for mobile that reads and writes to the same database. I suppose a lot of that is taken care of for you. I would like to get my hands on it to try but the licensing might be a showstopper.
 

Isaac

Lifelong Learner
Local time
Today, 06:51
Joined
Mar 14, 2017
Messages
8,738
Power Apps can't do code, though
Then it either sucks, or there has to be a LOOOOOT of configurable properties. Either way doesn't sound like a fun afternoon to me.
 

zeroaccess

Active member
Local time
Today, 08:51
Joined
Jan 30, 2020
Messages
671
Then it either sucks, or there has to be a LOOOOOT of configurable properties. Either way doesn't sound like a fun afternoon to me.
It actually looks like they took PowerPoint and Excel and Access and blended them together. No VBA. But there are "formulas" used in a similar-looking bar as Excel. I wouldn't be able to criticize much without getting my hands on it but I know that for many, no VBA or query editor means it can't replace Access.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:51
Joined
Feb 28, 2001
Messages
26,999
So far, in the time I have worked with Access as a dabbler and later as a user for corporate and government functionality, I have never seen anything in the same price-class that had quite the utility of Access. Particularly considering the added feature of having lots of "hooks" into Excel, Word, Outlook, and other Office-class apps. Every time someone comes up with an alternative, it is sorely deficient in some way. Though Access is clunky and has many limitations when not using an active SQL back-end, nonetheless I've never seen anything that could beat it overall.
 

zeroaccess

Active member
Local time
Today, 08:51
Joined
Jan 30, 2020
Messages
671
Having no experience with SharePoint I had to look further. It turns out you can create relationships:

Create list relationships by using unique and lookup columns

Still, there will be limits depending what you want your apps to do – which means if you want a desktop Access front-end and a mobile Power App to connect to the same SharePoint, it will place limits on its complexity.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Sep 12, 2006
Messages
15,613
I'm not sure about clunky

I have used the RAD facilities of Access to do quite a few things that have had nothing to with databases. Just a way of getting a programming language that I am very familiar with.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:51
Joined
Feb 19, 2002
Messages
42,970
The Relationships you create in SharePoint result in table level lookups and you have NO choice. It was from Sharepoint that MS got the idea to do the same thing in Access. Sharepoint is a poor substitute for even Jet but if you have small tables (< 5,000 rows) and you need to have remote users without using Citrix or RD, then it is an option. Not one I like but we don't always have control over decisions made by our clients or employers.

Just FYI, Sharepoint emulates tables by using a skinny table with an ID, tblID, FK, FieldName, FieldValue organization. This is essentially an entity/attribute schema. So, if your "table" has 30 columns, each logical record takes 30 rows (the data is actually stored in SQL Server) to store it. And I think all "tables" are stored in the same physical SQL Server Table. So couple of rows might look like

1, tblCust, 245, CustName, Microsoft
2, tblCust, 245, Addr, One Microsoft Way
3. tblCust, 245, City, Redmond
4, tblCust, 245, St, Washington
 

Users who are viewing this thread

Top Bottom