Go Back   Access World Forums > Apps and Windows > SQL Server

Thread Tools Rate Thread Display Modes
Old 11-12-2015, 04:55 AM   #1
Newly Registered User
Join Date: Nov 2015
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
tarkany is on a distinguished road
Ms Access + MySQL or Visual Basic + SQL Server

We are a small medical company and carry out regular examinations for employees of various companies. Weve been using an MS Access database for a couple of years now for tracking our financial information and scheduling appointments, which has been working flawlessly. Now we would like to take a big step and create our own custom made DBMS to record all medical data of our clients. What we need in development stage 1:

- a database that can manage a growth of around 8-10 thousand new records annually (most of our clients return on a yearly basis)
- high security, since we will store medical data
- functionality to scan previous medical records and link them to individuals as easy as possible (without the need to manually load a separate program and locate scanned images)
- ability to manage 5-10 concurrent users

What we need in development stage 2:

- web interface for employers to check upcoming examinations
- (ideally) a mobile app to be more flexible in scheduling appointments
- ability to manage 100 concurrent users
- (create an invoicing module - maybe stage 3)

Im pretty good in Access now and my VBA skills are getting better and better.

I did a lot of research and read plenty of forums, so I feel I have the following options
1. use Access as front-end, MySQL as back end, later use php for development stage 2 (I have no experience with php so far) linking to the previously developed MySQL database
2. move from Access to Visual Studio and use SQL server

I would really appreciate your thoughts on this one, which path to choose. Or I am open to any other suggestions you find suitable for our needs.

tarkany is offline   Reply With Quote
Old 11-12-2015, 10:11 AM   #2
Newly Registered User
Join Date: Jun 2012
Posts: 303
Thanks: 11
Thanked 56 Times in 55 Posts
ButtonMoon will become famous soon enough
Re: Ms Access + MySQL or Visual Basic + SQL Server

In different ways both of the two choices you mention are slightly unusual and probably not the options you should prefer. MySQL has lost its popularity since Oracle purchased it and many people prefer MariaDB (a MySQL offshoot) nowadays. Access has no merits at all for any new-build web development project.

In almost every way SQL Server is technically superior to MySQL/MariaDB. On the other hand, Visual Basic (presumably you mean VB.Net) is a niche language and much less common than C#.

I suggest you need a web interface at “stage 1” because there doesn’t seem much point in developing a desktop capability and then a web capability. Don’t try to deliver things in “stages” like that. I suggest a more Agile project approach would be better.

From what you've said this seems like a critical solution for your business. You'll need a development team (no-one, no matter how expert, ought to take on key operational system projects alone because that kind of key-man dependency would put your business at risk). Either your company already has a development team, in which case you need to consult them, or you are hiring one, in which case the market and your budget are factors in your choice. If you are a Microsoft shop then I would go for .NET and SQL Server just because of the widespread support and availability of skills.

Finally, don't forget the mantra of "buy before you build". There are obviously commercial solutions readily available to do the job. For these kinds of line-of-business solutions the packaged solutions typically work out cheaper than reinventing your own.

Last edited by ButtonMoon; 11-12-2015 at 10:22 AM.
ButtonMoon is offline   Reply With Quote
The Following User Says Thank You to ButtonMoon For This Useful Post:
tarkany (11-12-2015)
Old 11-12-2015, 12:26 PM   #3
Newly Registered User
Join Date: Nov 2015
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
tarkany is on a distinguished road
Re: Ms Access + MySQL or Visual Basic + SQL Server

Thank you for your quick reply. The truth is that we operate in a niche segment of health care, there are only a few programs available, but none of them offer a solution which could handle 5 different locations with one shared database. I have negotiated with external developers but their quotes were just unreasonably high for a small company like ours. This was one of the reasons I decided to develop the program myself. The other reason was that I have a lot of fun working on this, and luckily there is no time pressure yet. Also I have already modelled the program in Access (approx. 80% ready) I just need something more roboust.
Based on what you recommended I believe I will choose C# .NET and SQL server. I will also reconsider the development stages. Thanks again for your comment!

tarkany is offline   Reply With Quote
Old 11-13-2015, 12:42 PM   #4
Newly Registered User
HiTechCoach's Avatar
Join Date: Mar 2006
Location: Oklahoma City, OK
Posts: 4,245
Thanks: 8
Thanked 155 Times in 143 Posts
HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough
Re: Ms Access + MySQL or Visual Basic + SQL Server

If you will upsize the back end to the free Microsoft SQL Server Express then it open u up to a lot of options.

Note: Access works best with MS SQL Server not mySQL Server.

Once yu have your back end in MS SQL Server then you can use many doffernt frotne dn all at eh same time.

Since you have a lot already done in Access you can continue to use it with the MS SQL Server Back end.

You can also crete a web frotne nd that shares the same SQL Server back end.

You can also create >NET front end that also use the same SQL Server Back end.

It is even possible to create functionality in .NET and use it in your Access Front end.

You are not limited to a single development platform for front end to an MS SQL Server.

If the MS SQL Serer is exposed to the internet you can even use desktop apps (Access, .NET, etc) on remote PCs to connect to the MS SQL Server. You can use Windows Server 2012 or later to create your own private cloud. Note: Windows Azure started out on Windows Server 2012.

I now use MS SQL Server as the back end for all new Access development. This allows me to easily add ASP.NET and VB.net front ends for additional functionality.

(ideally) a mobile app to be more flexible in scheduling appointments
A well design web app that is response works great on all mobile devices.
Boyd Trimmell
Specializing in Accounting, CRM, and Business solutions
Microsoft Access MVP 2010-2015 , aka
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
HiTechCoach is offline   Reply With Quote
The Following User Says Thank You to HiTechCoach For This Useful Post:
tarkany (11-17-2015)
Old 11-17-2015, 08:35 AM   #5
Newly Registered User
Join Date: Nov 2015
Posts: 3
Thanks: 2
Thanked 0 Times in 0 Posts
tarkany is on a distinguished road
Re: Ms Access + MySQL or Visual Basic + SQL Server

Hi HiTechCoach!

Thank you for your reply. It was really helpful!
tarkany is offline   Reply With Quote
Old 11-17-2015, 02:11 PM   #6
Nothing In Moderation
Rx_'s Avatar
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,794
Thanks: 635
Thanked 336 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Re: Ms Access + MySQL or Visual Basic + SQL Server

In my past role in the late 90's, I was CTO for a small Plan of Care program that was taken public. The founder of Stat Paths (a plan of care based on diagnostics) acquired $3M USD to develop an Access program that managed Long Term Care facilities. It covered all of the government reporting data uploads, the plan of care, the nurses notes, observations, head-to-toe, and much more. As an example, it covered Meds. It allowed the nurse for a wing (around 40 to 90 patients) to load up the push cart with the medications and instructions to go make the rounds. Then it interfaced into the re-inventory delivery system with the Doctor's orders.

The product was in Access 97 with some Visual Basic DLL added in and a SQL Server back-end. The product was beta tested in a couple of long term facilities. Then the expansion demanded national reach for many facilities.
Note, a lot of these facilities only had dial up networking at the time. Internet and even DSL was rare in rural areas.

I bring this all this up so it is somewhat relational to your needs.

The real problem was that most facilities don't really have a IT person.
So after much consideration, I contacted the new fangled company CITRIX.

With Citrix, we had one copy of the production front-end with links to a SQL Server Back end for each company. A company might have one or a dozen facilities.

With Citrix, we made no changes in the Access code. The customer just loaded up an ICA (type of terminal interface) client on a PC of any version of windows, Mac and even some weird stuff like handheld (remember all the wireless that has come and gone?). Today, a smart android phone could even have an interface.

One Access application per company. A company might pay for an extra module such as Veteran co-pay for example, while another state might offer something unique too.

If your going to expand, you really want to migrate your back-end data onto SQL Server. It is mostly straight-forward. However I highly suggest you pay a consultant to avoid the normal pitfalls and minor challenges. You would probably like to see a flawless conversion in 30 days.

The Citrix side gets really interesting. First of all, it will read the client's workstation across the nation and expose the workstation's printers, monitor and other devices.

The Access Reports will need some code modifications so they save as the end-user's hard drive, not so much your server. There are just some lessons learned and tricks. Again, hire a good professional. This is just a process that an experienced Access Programmer can help you with.

The Citrix will allow an administrator to set up accounts (kind of like a LAN manager).
Unlike Web, Citrix is extremely secure. The screen is divided up into 128 squares. Each square employes 128 Bit encryption per session. The bandwidth for a dial up modem use to support 3 end users. It is also known as a Thin Client. No data is stored on the remote workstation. This means all the data stays on the server and SQL Server.

The upside is that with a good plan and a very few right consultants, you could probably convert and launch nationally in 6 to 8 weeks.

The advantage of Citrix is that it would save huge amounts of money for re-programming in a different Web language. The time saved would be generating new income stream. Imagine in 8 weeks, a new client calls and wants immediate access and you have them running the next day. Training: With Shadowing - your trainer or Help Desk can view your clients terminal. This was a huge savings! It also opens up the opportunity that your client wants to hire one of your staff to fill in until they hire someone. That is more income stream opportunity.

The down side to citrix is the cost per-seat licensing. I think you can start with 5 seats then grow into the 10,000 seat as you need. You will need a staff or consultant to attend about 10 total days of Citrix setup, management, and security classes.

Now, Microsoft even has Citrix as part of the .NET deployment.
They don't really promote it as much because most sales staff just understand the Office 365 basic package.
The Citrix version of Office 365 Professional would cost something like $27 per end user per month (don't quote me, but it is something like that). That would include Office Professional 365 with all of the web redundancy and your Access special application on a Citrix server, on a Cloud service. Basically, you wouldn't have to buy and maintain a server and all of the backup. You could add or remove clients every day.

I can put you into contact with a MS Partner who presents this with Access applications from the Denver area.

My consulting company provides services for an international company regarding complex industry specific data management. They had a rough Access DBs that I converted to SQL Server back-end. I then guided them through Citrix deployment. I didn't have a team. There was bureaucracy. It took about six weeks.
I can program or alter Access /SQL Server databases and put a new release to all users several times a day. B.T.W. I am not looking for more work. It is just an example for you to consider.

I think Citrix (or the version for Microsoft Cloud Services licensed through Citrix) would be your lowest cost, least risky, fastest conversion, and most scale-able growth option.

The other options are all valid. Call me lazy and low risk.

Rx_ is offline   Reply With Quote

sql , vba , visual studio

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
difference in visual basic on access and visual studio iworkonline Modules & VBA 1 05-26-2010 01:49 PM
Connect to mySQL Database with Visual Basic charlesandrews Other Software 8 01-01-2010 03:06 AM
Access to Visual Basic supercharge General 1 10-12-2005 12:36 PM
Visual Basic and Access Help Leej2000 Forms 1 05-26-2004 04:36 AM
Visual Basic vs. Access Rob.Mills General 7 09-04-2003 07:30 AM

All times are GMT -8. The time now is 07:17 PM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World