Skip Navigation LinksHome Page > Articles By Patrick Wood > Create a Read-Write WebApp Desktop Database

How to Create a Read-Write Desktop Database from Your Access Web App

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


Click the File Tab

Step 2: Click on the Manage Connections Button


Click on Manage Connections

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.

Click on Manage Connections

Now you can see that "Connections" is brightly colored and a light color now appears around the "From Any Location" Icon.

Color is added to Icon


Step 4: Click on "Enable Read-Write Connection"

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.

Color is added to Icon


Step 5: Click "View Read-Write Connection Information"

Click on View Read-Write Connection Information


Step 6: Copy and Paste all the connection information and save it in a secure file.

Copy Connection information


Step 7: Click the "Create Reports Button"

Click 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.

Read-Only Connection String


Step 9: Click on the "DATABASE TOOLS" Tab

Click DATABASE TOOLS Tab


Step 10: Click the "Visual Basic" Button

Click Visual Basic Button


Step 11: Click "Insert" and then "Module"

Click Insert 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.

The Code Window


Step 13: Run the Code

The Code Window

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:

Confirmation Message

If there were Errors you should see this message:

Error 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.


Conclusion

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.