Building a customer portal connected to SQL Server backend. What are my "cost-effective" options?

[medihub_guy]

Hi there!

So I built a pharmacy management software. Now I'm looking to offer patients the ability to view their medication list, request refills and update their demographics. All of this information is currently captured in my SQL Server tables. Naturally, I wanted to stay "in house" with the MS suite and looked in Power Apps. Unfortunately, though, that option does not appear to be cost effective. I spoke to one of their sales agents and the best price they could offer is $200 per 100 users (in my case patients) per month... The thing is though, what about scalability? I'm gunning for 100,000 plus users on this thing!
There must be a better way. Any ideas?

 

[The_Doc_Man]

At that traffic level, you are looking at web protocols. Access isn't going to cut it on costs unless you want to do a run-time-only version with some kind of .ACCDE file for the FE... which would be possible except that I doubt you would be able to assure customer environment uniformity. Which means a LOT of smarts in the run-time FE file. And I'm not sure I could do it even with 20+ years of Access experience and 50+ years of total programming experience.

 

[medihub_guy]

Lets entertain the .accde FE file option. What all does that entail? How will I get that on patients mobile phones as an app? I'm almost certain this cannot happen or even if it is, advisable.

 

[plog]

"Cost effective" is highly dependent on your skills and experience. And to be honest, by you asking this question it raises doubts about you having the necessary ones to accomplish this.

Doesn't mean you can't acquire them, just that if you have to ask how to do this in a low cost manner there's most likely a learning curve. You can get a webhost for $5 a month that will allow you to throw up a site to accomplish what you want. Do you know how to develop websites? Do you know how to develop in a manner that keeps you in compliance with all laws related to the security of personal health information?

 

[medihub_guy]

"Cost effective" is highly dependent on your skills and experience. And to be honest, by you asking this question it raises doubts about you having the necessary ones to accomplish this.

Doesn't mean you can't acquire them, just that if you have to ask how to do this in a low cost manner there's most likely a learning curve. You can get a webhost for $5 a month that will allow you to throw up a site to accomplish what you want. Do you know how to develop websites? Do you know how to develop in a manner that keeps you in compliance with all laws related to the security of personal health information?

I'm not good at web development. I am proficient in MS Access. Are you saying building a website in Wix or wordpress (for example) can solve my problem? I can build a secure patient portal that can manage 100,000 + users seemlessly?

 

[The_Doc_Man]

Since this is a medical-related application, NO choices are going to be cheap. If this were a product-only (no patient data involved) site, your security would be only average in requirements. But you said patient data would also flow across the net as your customers check their personal records. You suddenly raised the stakes considerably. The issue of security for 100,000 users will be daunting no matter WHAT type of portal you use. BUT from a purist standpoint, I'm thinking the resources needed for this would be prohibitive when using Access FE.

The reason is that SMB (Server Message Block) protocols are part of the TCP family of (active) connections, whereas web-based protocols are UDP and do not necessarily require a persistent connection - because they retain context via the "cookie" system instead.

TCP protocols need a persistent network socket. If 2% of your customers are online, you will need to have 2,000 sockets - which is a LOT unless you have a REALLY hot network - in the 1 GB and up range. The networks will need a "keep-alive" pulse for every connection. If you actually log in a true "session" from the FE to the server, you have to track those network sockets, which speaks to the size of the system you would need. (Not somebody's desktop tower system.)

Doing this on the web isn't that much easier, but the use of web/connectionless or low-connection-only methods reduces the internal structures that you need to maintain. Getting users on mobile phones isn't IMPOSSIBLE with an .ACCDE, but I would bet that it will be hard to find a run-time-only version for users on Apple phones. And you CAN'T tell them that you MUST be on Android to user your facility, because they will say "OK, then I won't use your facility at all." Whereas web-based phone connections aren't that hard.

I was thinking about the .ACCDE, but when I got to REALLY thinking about it, I realized that Access isn't going to be the solution for the BIG picture. Sorry if my musing misled you.

 

[plog]

Are you saying building a website in Wix or wordpress (for example) can solve my problem?

I'm not familiar with those those two technologies to say if they can accomplish what you want, but my gut feeling is "not just by themselves". My point is that money and time are two big elements of a "cost effective" solution. The more time you are willing to spend, the less money you will need. The more money you are willing to spend, the less time you will need.

The balancing of those two resources are up to you. "Cost effective" is in the eye of the beholder.

 

[medihub_guy]

Now that I think about it, I'm basically creating an online pharmacy. It's nothing new. But I need that functionality. Looking up "online pharmacies" on youtube and seeing advertisments of current pharmacies on how their website functions, @plog is right. I dont have to over complicate this. A simple user login plug in will suffice for security purposes. At least thats what the other guys are doing. The Privacy Policy statement that noone reads will be at the footer. Ok time to rock and roll.

 

[Gasman]

We used to access an Access db via a browser on this site

Features | Client Database Software | witsIFA

Key Features

www.witsifa.co.uk

 

[Pat Hartman]

I'm not good at web development. I am proficient in MS Access

Access is only useful in a closed environment. Either the people need to be on your LAN or you need to be running a Citrix server and they use your Access app that way. I think the phone people could even use Citrix since it runs in a browser but you would need to build the forms so they can be used on a phone. I'm also not sure if Apple has done something to make Citrix not work in iPhone browsers. I wouldn't put it past them.

Being on your LAN is impossible and using Citrix will require annual licenses for each unique user which makes that solution cost prohibitive. So, Access is not the solution. You need to create a web application using whatever web technology you know. This is not a task for a beginner. I don't know what your country's laws are but in the US, privacy is pretty stringent so hosting commingled data will require strict segregation so you don't accidentally show client 2 data for client 1. You also have to worry about what data you keep where so you don't get killed by a hacker.

Looks like you will be starting from scratch for the development. At least the Access database can be used as the design model. You can still actually use your Access database for your own personal back office work. It just can't be the client facing part of the app. That needs to be actual web pages.

I don't develop web apps so I can't recommend a tool. What I can tell you is that it will be far more difficult than building the Access application was. I have found every tool I tried to be clunky and difficult to use so since I never had a pressing need, I never did much more than a surface evaluation. Platforms like Wordpress are not data bound out of the box so be prepared to purchase plugins. If you are happy with the data validation and process control you created in Access, that will be helpful for designing the web app but the code will be different. No web tool that I know of uses VBA so a new language is in your future.

 

[Edgar_]

If phones are involved and you're going to use WordPress or Wix, a responsive template will be required. If that responsive template is going to be connected to a SQL server database provided by the hosting service, that database is likely going to be shared with thousands of other websites, also expect very little customization.

If you're building an online pharmacy, there are multiple services that let you create your own store with landing pages and all, like Shopify.

If you choose to take the web app path, React and NodeJS are a good solution, because both use JavaScript. React also helps you reuse a ton of code from the web app in the mobile app, which can run both on Android and Apple devices. So this is definitely a cost-effective path that uses current technologies and you'll have tons of support, as opposed to many other web technologies which will make you feel like you're working with outdated stuff. This suggestion is what I use, but you can see how it ranks here:
https://survey.stackoverflow.co/2023/

I suppose one good reason it's so popular is that it gets work done fast. By the way, Express is part of Node and Next uses React, so React and Node's percentages are much higher in reality.