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:
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
db.Close
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.