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. tdf.RefreshLink End If End If DoEvents ' Enable other Windows processes to run. Next tdf ' Show a message about the Results. If Len(strErrors & "") = 0 Then MsgBox "All ODBC Tables were converted from Read-Only to Read-Write.", vbInformation Else Debug.Print vbCrLf & "Errors in Procedure ConvertTablesToReadWrite:" & vbCrLf & strErrors MsgBox "There are Error Messages listed in the Immediate Window!", vbExclamation End If EXITHERE: ' Release Memory. On Error Resume Next Set tdf = Nothing Set db = Nothing Exit Sub ERRHANDLE: ' Collect all Error info and continue to run the Procedure. strErrors = strErrors & "Error " & Err.Number & ": " & Err.Description & vbCrLf & vbCrLf Resume Next Resume End Sub
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 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.
Get the Access and Outlook Appointment Manager to manage all of your Outlook Calendar Appointments and Access dated information.
Patrick (Pat) Wood