At AccessExperts.net we just completed another project with Microsoft Access and SQL Server 2005. There were a couple of great developements with the project we want to share with you: That Access 2003 and 2007 work great with SQL Server over the Internet, and that you can use SQL Server's image field to issue updates to all users of your Access database. We will address the former here, an upcoming article will address the later.

Access over the Internet, seriously?
Yes, I've heard the arguments before, it can't be done, it's too slow, etc. I'm telling you it's possible with the right hosting provider and using SQL Server 2005 or later, along with the tips in this article.

In order to avoid problems with Access and the SQL Server over the Internet we recommend the following:
o Use a global connection variable in your code as much as possible. I resolve the issue by using a public function in my code that will open the connection for me for all my recordsets or commands. See the code below for recordsets:

Global con As New ADODB.Connection  'I use a global connection object in my code

Public Function OpenMyConnection(rs As ADODB.Recordset, strSQL As String, Optional _            rrCursor As rrCursorType, Optional rrLock As rrLockType) As ADODB.Recordset

   
    If con.State = adStateClosed Then 'Check if the connection is closed and if so, open it.
       'con is defined as a global variable in the project
        con.ConnectionString = conConnection & "User ID =" & ReadGV("UserID", strText) & ";Password=" & ReadGV("Password", strText)  'Replace the ReadGV and WriteGV with the appropriate user and password for your project.
        con.Open
    End If

    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = con
        .CursorLocation = adUseServer
        .CursorType = IIf((rrCursor = 0), adOpenStatic, rrCursor)
        .LockType = IIf((rrLock = 0), adLockReadOnly, rrLock)
        .Open strSQL
        If .EOF And .BOF Then
            NoRecords = True
            Exit Function
        End If
    End With
   
End Function

Keep less used data locally 
Most of my drop downs in my project are static data that will rarely change, (employees, salespeople, product names, etc.). Rather then use SQL Server to read the static data, I suggest creating local tables to your Access frontend and checking for new changes on start up. 

Use Views were possible!
You're going to have to learn how to create views on SQL Server in order to avoid long access times with the data. I recommend using a view anytime you're using a list or combo box that need access to dynamic data. It simply takes too long for Access to download all of the table data, calculate the query, then display it to the user. Better to create the view and link it to your access data as it where another table. I use views were I don't need to edit data, but you can use them for editing as well.

SQL Server Security - Bonus!
Possibly one of the best uses of SQL Server is the security benefits with Microsoft Access. You will need to define public roles and users in the system, along with specifying security rights with every object in the database: both tables and views.

Educate your users
Using Access with a SQL Server on the Internet is not the same as a local SQL Server or Access file on the network. Data access speeds may vary from time to time and location, you may get disconnected from the server, especially when you walk away from the open application for a long time.

I hope these tips help you make the leap to creating Access databases over the Internet either for a client or for your ornanization. Of course AccessExperts.net can help you with any aspect of your Access to Internet project, give us a call today at 312.242.3346 today!