Access Database in the Cloud?

Throughout 2017, we were approached by a number of clients including KnoxMarine (Charleston, SC) and SeniorSmart (Sykesville, MD) to fix their ‘Cloud’ Access databases.  They were both using Microsoft Cloud to hold their Access Database files and were very disappointed with the results.  Primarily because this cloud solution creates a temporary copy of the Access Database on the local workstation, then overwrites the cloud version when the file is closed.

This, of course, means that if two users have the database open simultaneously, when the second user closes their version of the database, the first user’s database changes will be overwritten.

So what is a true Cloud Solution for an Access Database?  Then Answer: SQL Server or MySQL.

What you do is move your tables into SQL Server or MySQL.  This database needs to reside on a server that is on the internet (for instance, The Data Control LLC server we offer for $50/month).  Then modify your Access Database to connect to these tables through the internet.  We’ve done this for API Services (Newport News, VA) and HORWITZ, RICHARDSON & BAKER, LLC (Chicago, IL)

For instance:

 

Public Sub UpdateWebSQLConnections()
On Error GoTo err_Procedure

Dim rs As Recordset
Dim db As Database

Set rs = CurrentDb.OpenRecordset(“Select * from zstjncDataFiles_Tables Where DataFileID = 1”)
Set db = CurrentDb

Dim strWebIP As String
strWebIP = DLookup(“BackEndWebServer”, “zstblInformation”)

While Not rs.EOF
If UpdateSQLConnection(rs!SourceTableName, rs!DisplayTableName, “TCP/IP Address”, “ServerName”, “DatabaseName”, “Password”, “1”) Then
rs.MoveNext
Else
MsgBox “Connecting to SQL Server failed.”
Exit Sub
End If

Wend

    rs.Close
db.Close
exit_Procedure:
Set rs = Nothing
Set db = Nothing
Exit Sub

err_Procedure:
MsgBox Err & “: ” & Err.Description
Resume exit_Procedure

End Sub

If you’d like us to host your database in our cloud, we’ll convert your database for free.  If you want to host your database somewhere else, we’ll give you a price to convert the tables for you and modify your Access Database to use the new tables.

 

This entry was posted in Uncategorized. Bookmark the permalink.