UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Help! What Are My Options?, Any Version    
 
   
HeatherO
post Nov 13 2018, 10:40 AM
Post#1



Posts: 5
Joined: 29-August 18



Good Morning All,

I have been reading posts in this forum, and others like it, over the last few weeks in an effort to research a way to make our Access databases accessible from the internet. I am thoroughly confused!!!

I am by no means a professional but have been learning as I go over the past 2 years. I have built 3 different databases for my company, which we use to conduct all of our day to day business. They're all split databases, with each user having a front end saved locally on their PC, and the back end saved to our server. We would like to expand our capabilities so that the database can be accessed from outside of our organization. As an example, we currently have an online application set up on our website. We export that data to an Excel file and then import it into the database. We would like to be able to tie this application directly to our database (like a form on the front end) so we no longer have to import and always have "real time" data. We would also like the applicants to be able to look up their application and make changes to it themselves... and again, the staff in our office would see these changes in real time.

I have been reading up on SharePoint, Azure, Rackspace, etc.... and cannot piece together what we need to do to make this happen. It seems that Microsoft is no longer going to provide the capability to do what it is that we want to do?? Is there a way to do this with Microsoft Access?? Or am I going to have to throw away 2 years of learning Access and VBA to try something else?? What would the other options be??

Any feedback is greatly appreciated.

Go to the top of the page
 
theDBguy
post Nov 13 2018, 11:11 AM
Post#2


Access Wiki and Forums Moderator
Posts: 73,924
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

The way I see it, your first option is your website application, where the data you want is already stored. What is it? Does it allow for external access? Is it an SQL Server database? If so, then you're almost home free.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Nov 13 2018, 11:39 AM
Post#3


UA Admin
Posts: 34,077
Joined: 20-June 02
From: Newcastle, WA


Keep in mind that there are two (at least) components to every database application: the database engine where the data is stored and the interface through which users interact with that data.

Access is unique in that it includes BOTH components. However, the Access interface is strictly Windows based. It is possible to use it in a lot of different ways, of course.

To replicate that in a web browser, you'll need a new interface. HOW you do that, of course, is where the tough questions lie.

Starting with the answer to thedbguy's question about the data. Thanks.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
HeatherO
post Nov 13 2018, 12:06 PM
Post#4



Posts: 5
Joined: 29-August 18



Our website is WordPress.

Our It guy seems to think that we can export the Access databases to SQL and somehow use that with our website.... but (he doesn't know that for sure) again, how does that then "talk" to our back end database? We ideally want a real time connection. There are a few other things we want to use this for me and it doesn't really work if it can't be real time? Or extremely close to it. We prefer not to have to "sync" data.
Go to the top of the page
 
GroverParkGeorge
post Nov 13 2018, 12:22 PM
Post#5


UA Admin
Posts: 34,077
Joined: 20-June 02
From: Newcastle, WA


Well, in theory, if you have Access tables and a SQL Server to which you can connect, then yes, migrating the data should be straightforward.

Keep in mind that there will be additional considerations, including performance degradation, when you do so.

Look at the SQL Server Migration Asssitant for Access. And download of the SSMA tool.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
HeatherO
post Nov 13 2018, 08:19 PM
Post#6



Posts: 5
Joined: 29-August 18



Performance degradation such as? That doesn't sound promising. Are there other options?
Go to the top of the page
 
GroverParkGeorge
post Nov 13 2018, 08:29 PM
Post#7


UA Admin
Posts: 34,077
Joined: 20-June 02
From: Newcastle, WA


Typical Access designs tend not to work as well when implemented with a remote database.

For example, a common approach is to load a form bound to an unfiltered dataset so that every record is retrieved for it. Then, to focus on one record, a filter is applied. This works okay for local Access accdbs or even accdbs on a LAN. However, across a WAN to a remote database, that strategy can result in serious performance lags. Instead, we want to START by loading only one record at a time.

The solution is to redesign such forms to use a pilot query that populates a list or combo box, using only indexed fields. Then when a selection is made in that list or combo box, the relevant record--by itself-- is retrieved into the form's recordsource.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
RobKoelmans
post Nov 18 2018, 04:51 AM
Post#8



Posts: 472
Joined: 25-November 14
From: Groningen, Netherlands


Hi HeatherO,
Did you have any progress on this already? Although it's not obvious (it's a very recent technology) i.m.o. MS-Flow is the central platform to use for migrations like yours. There's VBA code available that makes MS-Access consume data through MS-Flow on a record by record basis or on small search result sets. The big advantage is, that the same Flow thread can serve PowerApps. This way you can join develop your front-ends in both MS-Access and PowerApps. It's quite complex at first, but - as soon as you have the proof of concept - you can apply it anywhere quite easily.
Kind regards,
Rob
Go to the top of the page
 
HeatherO
post Nov 20 2018, 04:13 PM
Post#9



Posts: 5
Joined: 29-August 18



Thank you for the feedback. No, we haven't had any progress on this. The IT guy and I are suppose to sit down and have a discussion about this but to be honest, I'm still as confused as I was on Day 1.
Go to the top of the page
 
RobKoelmans
post Nov 24 2018, 04:46 AM
Post#10



Posts: 472
Joined: 25-November 14
From: Groningen, Netherlands


Working with PowerApps and Flow inspired us to work in the same way with Access Desktop. MS-access retrieves one or more JSON tables (relational but not nested due to some issues in PowerApps), create an ADO virtual recordset and bind that to a form. It works extremely well (just as we expected from working with PowerApps like that) but you may have to redo all your forms and reports in some way, I'm afraid. We can get you a proof of concept that works with a Flow of us (it's almost finished to deliver as a working demo but more urgent things keep coming in). On videos on my site you see how it's with PowerApps (www.metamicro.nl). The only difference is that PA takes JSON right away as collections.

It's thrilling to us because this looks to be a relatively simple way to make Access legacy comply to EU regulations on data safety. And there's still a lot of legacy around.
Rob
This post has been edited by RobKoelmans: Nov 24 2018, 04:46 AM
Go to the top of the page
 
RobKoelmans
post Nov 27 2018, 06:58 AM
Post#11



Posts: 472
Joined: 25-November 14
From: Groningen, Netherlands


Hi Heather,
We have MS-Access Desktop now completely working though Flow. No connected tables from other MDB's, SQL-Databases or SharePointLists anymore.
Kind regards,
Rob
Go to the top of the page
 
DanielPineault
post Nov 27 2018, 07:57 AM
Post#12


UtterAccess VIP
Posts: 6,272
Joined: 30-June 11



Look at the "What Are The Alternatives Then?" section found at http://www.devhut.net/2016/09/24/access-ba...edrive-dropbox/

Also, Migrating your data to SQL Azure and using a standard Access Front-end (this setup is often referred to as a Hybrid setup) is another solution. For this setup to work efficiently you need to optimize forms to only retrieve the record(s) you truly need. So limiting forms to no records when opening and retrieving single records is best, not filtering full tables... (which is a typical approach in common Access development).

That all said, when someone says to me they need a database accessible from the Internet Access goes by the wayside. Access just isn't the solution IMHO. I would personally recommend using proper tested and true Web technologies (PHP, .Net, ...).

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
RobKoelmans
post Nov 28 2018, 09:29 AM
Post#13



Posts: 472
Joined: 25-November 14
From: Groningen, Netherlands


Hi Daniel,
Having Flow submitting JSON to MS-Access works remarkably well. You can use the same https webservice within Flow on behalf of PowerApps and of Access. I doubt we'll ever be building REST services in ASP .Net on behalf of conventional websites atl. I think Access has become the preferable way to build again along with PowerApps.
Go to the top of the page
 
DanielPineault
post Nov 28 2018, 10:03 AM
Post#14


UtterAccess VIP
Posts: 6,272
Joined: 30-June 11



I know you're a big PowerApps/Flow guy, but I just can't get onboard. After being burnt 3 times now by MS going down a road and then doing a 180, I can't ask my clients to take that gamble again and risk getting burnt. People lots their businesses thanks to the way MS handled/kill Web Apps!

Nope, tried and true web technologies are my answer for any true web access questions. For instance, 20+ years PHP has just worked and most hosts support it. You're not dependent on anyone, move it to another host if you have any issues, plenty of resources, developers, ... I also don't like tying my clients into subscription based systems, I don't find that is doing them a service, especially in the long term, but once you make that leap, it's going to be very hard to get off.

I'm not saying the technology isn't interesting, I'm saying MS has proven itself to be not trustworthy.

Who knows, maybe I'll warm up to it over time, but then again I just spent 2 month migrating a major client away from Access to PHP to get away from these never ending Windows & Office bugs and give the a Web Based solution accessible on any device from anywhere in the world.



--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
AlbertKallal
post Nov 28 2018, 03:30 PM
Post#15


UtterAccess VIP
Posts: 2,704
Joined: 12-April 07
From: Edmonton, Alberta Canada


Well, perhaps you don’t really need a web system?

Often, what people “really” want is to simply use the application anyplace with an internet connection.

If you setup a server with terminal services, then all your workers can work anyplace with an internet connection.

Often we can’t see the trees for the forest so to speak.

Now of course it don’t make sense for customers to use some Access application – that kind of “customer” interface certainly should remain a web application.

You have to give VERY careful thought to what you need and want for some kind of customer web system vs that of allowing your internal staff to use an Access application “anywhere” and “anyplace”.

So using terminal services (remote desktop) will allow use of the Access appcation anyplace and anytime.

If you need an interface between your access applications to pull web data, then in most cases the best solution is to setup and run your own web server. That way, the access application and the web application can share + use a common database (likely SQL server).

Access does not lend its self to some kind of “customer” web interface. I mean, let’s say Access allowed you to migrate any form and parts to the web. The problem is NOW that web system does NOT keep data separate, and nor does SharePoint allow users to “self-signup”. Issuing of users and logons becomes a HUGE problem.

So if each customer can now launch that form, but all the records of all other customers would also be in full view. And keep in mind that a web system does not “automatic” ONLY show the customer their own data from the database.

YOU the developer has to restrict that data. Think of building an Access applications in which each user/workstation could ONLY see their own data! How would you restrict data for just each user? The problem is EXACT same when using Access, or a web application.

The web application does not out of the blue by magic JUST show each customer/user their own data. How these systems work is EXACT same as Access. That means you can’t just toss up a web form bound to some table. You have to adopt some kind of system to restrict what each user sees.

In most cases in your office, launching a form to view customer data is fine, but with a web system, such forms are of little use – since now every web user sees all data.

Web systems do NOT automatic separate out the data between each customer. And maybe all users (say your staff) in fact may want an access like form on that web site to view all data.

However, security, logon and a VERY long list of issues has to be dealt with.

For example, who maintains the passwords for the customers? Do they call you up for a password change? (Or forgotten password). Does someone spend the whole data answering these needs of customers for the web site now? All of these issues have to be designed and thought out by you. You could spend a week just sitting down and figuring out the type of logon system and how passwords etc. are to be managed. Perhaps you add software to allow users to change their own password? (Or maybe not???). How do you disable or turn off one logon now? (Who manages this issue???). You could write a LARGE BOOK on just management of customers and their passwords.

So nail down your goals into separate issues.

So, one goal is to use and deploy Access applications for your staff – terminal services is one of the best solutions here. Once setup, then anytime they have an internet connection to use a web browser, they will also have use of the Access applications.

Next up is the customer web portal. If you need both the customer web portal and the Access applications to share data, then I recommend running a web server on site. This will allow seamless use of a common database between the customer web portal and your Access applications.

The above is about the “best” case approach. I would migrate Access data to SQL server, setup terminal services, and then setup a server for the web portal. This approach is the “ideal” setup that would give you seamless integration with the customer web portal, and also allow use of Access from any internet connection.


Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 
RobKoelmans
post Nov 28 2018, 07:16 PM
Post#16



Posts: 472
Joined: 25-November 14
From: Groningen, Netherlands


I can relate to what Daniel is saying about not being trustworthy (though it wasn't evil, MS just didn't run the whole show on itself). VBA was supposed to be gone for 15 years or so. .Net was the thing, also for all Office products. Exchange Public Folders went and came back. SharePoint was gonna be the center of the universe. Digital Dashboard. I can also relate to what Albert is saying on running your own webservices. My point is that MS accidently did right with Flow. It's brilliant. We now have Access Desktop being the exact same first class citizen that PowerApps is to Flow. PowerApps is very hard to learn to be used well but - if you just follow our extremely simple strategy on using legacy on premises databases (or Azure SQL) from Flow, you have a simplicity and performance that you haven't seen in 25 years and you can forget about your own webapi2 services, ODBC/ADO connectors let alone Entity FrameWork, LinqToSQL and other source code generators.


We'll work out a demo with Access Desktop using the same Flow https-service that PowerApps uses. Access Desktop has no connected tables to BE or SQL-Server, only local lookup tables on behalf of data entry that are local to the access app. Just retrieving data by importing incoming JSON to Virtual ADO recordsets. It works great and a user cannot bypass its identity and retrieve information he or she is not allowed to have.
Just give us a couple of weeks to work things out in between customer project works.
Rob
Go to the top of the page
 
AlbertKallal
post Nov 28 2018, 09:38 PM
Post#17


UtterAccess VIP
Posts: 2,704
Joined: 12-April 07
From: Edmonton, Alberta Canada


@RoKoelmans

Well, .net has WELL become an industry standard for 15+ years now.

And SharePoint? Well it going on 15+ years strong, and “most” of the office 365 services were built around these SharePoint services. I only mention this for the reason that .net, and SharePoint are likely two of the greatest success from Microsoft in the last 15+ years.

Access started SharePoint list support in version 2003. Here we are in 2018, and that support from Access is still going strong.


As noted, consideration has to be taken into account for pubic facing web sites vs internal. PowerApps are really limited to SharePoint, or say a 365 plan. You certainly might get it to pull some data from another web site – but that site will have to support some means to pull that data.

In other words, for “building” a customer web application or site, powerApps don’t cut it. (Pulling from some existing web site certainly is an option however).

And same goes for flow – again this is really the next gen SharePoint workflow system.

I mean, it not at all clear if re-writing an access application around power apps makes a lot of sense cost wise as opposed to say using remote desktop? If some forms for data entry is the goal, then sure, power apps will fit the goal, but NOT for customers!

And if one is going to jump on SharePoint (on premises), or say some office 365 plan to start building some web interface and forms, then again, no question power-apps is a great choice. (But again, not for customer facing sites).

And for those with experience using SharePoint workflows will nicely migrate over to “flow”. Flow has some great “web” feature that allow you to take actions on incoming email, or even say a file appearing in drobox. A great tool to string “steps” in a process, and add some automation to such a business process.

However, none of the above technologies are suitable for public facing web sites, or so called “customer” portals.

Not that anything wrong is with such choices, but as noted, MUCH consideration in regards to the target audience is required here.

Attempting to “convert” or change say an access application, or some powerapp applications for internal business use as opposed to a customer web site are VERY different concepts and goals.

I mean, a customer web site is like the difference between going to a bank teller, and that of using a bank machine. When you are in line at the bank, then “any” teller can service you. Their computer screens can bring up any customer, and all customers they want. The application is much like an access application.

However, an instant teller machine can ONLY display information about the one customer (you). So workflows designed around Access or say PowerApps don’t translate into the “model” required for a customer “portal” site.

For a customer site then logons, self-signup, and restricting the information to that one “customer” becomes an “opposing” goal to an internal application and business process in which all users tend to have use of all internal data.

So this “distinguishing” of customer facing and that of using say PowerApps to build some internal system needs to be clear.

None of the above is really “news”, but I often see the term “web” tossed around without consideration as to this being some internal business process system, or a system in which customers are self-served.

PowerApps (and possible flow) can often use data from these customer facing sites, but these tools cannot be used to build such customer facing sites - a big issue to keep in mind.

However, given the long life of .net, SharePoint, and now powerApps based around these technologies? Well, yes, I think they are all here to stay for the long run.

Regards,
Albert D. Kallal (Access MVP, 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 
RobKoelmans
post Nov 29 2018, 12:09 PM
Post#18



Posts: 472
Joined: 25-November 14
From: Groningen, Netherlands


Hi Albert,
Here my responses:



PowerApps are really limited to SharePoint, or say a 365 plan. You certainly might get it to pull some data from another web site – but that site will have to support some means to pull that data.
RK: This is what we use MS-Flow extensively for.

And same goes for flow – again this is really the next gen SharePoint workflow system.
RK: It's much more than that. I'd rather see it as the next generation webservice platform that has workflow as the main design element.

I mean, it not at all clear if re-writing an access application around power apps makes a lot of sense cost wise as opposed to say using remote desktop? If some forms for data entry is the goal, then sure, power apps will fit the goal, but NOT for customers!
RK: Flow with SP/AWA combinedly provides for a non-coder platform for business logic and data model/information model. It has fantastic performance and is completely independent from both SP and AWA in deployment. Providing PowerApp to intranet/extranet audience targets is cheap and effective. The way we have Access Desktop now, you only have to download the .accdb from your document list, provide flow with the SQL-credentials and it's running (without BE of SQL linked Tables or ODBC/ADO connections) and much better than it would with for instance ADO to Azure-SQL). What you modify on behalf of using Azure SQL is more than sufficient for using Flow if you use flow properly, that is Flow doing nothing except throwing a query script to SQL-server and return the JSON to Access Desktop (or PA).

And if one is going to jump on SharePoint (on premises), or say some office 365 plan to start building some web interface and forms, then again, no question power-apps is a great choice. (But again, not for customer facing sites).
RK: I don't see why not. We got some 20,000 accounts for free as MS Partner. Can't be expensive if you purchase some. You get a very advanced deployment platform that you'll never be able to build for yourself. All user management can be done in Flow, for instance.

And for those with experience using SharePoint workflows will nicely migrate over to “flow”. Flow has some great “web” feature that allow you to take actions on incoming email, or even say a file appearing in drobox. A great tool to string “steps” in a process, and add some automation to such a business process.
RK: True, and a lot more. We can practically eliminate all of our C# deployment.

However, none of the above technologies are suitable for public facing web sites, or so called “customer” portals.
RK: I agree on web sites but all the hard work is in Web-API's and those are gone completely.

Not that anything wrong is with such choices, but as noted, MUCH consideration in regards to the target audience is required here.
RK: yes, this is opening up complete new dimensions to non .Net coders like most on UA.

Attempting to “convert” or change say an access application, or some powerapp applications for internal business use as opposed to a customer web site are VERY different concepts and goals.
RK: Yes, but many do convert Access Applications to Azure-SQL. This is no different and much, much easier to deploy. Besides, this is safe. You have a wall around your database that you physically can't cross because you can provide each user his or her own API-key.

I mean, a customer web site is like the difference between going to a bank teller, and that of using a bank machine. When you are in line at the bank, then “any” teller can service you. Their computer screens can bring up any customer, and all customers they want. The application is much like an access application.
RK: no not in our case. The access desktop application will only serve you providing you only have your API-key.

However, an instant teller machine can ONLY display information about the one customer (you). So workflows designed around Access or say PowerApps don’t translate into the “model” required for a customer “portal” site.
RK: Yes, they do.

For a customer site then logons, self-signup, and restricting the information to that one “customer” becomes an “opposing” goal to an internal application and business process in which all users tend to have use of all internal data.
RK: No, it's dynamically depending on the request. For instance, you can design it to retrieve all customers, but only your own scheduler data. Just as with linked tables be it that you can't bypass the filtering settings because those are applied by Flow, not by the client.

PowerApps (and possible flow) can often use data from these customer facing sites, but these tools cannot be used to build such customer facing sites - a big issue to keep in mind.
RK: Yes, I'm convinced they can. Very well, actually.

However, given the long life of .net, SharePoint, and now powerApps based around these technologies? Well, yes, I think they are all here to stay for the long run.
RK: I'm sure of that.
Kind regards, Rob
This post has been edited by RobKoelmans: Nov 29 2018, 12:10 PM
Go to the top of the page
 
HeatherO
post Nov 29 2018, 04:12 PM
Post#19



Posts: 5
Joined: 29-August 18



I am so happy to see all of this feedback! I'm sorry that I'm just now responding but I never received any notifications that anyone had replied. I will take some time and read through this all of this this evening or tomorrow, share with my IT guy, and my guess is that we will probably have more questions smile.gif Thank you everyone who has responded!
Go to the top of the page
 
RobKoelmans
post Nov 30 2018, 05:23 AM
Post#20



Posts: 472
Joined: 25-November 14
From: Groningen, Netherlands


Hi Heather,
Good to know you're still looking for a feasible migration path.

Albert, on this: Access started SharePoint list support in version 2003. Here we are in 2018, and that support from Access is still going strong.
Did I miss something? We have always used Business Connectivity Services or External Lists as they were renamed to. We found direct list support practically unusable due to threshold/throttling issues, SharePoint loading the whole list in memory (not doing so over the threshold, just omitting parts of the result set and being incredibly slow).
Rob
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2018 - 07:02 PM