So you have RemoteApp up and running and you have made an MS Access database available in the list of Remote Apps, but when you open it you get a message stating that the Database is read-only and that you can only update Linked tables!

Most likely this is a permissions error. Make sure that the RemoteDesktop Users group, whatever that is named on your server, has full permissions on the Folder housing your database, and on the database file itself.

Hope this helps someone!

When developing your Excel or Access application, it’s not uncommon to need to capture your user’s domain/username for one reason or another. A user’s domain/username combination is the traditional unique identifier for a user - often captured by programmers to do things in like:

• Help manage user login and permissions
• Create a history of who used the application
• Capture the name of each user who made the changes to data
• Personalize outputs and program messages for users

.
There are literally half a dozen ways to capture a user’s domain name and user name. In this post, I’ll give you a very easy method using the Environ function.

Environ stands for Environment, and refers to environment variables in an operating system. Environment variables are specially named aliases for specific system properties, exposed as a kind of shortcut for system administrators and programmers.

Although majority of these Environment variables are useless to your average Excel/Access developer. There are a few that can prove to be useful. Two of these are the UserDomain and UserName variables. We can use these to capture a user’s Domain\UserName.

To demonstrate this, I’ve entered the following function into a standard module. You can call this from a form or query!

Function getReturnUName()
       getReturnUName = Environ(“UserDomain”) & “\” & Environ(“Username”)
End Function

Do you use the report wizard to help you create reports in Microsoft Access 2007? If you do, you are probably aware of the control layout. Many controls are automatically grouped into a layout when you use the wizard to create a report. These control layouts help you to arrange the controls, i.e. text boxes and labels, as a group. When you move or resize one control, the other controls will automatically adjust accordingly. While using layouts can save you time when manipulating controls, they can also be restricting. It is possible to remove a control layout. To remove a control layout, you need to view the report in the design or layout view. Once in the appropriate view, select the controls you want to remove from the layout. Then select the Arrange tab on the ribbon and click the Remove button located within the control layout section. With the control layout removed you will be able to independently move and resize the controls within your report.

If you want to switch on the Trace log for MSAccess linked tables to SQL Server then set the following registry key to 1. If you can't see the key then create a new REG_DWORD key and set the value to 1. The log file will be called sqlout.txt and it will be produced in the Default database folder as indicated in MSAccess Tools->Options->General->Default database folder.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC] TraceSQLMode

Hope this helps! Also don't forget to switch it off when you don't need it.

This is how to list all Tables and Fields in an Access Database Project (ADP).

Option Compare Database
Option Explicit

Public Sub ListTablesAndFieldsADP()
    '---------------------------------------------------------------------
    'Reference is needed for: Microsoft ADO Ext. 2.x for DDL and Security
    '---------------------------------------------------------------------
    Dim cat As New ADOX.Catalog 
    Dim tbl As ADOX.Table       
    Dim col As ADOX.Column       
    Dim fs As Object
    Dim output As Object

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set output = fs.CreateTextFile("c:\Tables_Views_And_Fields.txt", True)
    
    Set cat.ActiveConnection = CurrentProject.Connection
    
    For Each tbl In cat.Tables
        If Left(tbl.Type, 6) <> "SYSTEM" Then
            output.WriteLine tbl.Name & " (" & tbl.Type & ")"
            For Each col In tbl.Columns
                output.WriteLine "  " & col.Name
            Next
            output.WriteLine
        End If
    Next
    
    Set col = Nothing
    Set tbl = Nothing
    Set cat = Nothing
End Sub

This is how to list all LINKED Tables and Fields in an MS Access Database.
To list Local Tables, remove Len(tdfCurrent.Connect) > 0

Public Sub ListTablesAndFields()
    Dim fld As DAO.Field
    Dim tdfCurrent As DAO.TableDef
    Dim dbCurrent As DAO.Database
    
    Set dbCurrent = CurrentDb
    
    Dim fs As Object
    Dim output As Object
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set output = fs.CreateTextFile("c:\Tables_Views_And_Fields.txt", True)
    
    For Each tdfCurrent In dbCurrent.TableDefs
        If Len(tdfCurrent.Connect) > 0 And Left(tdfCurrent.Name, 4) <> "~TMP" Then
            output.WriteLine tdfCurrent.Name
            For Each fld In tdfCurrent.Fields
                output.WriteLine "   " & fld.Name
            Next
            output.WriteLine
        End If
    Next
End Sub