Complex Local Access Database - Enable Work from Home (1 Viewer)

[HairyArse]

I work for a small company (25 employees) 10 of which are database users. We have an internal Access Database we've been using and developing for the last 10 or so years. Needless to say it's quite complex and there's a lot to it.

Access front-end connected to a local MySQL server backend.

With the whole imminent impending Coronavirus shutdown I'm investigating ways to allow my users to work from home.

My first instincts have been to setup remote desktop access and allow users to connect in and use their desktop on their home device. However, I'm concerned about the load on the our connection and that this might ultimately be an extremely frustrating user experience.

With that in mind I have a couple of questions:

1) Is there a quick and easy way to connect an Access front-end to an online back-end so that I can just send each user a local copy of the front-end, connect it to an online service and they all use it locally and seamlessly? We already have various MySQL databases with domains we own but none of my existing providers allow external apps to connect to their databases.

2) Is there another recommended solution to easily convert an MS Access database to an online web-based solution? I am decent enough at PHP, HTML, CSS, MySQL that I could build these all manually but we have 165 tables and so that's a lot of manual development and form building and testing I don't have the time to do so I'm looking for some form of quick import.

3) Any other suggestions given the time and pressure constraints I'm under?

 

[CJ_London]

1) not quick and easy - the easiest option is to subscribe to Sql Azure which is sql server (for my sql see this https://play.google.com/store/apps/details?id=net.probytion.mymoclient&hl=en, but not had good reviews) - queries should pretty much work 'as is' but need to be designed to minimise network traffic (which you should be doing anyway) so I would anticipate some tuning will be required. You can scale the fees to provide more or less 'data activity' as required to maintain performance. Note access security is usually by IP address, so not suitable for users on the move - and they will need a fixed IP at home.

2) No - even with a good skillset, expect to budget 10 times the development time to do the equivalent in Access. There are online database/front end builders but they tend to be fairly basic in terms of functionality - however just found this which might fit your needs, you'll need to investigate https://go.caspio.com/migrate-acces...D_BwE&r=https://www.google.co.uk/&s=385&d=713

Another option is to move to terminal server - each user has their own profile for the FE and connect to BE on the same server - other than relinking and perhaps reconfiguring for different paths there should not be any changes - you say this is an old app so you may need to upgrade to a later version of Access - or use a runtime version. Benefit is performance - from a user perspective it will be like they have the back end on their local machine.

 

[HairyArse]

Hmm. The other thing I have to contend with is that these are low skilled users uncomfortable with anything outside of even the most basic of operations. Trying to talk them through installing remote desktop software alone was painful and time consuming.

I came across Caspio myself earlier and it does look good but I've never heard of it and was hoping there was a more standard option that the Access experts use and I just wasn't aware of.

To make matters worse my boss has just told me that she expects me to have a solution/recommendation in place by the end of the day. I said that that time constraint is not helpful and I don't want to make bad choices because I've been rushed and forced into making a decision, but, well, you all know the deal. We've all been there... The larger problem is that we've never implement WFH initiatives before as we all work within the same single site and are a manufacturer so it's never been necessary.

I don't see that I have the time, resource or budget to implement any of the actual best solutions and I think I'm just going to have to hope that remote desktop connections prove to be stable enough. My big concern is that I see that internet speeds and mobile connections are already suffering under the strain and I can see this only getting worse. Remote desktop is laggy and slow enough as it is without limited connectivity.

:Z

 

[HairyArse]

Caspio actually looks great but at $8 per month per additional form/report/table outside of the initial offering, for this project at least, it would soon get very expensive.

 

[vba_php]

HA,

Because access is a microsoft product, not to mention that it is a local application, conversion tools are probably not readily available, although many have attempted it. for instance, see here: https://www.google.com/search?q=ms+access+convert+to+php+interface

if you're building from scratch, it should not take too long if you know how to do a decent amount of things. If you say you have HTML and CSS skills, you can use these to build virtually *anything* because both the scripting languages have so many resources in them. I have all of these resources listed on my website if you want to see listings of them. But if you are going to use HTML, you should consider version 5.0 because it has new features that version 4.x[x] did not have, although most of the changes were about deprecating tags and issuing substitutes, which of course means absolutely nothing.

If you have 165 tables, my first thought is that your DB is bloated. that's a serious amount of data, even for a company with 100+ employees. but it depends on how many records each table of yours has. If you know PHP, you can automate the creation of all of your tables by using other programs as well. for instance, MS EXCEL. here's a sample script from me that I used on my own website to produce automated HTML code and slap it on a webpage so I didn't have to write it out by hand:

Function hyperlinks_add_to_HTML_table()
'this function adds the URL already in the HTML table to the HREF argument of the <A> tag.

Dim r As Range
Dim elLinkStart As Long
Dim elLinkEnd As Long
Dim elLinkLength As Long
Dim elLinkString As String
Dim elHrefStrOld As String
Dim elHrefStrNew As String
Dim elFinal As String

Application.ScreenUpdating = False

    For Each r In Range("a1", "a303")
        If InStr(r, "HREF=") > 0 Then
            elLinkStart = InStr(r, "=" & """" & """") + 4
            elLinkEnd = InStr(r, "</A>") - 1
            elLinkLength = elLinkEnd - elLinkStart + 1
            elLinkString = Mid(r, elLinkStart, elLinkLength)
            elHrefStrOld = "=" & """" & """"
            elHrefStrNew = "=" & """" & elLinkString & """" & " target=" & """" & "_blank" & """"
            r = Replace(r, elHrefStrOld, elHrefStrNew)
        End If
    Next r

Application.ScreenUpdating = True
End Function

you can do the exact same thing with a PHP script on a server. for instance, something like this:

CREATE TABLE table (
id INT(6) AUTO_INCREMENT PRIMARY KEY,
field1 VARCHAR(30) NOT NULL,
etc, etc....
)

and write a loop appropriately, like this:

<?php
for ($x = 0; $x <= 10; $x++) {
    //run mysqli_query() function here or PREPARED statement
}
?>

 

[HairyArse]

This system runs every facet of the business so that number of tables is entirely necessary. Actually there are only 153, but your point still stands.

But take just a simple Sales order for instance. The following tables are all required and essential.

Sales Order
Sales Order Detail
Customers
Customer Country
Products
Product Colours
Product Rank
Product Compositions
Product Sort Colours
Product Classification
Product Item Type
Product Secondary Type
Shipping Method

You get the idea.

Add to that Purchase Orders, Quotations, Stock, Customers, Supplier, Invoicing, Credits, Contracts and it's very easy to quickly use 153 tables. Of course I appreciate the concern, though.

 

[vba_php]

it doesn't matter what your tables look like, HA. you can still what I'm saying. do you really wanna create tables from scratch, one by one, in a mysql database? I would think not! once the tables are created too, you can use your mysql tool to import ALL your data into those tables through .sql files (or any other extension for that matter. ones that are parsable, that is). do you know how to produce them?

 

[HairyArse]

Oh and my tables are already in a MySQL database. I have XAMPP running on my database server and can manage my tables using PHPMYADMIN.

 

[vba_php]

so what do you need help with then? I don't understand. do you simply need to IMPORT all the data from all the tables? because you can also do that with code, although that would take some massive resources and literally take forever. =) LOL.

phpMyAdmin is a universal tool. It is OK, but I've seen better. you can import your data in all kinds of ways => .txt, .xls, .sql, etc, etc.....it goes on and on. in terms of automating the exporting process in access, that is easy since it is simply 1 VBA code routine and a loop, and probably a statement or function or 2. which you already know how to do ... and I'm pretty sure PHP has resources to import data into tables without running APPEND queries in the language. just 3 days ago, someone asked that same question over on https://www.phphelp.com/

 

[HairyArse]

All of my entry forms, reports and queries are handled by an Access front-end connected to a local back-end database server. All under this roof.

When my users work from home, they are not on this network and currently the only way for them to connect in to the database is through remote desktop access.

I have a VPN setup, but it's extremely slow even for one user to connect to the back-end never mind 10+.

 

[vba_php]

All of my entry forms, reports and queries are handled by an Access front-end connected to a local back-end database server. All under this roof.

When my users work from home, they are not on this network and currently the only way for them to connect in to the database is through remote desktop access.

I have a VPN setup, but it's extremely slow even for one user to connect to the back-end never mind 10+.

well I understand all that, HA, but I don't think we're on the same page here. access can connect to almost anything on the back-end. tables are stored on the back-end. so you say you're running XAMPP. I've also used that, but only for testing purposes. what exactly is the problem with moving your tables from one back-end to another? I don't think I'm quite following that. if your users are using remote desktop to access your back-end tables, you can simply have them use the internet to connect to it if you want to create a web-based interface using the "common 3 f/e designers" (html/css/js). thus, Access's front end that you have now is transformed into webpages with HTML elements and forms, and your back-end transforms into a mysql database on a server.

 

[CJ_London]

it would soon get very expensive.

anything web based is expensive - you'll be 'got' one way or another whether it be for data storage or connection or both. Terminal Server will cost around $25/person/month and will be the easiest to implement a matter of hours. Suggest you talk to your boss to get guidance about cost budget and time to implement. What is the benefit to the business to be able to connect your employees.

Trying to talk them through installing remote desktop software alone was painful and time consuming.

was? so they are already using remote desktop (which is how you would connect to terminal server)? If so that is half the job done.

 

[HairyArse]

Sorry, I've had a completely hectic morning and haven't made myself as clear as perhaps I needed to.

I was wondering what the typical professional approach was for people in this scenario.

My preferred option, and what I believe to be the best option longer-term is to migrate to a web-based system with HTML/PHP forms/reports/queries. The problem is, if you look at the example of one of my entry forms below, recreating this from scratch is a lot of work. There are lots of rules and exceptions and prompts specific to certain inputs etc, that would all have to be recreated in javascript or something similar and I reckon it would probably take me the best part of 2 days just to build this single form alone and maintain functionality.

I was hoping there was a solution, that I wasn't aware of that everyone else was using, that would easily export my forms to a web form but it seems short of spending stupid amounts of money on Caspio or something similar manually building them is the only way.

As it is I'm just installing RemotePC on everyone's PC and laptop and they're going to remote connect into their computers and just the Access database over the internet that way. It will be sluggish and laggy but at least all core functionality will work. I just worry that when internet speeds inevitably decrease over the coming week due to everyone being stuck at home that 10 people remote connecting into one small business with only 20/30mbps internet it might prove to be unusuable. Hence why I was looking for quick and easy ways to migrate to the web.

The other option is to find an internet host that will allow external connections and then send everyone a copy of the front-end MDB connected to this host. So far I'm yet to find one, so if anyone knows of one that supports it then let me know.

I appreciate everyone's help and advice but I fear given the time constraints and pressure I'm under, what I'm doing is about the best I can hope for in such a short space of time.

 

[vba_php]

Your best option if you don't want to build from scratch is simply to search Google for the conversion tools you need. More than likely they are out there because almost anything is available on the internet. But from your last post I don't think you understand that web-based forms are not Jen generally built with JavaScript. JavaScript is a manipulative language and so it provides events not HTML type structure

 

[vba_php]

Unless you are a talking of course about asynchronous languages that were created by Google and Facebook. Those are brand new and very difficult to learn so I'm sure you probably won't want to do that because that's a huge learning curve

 

[HairyArse]

Sorry, no I meant build the forms with PHP/HTML/CSS/MySQL and use a bit of javascript for rules, prompts, exceptions etc - I've done similar work for other parts of our system and I know it works and works well, it's just a time commitment I don't have right now.

I use the Metronic framework bought from themeforest and it works well, I just hoped there was a magical solution out there that everyone else was using but me.

 

[vba_php]

just hoped there was a magical solution out there that everyone else was using but me.

i seriously doubt it, only because your situation is unique. like I said before, the internet is such a massively diverse place, if *IT* is possible, you can bet your bottom dollar that someone has created it, either for free with advertisements integrated into it, or for a fixed price. have you searched yet?

 

[Minty]

RDP shouldn't be laggy or slow, effectively you are running (should be) the application locally on the same network as the BE.

If you are talking about RDP'ing into the user's desktops to then run Access again that shouldn't be noticeably worse than normal use. You are only moving the screen images and keyboard/mouse inputs over the internet.

Have a look at the google remote desktop client if whatever RDP client you are using is that bad.

Ideally, you should run a dedicated Terminal/Citrix server to facilitate this and then simply VPN in and connect via RDP to that user's front end folder. If the TS is on the same backbone as the SQL server it should be really quick.

You will probably want to look at your external connection bandwidth, 100Mbps uncontended or better for a lot of users.

 

[HairyArse]

Yeah that's the problem. I know a Citrix server is probably the best solution for all of this but I've had 1.5 days to set this all up as well as doing a thousand of other things and having never touched Citrix before it's not viable to buy the hardware, configure it, set up Citrix, train my colleagues and setup all their remote machines in such a short space of time. :S

 

[Minty]

Terminal Server was included in earlier versions of windows server 200x, and the way the licensing was arranged meant you could add users without needing licences immediately from memory. (I'll let you interpret that how ever you want )

It might be worth a look depending on what your servers are running on. Citrix is essentially an enhanced pretty UI version of RDP sat on top of a proprietary version of TS. (Bit more to it than that but you're probably getting my drift.)