Dave's Technophorical Times

A blog about Microsoft's Technologies!
SharePoint :: MVC :: ASP.NET :: IIS :: SQL Server :: Visual Studio :: MS Access

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


  • Sub EnvironListing()
  •  Dim I As Integer
  •  I = 1
  •   Do Until environ(I) = ""
  •     Debug.Print environ(I)
  •     I = I + 1
  •   Loop
  • End Sub

  • 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
            End If
        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
            End If
    End Sub

    The Blogger

    Dave Stuart I'm a Developer with a passion for coding. I enjoy the challengers that come with the job! SharePoint is one of my expert areas along with .NET Web Development with MVC and good old MS Access VBA coding. I Blog so that I can remember how I did that way back when; PLUS all this stuff is searchable! I constantly study and run my own business, Dafran Inc. I have passed 22 Microsoft Exams since 1998 when I first jumped on the treadmill of knowledge. I hope that you enjoy this Blog as much as I enjoy updating it. All the very best from Calgary, Alberta, Canada. contact me at linkedin @ dafran.ca


    <<  March 2021  >>

    View posts in large calendar

    Sign in