Dave's Technophorical Times

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

This is a very good article about Working with VBA in the 32-bit and 64-bit Versions of Office 2010 http://msdn.microsoft.com/en-us/library/ff700513(v=office.11).aspx

I have re-posted it and referenced it here! Mainly for my own reference :-)

Office 2010 32-bit and 64-bit Client Installations

Microsoft offers a 32-bit client installation and 64-bit client installation of Microsoft Office 2010. If you use a 64-bit version of Microsoft Windows, you can choose which version of Microsoft Office to use. By using the 64-bit version, you can experience improved application performance by using more physical memory for storage and by moving more data in fewer operations.

However, there are considerations when moving your 32-bit applications to or creating solutions for the 64-bit version of Microsoft Office. For example, applications written for the 64-bit version likely do not work in previous versions of Microsoft Office. Also, calls to Microsoft Windows Application Programming Interface (API) functions from your application code may not work.

Calls to 64-bit Windows API functions from 32-bit solutions (and the opposite) may lead to erratic behavior or system crashes resulting from the truncation of data or overflows into protected memory spaces. To compensate for this, Microsoft Office provides a 32-bit version and 64-bit version of several of the Declare statements used in the Windows API. To ensure compatibility, you must change the Declare statements in solutions to differentiate between 32-bit calls and 64-bit calls. These differences and remedies are described in the article Compatibility Between the 32-bit and 64-bit Versions of Office 2010.

Office 2010 introduces a new version of Microsoft Visual Basic for Applications (VBA) known as VBA 7.0 that is updated to work with 64-bit client installations. VBA 7.0 improves the performance of your Microsoft Office applications through 64-bit performance improvements. VBA 7.0 also enables you to create applications that are compatible with Office 2007 or continue to use VBA solutions created in previous versions. To improve compatibility of application code targeted to a particular version of Microsoft Office, there is a compilation constant (VBA7) that enables you to determine the version of VBA you are using and execute the correct code. Note that if your applications are only written for the 32-bit versions of Microsoft Office, no changes are necessary to your code.

There is also a compilation constant (Win64) that enables your Office 2010 solutions to work with both the 32-bit version and 64-bit version. Both of these compilation constants are demonstrated in the following sections.

As stated previously, VBA 7.0 works with 64-bit versions of Microsoft Office. To enable VBA solutions created for 32-bit versions to continue working in Office 2010, the compilation constant (VBA7) tests to determine the VBA version used in the solution.

The following code example shows how to use the VBA7 constant. This example enables you to find the location and size of the main window in Microsoft Excel 2010. To do this, first, a user-defined type, RECT, is created. Next, the Windows API functions, FindWindow and GetWindowRect, are defined to return the dimensions of the window. Because there is a 32-bit version and 64-bit version of these functions, the VBA7 compilation constant directs the compiler to the correct section of code. Then, the DisplayExcelWindowSize subroutine is called. This subroutine calls the FindWindow and GetWindowRect functions. Finally, a message box appears with the dimensions.

' A user-defined type to store the window dimensions.
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type

' Test which version of VBA you are using.
#If VBA7 Then
   ' API function to locate a window.
   Declare PtrSafe Function FindWindow Lib "user32" _
      Alias "FindWindowA" ( _
      ByVal lpClassName As String, _
      ByVal lpWindowName As String) As LongPtr
   ' API function to retrieve a window's dimensions.
   Declare PtrSafe Function GetWindowRect Lib "user32" ( _
      ByVal hwnd As LongPtr, _
      lpRect As RECT) As Long

   ' API function to locate a window.
   Declare Function FindWindow Lib "user32" _
      Alias "FindWindowA" ( _
      ByVal lpClassName As String, _
      ByVal lpWindowName As String) As Long
   ' API function to retrieve a window's dimensions.
   Declare Function GetWindowRect Lib "user32" ( _
      ByVal hwnd As Long, _
      lpRect As RECT) As Long
#End If

Sub DisplayExcelWindowSize()
   Dim hwnd As Long, uRect As RECT
   ' Get the handle identifier of the main Excel window.
   hwnd = FindWindow("XLMAIN", Application.Caption)
   ' Get the window's dimensions into the RECT UDT.
   GetWindowRect hwnd, uRect
   ' Display the result.
   MsgBox "The Excel window has these dimensions:" & _
      vbCrLf & " Left: " & uRect.Left & _
      vbCrLf & " Right: " & uRect.Right & _
      vbCrLf & " Top: " & uRect.Top & _
      vbCrLf & " Bottom: " & uRect.Bottom & _
      vbCrLf & " Width: " & (uRect.Right - uRect.Left) & _
      vbCrLf & " Height: " & (uRect.Bottom - uRect.Top)
End Sub

To test this solution, add a module to Excel 2010, insert the previous code, and then run the macro. You should see a message displayed similar to Figure 1 with the dimensions, in pixels, of the window.

Figure 1. Message with the dimensions of the main Excel window

Message with the Excel window dimensions

Resize the Excel window and rerun the macro. You should see that the values changed.

In some solutions, you must provide code for the 32-bit version of Office 2010 and code for the 64-bit version of Office 2010. And as in the previous example, you must call different versions of the Windows API functions. To do this, the Win64 compilation constant is available as shown in the following example.

' Test whether you are using the 64-bit version of Office 2010.
#If Win64 Then
   Declare PtrSafe Function GetTickCount64 Lib "kernel32" () As LongLong
   Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
#End If

In this example, if you code is running in the 64-bit version of , the GetTickCount64 function is called. Otherwise, the GetTickCount function is called.

VBA 7.0 is the latest version of VBA that improves the performance of applications that are created in Office 2010. To enable you to continue using your legacy solutions in different versions of Microsoft Office, the VBA7 compilation constant is available. Likewise, because there is now a 32-bit version and 64-bit version of Office 2010, you use the Win64 compilation constant to direct the compiler to the run the correct section of code in your VBA applications.

So you are getting ###### in your report!

The Currency format adds "White Spaces" to the right (and left I think).  So changing the format from Currency to $#,##0.00 for decimal place fields and $#,##0 for no decimal places gives to more room. You can also set the Left and Right padding to ZERO.  The advantage with the new format is that it removes "White Spaces" to the right that are automatically added when using the Currency format.



Reference: http://support.microsoft.com/kb/209207

The following table lists the Microsoft Access command-line options.

   Option       Effect                                    Applies to
   database     Opens the specified Microsoft Access      Access 
                database or Microsoft Access project.     database or 
                Include a path if necessary. If you are   Access 
                running Windows 95, the default path is   project
                your My Documents folder.	
   /excl        Opens the specified Access database for   Access 
                exclusive access. To open the database    database 
                for shared access in a multiuser          only
                environment, omit this option. 
   /ro          Opens the specified Access database or    Access 
                Access project for read-only access.      database or 

   /user        Starts Access using the specified user    Access 
   user name    name.                                     database 

   /pwd         Starts Access using the specified         Access 
   password     password.                                 database 

   /profile     Starts Access using the options in the    Access 
   user         specified user profile instead of the     database or 
   profile      standard Microsoft Windows registry       Access 
                settings created when you installed       project
                Access. This replaces the /ini option  	
                used in versions of Access earlier   	
                than Access 97 to specify an   	
                initialization file.

   /compact     Compacts and repairs the Access           Access 
   target       database, or compacts the Access project, database or 
   database or  specified before the /compact option and  Access 
   target       then closes Access. If you omit a target  project
   Access       file name following the /compact option, 	
   project      the file is compacted to the original  	
                name and folder. To compact to a different  
                name, specify a target file. If you don't 	
                include a path in target database or target 	
                Access project, the target file is created  	
                in your My Documents folder by default. In 	
                an Access project, this option compacts 	
                the Access project (.adp) file but not the 	
                SQL Server database. 
   /repair      Repairs the Access database specified     Access 
                before the /repair option and then        database 
                closesAccess. In Access 2000, compact     only
                and repair functionality is combined  	
                under /compact. The /repair option is 	
                supported for backward compatibility. 
   /convert     Converts an Access database in an         Access 
   target       earlier version to an Access 2000         database 
   database     database with a new name and then closes  only
                Access. Specify the source database  	
                before using the /convert option. 	

   /x  macro    Starts Access and runs the specified      Access 
                macro. Another way to run a macro when    database or 
                you open a database is to use an          Access 
                AutoExec macro.                           project
   /cmd         Specifies that what follows on the        Access 
                command line is the value that will be    database or 
                returned by the Command function. This    Access 
                option must be the last option on the     project
                command line. You can use a semicolon (;)	
                as an alternative to /cmd. 	

   /nostartup   Starts Access without displaying the      Access 
                startup dialog box (the second dialog     database or 
                box that you see when you start Access).  Access 

   /wrkgrp      Starts Access using the specified         Access 
   workgroup    workgroup information file.               database 
   information                                            only

   /runtime     Starts Access in run-time mode.           Accesss 
                                                          database or 



  • To run a Visual Basic for Applications procedure when you open a database, use the RunCode action in the AutoExec macro or in the macro that you run by using the command-line option /x. You can also run a Visual Basic procedure when you open a database by creating a form with a Visual Basic procedure defined for its OnOpen event. Designate this form as the startup form by right-clicking the Database window, clicking Startup, and then entering that form in the Display Form/Page box.
  • To specify a forward slash (/) or semicolon (;) on the command line, type the character twice. For example, to specify the password ;mjs/md on the command line, type ;;mjs//md following the /pwd command-line option.
  • Examples:

    You can type the following command in the Run dialog box to open an Access 2000 database in exclusive mode that runs the specified macro at the startup:
    "C:\Program Files\Microsoft Office\Office\msaccess.exe" "C:\Program Files\Microsoft Office\Office\samples\northwind.mdb" /Excl /X Add Products
    To open an Access 2002 database in exclusive mode that runs the specified macro at the startup, type the following command in the Run dialog box:
    "C:\Program Files\Microsoft Office\Office10\msaccess.exe" "C:\Program Files\Microsoft Office\Office10\samples\northwind.mdb" /Excl /X Add Products
    Similarly, if you want to open an Access 2003 database in exclusive mode that runs the specified macro at the startup, type the following command in the Run dialog box:
    "C:\Program Files\Microsoft Office\Office11\msaccess.exe" "C:\Program Files\Microsoft Office\Office11\samples\northwind.mdb" /Excl /X Add Products

You get a Type Mismatch Error on the GetFolder line!! Why is that? Because the Folder declaration needs to be like this: Dim oFolder As Scripting.Folder. This will be the case even if you have a reference to Microsoft Scripting Runtime.

The code below will fail. Add the Scripting. to the Folder declarion and it will work!

Dim oFileSystem As New FileSystemObject
Dim oFolder As Folder
Dim oCurrentFile As File
Dim oFileColl As Files

Set oFolder = oFileSystem.GetFolder("d:\data\bradfb\Desktop\CR Disposition\")
Set oFileColl = oFolder.Files

If oFileColl.Count > 0 Then
    With lstFiles
        For Each oCurrentFile In oFileColl        
            .AddItem oCurrentFile.Name            
        .ListIndex = 0        
    End With
End If

Set oFileSystem = Nothing
Set oFolder = Nothing
Set oFileColl = Nothing

The entire Detail section of a Microsoft Access form goes blank sometimes. The text boxes you see in design view disappear when you go to use the form, leaving nothing but empty space.

The Cause

It happens when both these conditions are met:

  1. There are no records to display, and
  2. No new records can be added.

Condition (a) can be triggered in several ways. Examples:

  • The form's Data Entry property is set to Yes. (This means the form shows no existing records, i.e. it is for entering new ones only.)
  • The form has a Filter applied (or is opened with a WhereCondition) that yields no records.
  • The form is based on a query where the criteria yield no records.
  • The form is based on a table that has no records.

Condition (b) can be also be triggered by several things:

  • The form's Allow Additions property is set to No.
  • The form's Recordset Type property is set to something other than Dynaset.
  • The form is based on a read-only query. (If you cannot add a record directly to your query)

You can demonstrate the problem with any form, just by setting its Data Entry property to Yes, and Allow Additions to No.

If it has Form Header and Form Footer sections, controls in these sections will still be visible but they may not work properly.


You cannot prevent this from happening, unless you can avoid (a) and (b) being true at the same time.

If you have set Allow Additions to No so as to prevent new records, there is a simple workaround. Set Allow Additions back to Yes, so the form can show the new record when there are no others. You can prevent new records by canceling the BeforeInsert event of the form. Steps:

  1. Set the Before Insert property of the form to:    [Event Procedure]
  2. Click the Build button (...) beside this. Access opens the code window.
  3. Insert the line Cancel = True between the other two. The code will look like this:
        Private Sub Form_BeforeInsert(Cancel As Integer)
            Cancel = True
        End Sub

If you cannot prevent condition (b), you might be able to prevent condition (a). For example, if you are opening a form with a WhereCondition that could result in no records, you might prefer to display a dialog rather than the blank form. To do that, cancel the Open event of the form:

Private Sub Form_Open(Cancel As Integer)
    If Me.RecordsetClone.RecordCount = 0 Then
        Cancel = True
        MsgBox "No records"
    End If
End Sub

(Note: The procedure where you used OpenForm will be notified with error 2501 that the form did not open. Use error handling in that procedure to trap the message.)

There are times when you cannot prevent (a) and (b) happening together. A complex search form may have to use a read-only query, and the user's criteria may return no results. For these cases, place the criteria boxes in the form header section, and accept the fact that users will be okay if they see nothing when the search returns no results.

January 2010 update: In Access 2007 and later, check that the form's Filter On Load property is set to No so that it does not automatically apply a previously saved filter when you open  the form.

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

    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


    <<  June 2019  >>

    View posts in large calendar

    Sign in