Access will automatically create an Access Reports Desktop Database for you that is Read-Only but you are not limited
to just that option. With a little code you can easily change those linked tables to Read-Write tables. You can enjoy
all the rich features and flexibility we all have come to love with Access. It is good to know that using an Access Web
App does not limit you to having that as the only tool in your developer tool belt.
Since Web Apps are new to many of us I am going to start from the beginning and show step-by-step how to create an
Access Desktop Database with Read-Write linked tables to your Web App's Azure SQL Database. You begin with your Web
App opened in Access.
Step 1: Click the File Tab
Step 2: Click on the Manage Connections Button
Step 3: Enable Connections
I always enable connections from any location. The long and complex UID and Password should provide sufficient security.
And you can always change the Password.
Now you can see that "Connections" is brightly colored and a light color now appears around the "From Any Location" Icon.
Step 4: Click on "Enable Read-Write Connection"
Now you can see that a light color surrounds the "Enable Read-Write Connection" Icon indicating the connection is enabled.
Step 5: Click "View Read-Write Connection Information"
Step 6: Copy and Paste all the connection information and save it in a secure file.
Step 7: Click the "Create Reports Button"
Access will create a Desktop Database for Reports in the folder you select.
Step 8: Open the Reports Database
The database will have linked Read-Only tables. You can create Reports to view the data but you cannot edit, insert, or delete any of the data.
When you hover the cursor over the linked tables in the Navigation Pane you can see the Read-Only Connection String. But you can change the
Connection String so the tables will be Read-Write with just a little code.
Step 9: Click on the "DATABASE TOOLS" Tab
Step 10: Click the "Visual Basic" Button
Step 11: Click "Insert" and then "Module"
Step 12: Copy the Code Below and Paste it in the Code Window
' Procedure : ConvertTablesToReadWrite
' Author : Patrick Wood - Gaining Access Technologies - http://gainingaccess.net/
' Purpose : Convert Read-Only Access Web App Linked Tables to Read-Write.
' Arguments : strReadWritePWD - Your Read-Write Password.
' Example : Call ConvertTablesToReadWrite("MyReadWritePWD")
' : You are welcome to use this code in your Applications and share it if you
' : keep this header with the code. There is no warranty expressed or implied.
Public Sub ConvertTablesToReadWrite(strReadWritePWD As String)
On Error GoTo ERRHANDLE
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strConnect As String
Dim strReadOnlyPWD As String
Dim strErrors As String
Dim lngStart As Long
Dim lngEnd As Long
Set db = CurrentDb
'Loop through the TableDefs Collection.
For Each tdf In db.TableDefs
'Verify the table is an ODBC linked table.
If Left$(tdf.Connect, 5) = "ODBC;" Then
' Skip System tables.
If Left$(tdf.Name, 1) <> "~" Then
Set tdf = db.TableDefs(tdf.Name)
' Get the Table's Connection String.
strConnect = tdf.Connect
' Get the Read-Only Password from the Connection String.
lngStart = InStr(1, strConnect, "PWD=")
lngEnd = InStr(lngStart, strConnect, ";")
lngStart = lngStart + 4
strReadOnlyPWD = Mid$(strConnect, lngStart, lngEnd - lngStart)
' Replace the Read-Only UID with the Read-Write UID.
strConnect = Replace$(strConnect, "_ExternalReader", "_ExternalWriter")
' Replace the Read-Only Password with the Read-Write Password.
strConnect = Replace$(strConnect, strReadOnlyPWD, strReadWritePWD)
tdf.Connect = strConnect
' Apply the changes to the Table.
DoEvents ' Enable other Windows processes to run.
' Show a message about the Results.
If Len(strErrors & "") = 0 Then
MsgBox "All ODBC Tables were converted from Read-Only to Read-Write.", vbInformation
Debug.Print vbCrLf & "Errors in Procedure ConvertTablesToReadWrite:" & vbCrLf & strErrors
MsgBox "There are Error Messages listed in the Immediate Window!", vbExclamation
' Release Memory.
On Error Resume Next
Set tdf = Nothing
Set db = Nothing
' Collect all Error info and continue to run the Procedure.
strErrors = strErrors & "Error " & Err.Number & ": " & Err.Description & vbCrLf & vbCrLf
The code should look like this in the Code Window.
Step 13: Run the Code
In the Immediate Window type in "Call ConvertTablesToReadWrite("")" Then enter the Read-Write Password you copied
and saved in Step 6 between the quotation marks. To Run the code click just to the right of the Parentheses and press the "Enter"
Key on the Keyboard. When the code is finished running a Message Box should appear.
If there were no Errors you should see this message:
If there were Errors you should see this message:
The Errors information is there to help you fix any problems. But an error does not necessarily mean the conversion
to Read-Write failed. To check if the tables are now Read-Write you need to do the next step.
Step 14: Completely close Access to clear the Cache then open your database and try it out
Because Access Caches the connection string, it has cached your Read-Only connection string. You must first close Access and open
it again to use the new Read-Write connection string. Note: you must close Access itself and not just the database to clear the cache.
Access Web Apps Hybrid Possibilities
Congratulations! You now have a new Access Web App Hybrid Application! And there are many other means of adding new functionality to
Access Web Apps. We do not have to see Access Web Apps as silos all alone unto themselves. We can take the broader view of seeing Access
Web Apps as a part of a dynamic and feature laden set of Applications. We can use other Apps and Applications with our Access Web Apps.
We can use our Web Apps with SQL Server Management Studio, Visual Studio, Office Web Apps, and other Apps and Applications to create a
broad collection of powerful and versatile Applications. We can build solutions based on Access Web Apps. Or we can build solutions to
which we add Access Web Apps to enable our existing Applications to be used on the Web. We have not reached the limit of what can be
achieved with Access Web Apps.
Exploring Grow-up Paths for Access Web Apps
There are also a number of grow-up paths for Access Web Apps.
You can migrate your Web App's Azure SQL Database Tables and data to a SQL Server Database or a free standing Azure SQL Database not
associated with a Web App. Or you can easily convert your linked tables to local Access Desktop tables. You can learn about this and
additional helpful and informative resources for Access Web Apps from Access MVPs on the web page entitled
The Free Access Web App Reference from Access MVP Crystal Long.
With your new Read-Write desktop Access Database with Linked Tables you can now use the rich variety of features of Access. You can create
Forms and Queries that will write to your Web App's Azure SQL Database. Your Web App is not in a silo by itself and you can do almost
everything that can be done with an Access desktop database. You can create new Access local tables. You can send emails to just about anyone.
You can use automation to work with Office and other applications. You can use the broad and rich features of Windows API's. With Access
Web Apps you can have a multitude of tools in your developer tool belt.
Email me at
or call us at
We use Microsoft Azure SQL Databases to provide safe and affordable 24/7 internet access to your company's vital information.
Contact us for a Free Consultation, feedback, or more information:
Page layouts based on Matthew James Taylor's Perfect multi-column CSS liquid layouts