We are professional web developers, but we faced many challenges when converting our agency from office to virtual in March 2020. Are you grappling with an LAN, ODBC and PHP coding? Our solutions included AppSheet, My SQL and ExpanDrive, all explained below. We thought sharing some discoveries could help others in the same situation.

Yes, I realize that many agencies have been virtual for years, and some even started out that way. But Dynamics Online started in 1997, and much of our infrastructure depended on a local-area network (LAN), postal mail and even a fax machine! So, when we started working from home last year, we had some big adjustments to make.

For a few months, we used our home computers or laptops to remote-connect to our networked office computers using LogMeIn, and thus were able to run all of the LAN applications. But that could be slow and took bandwidth from other family members. So, eventually, we brought our office computers home, after making the following changes and discoveries.

Goodbye LAN, hello cloud. When the pandemic hit, we were a 6-person agency running a few important multi-user applications over our office’s local-area network. Our most important LAN application was Microsoft Access, which we used as the database that ran our agency, functioning as a CRM, project management, timesheets and password management system. Access is a lesser-known member of the Office family that does not hold any explicit cloud-based features. But it was critical that we share up-to-the minute information about our website design and marketing projects.

We recalled that Microsoft Access does have support for ODBC, the database connectivity standard. And after watching many YouTube videos about it, we learned that it is possible to put the data component of an Access file on a remote service, and keep the queries, forms and reports on a local version of Access, one we could each run on our home computers.

But which shared database system to use? We first tried Google Sheets, the cloud-based spreadsheet system housed in Google Drive. We found only one vendor with a Google Sheets ODBC driver, which was very expensive. During our free trial, with some support from the vendor, we discovered that this driver did not work correctly. Too bad, because we had also started to develop a phone and computer app based on the Google Sheets data using the AppSheet platform. It would have allowed quick access to some of our important project data and client contact information.

We eventually implemented a working solution using our web server’s database, the popular MySQL platform. So our important data is on a web server, but we run a local version of Microsoft Access, just as if it were on a LAN, with a bit of balkiness on the first few queries. But otherwise, we can run all of our reports and enter data in the exact same way we did on the LAN. For each home computer, we had to install Microsoft’s Visual C++ Redistributable for Visual Studio, and MySQL’s ODBC Connector driver. One important step to use the database in Access is to create the DNS entry.

Once we got this working, we also developed a smaller web-based app to give us quick access to a subset of data. We programmed this with custom PHP coding. (Did I mention we are web developers?)

This app is helpful for our Mac users, since Microsoft long ago discontinued support for Access on Macs. Another important LAN application is QuickBooks, and we have not yet tackled cloud-based access. But only two of us need to access this, so one has it at home and other remotes into his house to run it on a networked computer. There are QuickBooks hosting services, but it seems that what they provide is very similar to our homegrown approach. QuickBooks Online is a different product than QuickBooks Desktop, so we did not want to convert to that.

File sharing on the cloud, not so fast! You have likely already used a file-sharing system like Google Drive or Microsoft’s OneDrive. But could it replace the lightning-fast access and practically unlimited storage we enjoyed using our network-attached storage (NAS) device in the office?

Not even close! We settled on Google Drive, since we already used Google Calendar and many other Google services. Since we frequently move directories of thousands of files related to our WordPress sites, we wanted a drag/drop interface we have enjoyed with Windows Explorer and Mac Finder. We found ExpanDrive, a clever utility that lets you assign a drive letter to a directory in your cloud storage. It supports Google Drive, OneDrive, and many other similar services, and even works via FTP to allow drag/drop access to files on a web server. Again, this gave us access to a shared set of files using the exact same interface we used at the office.

But…we have also suffered from slower and balky access and a delay in when others can see files we just uploaded. We also had an incident where Google Drive flagged many of our files and disallowed sharing them…still working to recover from that.

What about phones, postal mail, and meetings? Oh, those mundane aspects of running an office! Nothing too groundbreaking here, but we selected Talkroute to answer our phone line and present extensions for each of us that then ring on our cell or home phone. With the accompanying app, our corporate number shows on caller ID when we dial out. This is very low in cost and convenient, but also we get many fewer calls, and most of those phone meetings are now on Zoom.

For postal mail, we obtained a P.O. box from our local post office with the feature that its street address is our new corporate address, which looks great! We found this to be a much faster way to get mail compared to having it forwarded to one of our homes.

Speaking of getting checks in the mail, we ordered an electronic payment platform from our bank, which allows our clients and vendors to use our auto-pay service, and we can now get and send next-day payments. Why do paper checks still exist in 2021? [If you would like to pay your invoices this way,  email susan@dynamicsus.com!]

And finally, yes we have meetings on Zoom, and all the time! We started with GoToMeeting, which we had used for years as a screen-sharing device during phone meetings, and it morphed into a teleconferencing tool very similar to Zoom. But since all of our clients and friends already had the Zoom app downloaded, we decided to switch, and now we spend more time looking at each other than we did when we worked together every day in an office.

We hope you found something in our experiences helpful. So, is this virtual office setup what the future looks like? Thank goodness there were already so many cloud-based services for the workplace when the pandemic hit, and so the transition has gone fairly well with little interruption in productivity or accessibility to clients. Imagine if we had all been sent home in 1990 or earlier. I think we would be faxing all day long!