Solved

I need some expert opinions on the future of an Access Program I have

Posted on 2014-01-20
9
475 Views
Last Modified: 2014-11-12
Hello Everyone,

I have an Access program that is used by about 10 people.  The company I created it for is merging with another and now there will be about 200 people who need to use it.  Here is what I want to do, what I need to know:

1. Is it feasible?
2. Would this be a good way to go?
3. How hard would it be to do if it is the way to go?
4. Would speed be an issue?

Here is the setup:
The Access Program resides on a company server in the building.  The Access Program has links to another program also on the server.

What I want to do:
1. I would like to split the Access Program and put the tables that are currently in the Access Program up on Azure. My understanding is that while Access is not very good with large groups, that Azure SQL would be able to handle 200 people hitting the data at the same time.

2. Each user would have their own front end put on their computer (the user would not be able to get into design mode because I would be using Access Runtime).

Now there are some people in the company that would like to see the whole Access Program scrapped and have it completely rebuilt so that it is a web based program.  My concern with that is that the web based program would still need to be able to get to the data in that other program housed on the company server.  Also we have shown this Access Program to web developers (3 groups as of now) and with each one their first question was "Why would you want to change this?  It looks great and is doing exactly what you need it to".  This leads me to believe that we can keep an Access front end and just put he back end on Azure (with links to that other program on the company servers.

What do the experts think?  Am I stubbornly sticking to an old technology that people do not use anymore or can Access, with Azure, still be viable for a larger group?

Any opinions (and any links to websites for more info) are greatly appreciated and if you need more information (or if I have said anything "stupid" please excuse me, I am not an Access Expert, in fact I probably know enough programming in VBA just to be dangerous)

Thank you!
0
Comment
Question by:alevin16
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 82

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 100 total points
Comment Utility
The possible problem with Access and Microsoft Office in general is that they have moved it "to the cloud".  http://office.microsoft.com/en-us/business/compare-office-365-for-business-plans-FX102918419.aspx   You may have to get a premium subscription to even see what the Office 365 version of Access does or is capable of doing.  

Adobe is doing a similar thing with their programs.  I assume they are doing it to combat piracy of their programs.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 total points
Comment Utility
<<1. Is it feasible?>>

 Yes.  

<<2. Would this be a good way to go?>>

 You'd want to move the data out of JET and into SQL Server.

<<3. How hard would it be to do if it is the way to go?>>

  That's hard to say. Some apps covert easily to SQL, others need work.  Depends on what was done inside the app.

<<4. Would speed be an issue?>>

  Most likely not.  Most apps that are convert run faster.  Some might run slower, but there are a number of things that can be done to speed them up.

<<1. I would like to split the Access Program and put the tables that are currently in the Access Program up on Azure. My understanding is that while Access is not very good with large groups, that Azure SQL would be able to handle 200 people hitting the data at the same time.>>

  Forget Azure.  Use SQL Server locally.

<<2. Each user would have their own front end put on their computer (the user would not be able to get into design mode because I would be using Access Runtime).>>

 That's the way it should be setup.

<<Now there are some people in the company that would like to see the whole Access Program scrapped and have it completely rebuilt so that it is a web based program.  My concern with that is that the web based program would still need to be able to get to the data in that other program housed on the company server.  Also we have shown this Access Program to web developers (3 groups as of now) and with each one their first question was "Why would you want to change this?  It looks great and is doing exactly what you need it to".  This leads me to believe that we can keep an Access front end and just put he back end on Azure (with links to that other program on the company servers.>>

  That's popular; everyone wants to move to the web or the "cloud".  If it's working well now and you've had web developers look at it and don't suggest changing it, they why?  Conversion to a true web based solution will be a total re-write. Not sure how complex it is, but that could be very expensive.

 The only wrinkle is the users; are any remote or in remote offices?   There are ways of handling that (terminal services or using something like EQLdata.com, or possible a Access web app if the needs were very simple), but if a large number of your users were remote (like all), then I'd possibly re-think re-writting as a web app, especially if you believe the company will grow.

<<What do the experts think?  Am I stubbornly sticking to an old technology that people do not use anymore or can Access, with Azure, still be viable for a larger group?>>
 
 Azure is nothing more then SQL Server in the cloud as a service, but the problem with that is latency.  Most Access apps are not written in true client/server fashion, so over the internet solutions don't typically work well.   Not that you can't do it, but sometimes it takes some work to get there.

 At that point, your then asking "how much work" vs what it would cost to re-write and get rid of some of the Access limitations.

Jim.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<< I assume they are doing it to combat piracy of their programs. >>

  That and they've realized that a features based approach to selling can only go so far.  Plus getting everyone on a subscription model avoids the massive swings in cost vs revenue.

Jim.
0
 
LVL 12

Assisted Solution

by:pdebaets
pdebaets earned 100 total points
Comment Utility
Azure is SQLServer running in the cloud. You might not need cloud services, you may just need to convert the Access back-end to SQLServer or SQLServer Express (which is free).

If you need something web-based, you don't necessarily need to rewrite the Access front-end. You can just move it to a remote desktop hosting service like http://www.accesshoster.com/remote-desktop-hosting . For 200 users, you'd probably need SQLServer as the back-end, and I think AccessHosting will support that, or may support it soon. (I am not affiliated with AccessHosting.com).
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
features based approach to selling can only go so far.
Especially since they ran out of 'useful' new features several versions ago.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 100 total points
Comment Utility
Client side Access still has a lot of life yet.  Not everyone is enamored with the web and Access web apps, even in A2013, are not yet ready for prime time.

Access applications should ALWAYS be split regardless of what you are using as a BE so I would do that sooner rather than later.  The FE is distributed to each client workstation so it can run from their C: drive.  The BE is on a shared network drive.

Moving from Jet/ACE to SQL Server (or other RDBMS) can be a big job or a little job or anything in between.  It really depends on how well structured the app is and if it is already using good client/server techniques.

Once you upsize to SQL Server, your concurrent user limits are dictated by the SQL Server and not by "Access".  Each user has his own FE so as far as Access is concerned, the app is never opened by multiple users so all concurrency is managed by SQL Server.

The big thing you need to understand is how Access works with a "real" database server.  Access makes every effort to pass through all queries to the server and wait for the server to send back the requested records.  So, if you have a query with criteria that selects 10 records, then 10 records is what the server will send back regardless of how many the table holds.  What you need to avoid at all costs is forcing Access to request entire tables from the server.  So you need to take a little care with your queries and forms and this is the part where you will find out how much work you have ahead of you.  ALL forms should be based on queries and those queries should contain selection criteria to limit the rows returned.  

I do this in several ways.  The most common is to have a search box on a form (could be a combo but be careful of high row count combos).  The form's RecordSource uses the search box as its criteria.
Select ... From ... Where somefield = Forms!yourform!yoursearchbox;
That makes the form open empty since the searchbox is empty so nothing is found.  Then in the AfterUpdate event of the search box, I requery the form.
Me.Requery
That runs the query again, this time with criteria.

So your job with forms is to figure out how to give the users selection options that will limit the data returned.  Sometimes, I create intermediate forms that will bring down a small set of data.  From this list form, I click on a record to go to that specific record.

You also need to avoid VBA and UDF functions in your queries except for in the Select clause.  The RDBMS doesn't know VBA so there is no way the ODBC driver can send the function to the server.  That means Access sends the query to the server without the function and when the recordset is returned, applies the function locally.  This is fine for the Select clause.  However, in the Where clause, this would prevent Access from sending the criteria to the server and so it would request the server to send all the data.  Access would then apply the where clause to the returned recordset.   There are other options if you come to this.

Get rid of any domain functions you have that are in queries and you may also be able to get rid of many of them on your forms and reports.  Use table joins to get lookup values.  Create totals queries you can join to to get sums.

If you end up with slow queries, they can frequently be fixed with indexes or views you create on the server.  Stored procedures will be your last resort.

To get started.
1. Download and install SQL Server Express if you don't have an Azure test environment.
2. Clean up your schema by adding primary keys, RI, and appropriate indexes.
3. Give the upsizing wizard a whirl and see what happens.  Choose the option to use declarative RI rather than triggers and then link the upsized tables.

Run the upsizing until you get a clean conversion.  If you have errors, correct them, delete the server side database and do it again.  Change any column names that cause conflicts.

Then you'll need to test your app.  You will need to add dbSeeChanges as an option for DAO code that inserts rows to tables with identity columns.  You may need some other DAO tweaks also.  The one big difference is with when the identity/autonumber is assigned.  With Jet/ACE, it is assigned as soon as you dirty the record but that is because Access has close ties with Jet/ACE.  With ODBC data sources, the identity can't be assigned until the SQL is sent to the server for processing so you won't see it until AFTER the update has been applied.

Have fun.  Don't let them tell you that web apps are the only way.
0
 

Author Comment

by:alevin16
Comment Utility
Hello Everyone.

The feedback I am getting is AMAZING, thank you all (don't stop I can use all the info I can).  I read thru what you are saying and I wanted to add some more info:

1. The users in the other company (the 200 or so) are spread around the US so that is why I figured I had to use Azure instead of just SQL on their server.

2. My access skills are at an intermediate level so I know I do not have anything fancy in this program or in my queries (they are all pretty straight forward queries).

3. All of my tables have 1 primary key and when they are joined I keep integrity.

Please keep your opinions coming!
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
You may find that with a widespread user base, you will need to use Citrix.  Access isn't good with getting data directly over the web even though you can do it.  Try it and if it is slow, you can rebuild the app to be unbound or switch to Citrix.  I would give up and switch to a web technology rather than build an unbound app.  At that point, there is simply no reason to stick with Access since you have lost the RAD benefit.

I have several apps that are deployed using Citrix and it is quite stable and fast since all the processing happens on the Citrix server and the only thing sent to the clients is pictures of the application.  Just make sure you give each user his own personal copy of the FE.  The Citrix administrator will try to make them all use the same copy - DO NOT let that happen.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 total points
Comment Utility
<<1. The users in the other company (the 200 or so) are spread around the US so that is why I figured I had to use Azure instead of just SQL on their server.>>

  There are a couple of ways to handle that:

1. You can run a Access FE on the users box, and SQL Server as a backend, however this would most likely mean quite a bit of modification to the program if your using a VPN over the internet to connect the Offices.

 If you are going to use something like a dedicated MPLS network, then it's not a issue.  Really depends on how your connecting the offices.

2. You can use terminal services (and Citrix on top of that if you want, but RDP has come a long way in 2008 and up).   That keeps your app local on the server and means little program modification.   You still need to convert to SQL though and there is some additional overhead in user setup, but it is manageable.

   If you think though the company will scale up past hundreds of users anytime soon, your probably going to want to re-think the web re-write.

  While you can easily run a terminal services farm (multiple servers with load balancing), that gets costly to scale up when you start getting into the thousands of users (that's for any application, not just Access).

3. You could use www.eqldata.com, but for your situation, this would be the least desirable I think.

Jim.
0

Featured Post

Want to promote your upcoming event?

Is your company attending an event or exhibiting at a trade show soon? Are you speaking at a conference? Spread the word by using a promotional banner in your email signature. This will ensure your organization’s most important contacts are in the know.

Join & Write a Comment

Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
Synchronize a new Active Directory domain with an existing Office 365 tenant
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now