Recently I had to link tables from one access file in another access file.

I did not intend to document such a trivial task on my webpage, but since the function I copied from the internet ran into an endless loop after I only provided an invalid file path I decided to implement something myself.

Enjoy.

' @Author
'   Alexander Bolte
'
' @ChangeDate
'   2014-07-29
'
' @Description
'   Linking given table from given source Access database in CurrentDb under provided target name.
'   If a linked table already exists under given name, the link will be broken and replaced with the new one.
'
' @Param strDatabaseSource
'   String providing the full path of the source access database.
' @Param strTableSource
'   String providing the source table name in the source access database.
' @Param strTableDestination
'   String providing the target table name in CurrentDB.
' @Return
'   True, if linkage succeeded, else false.
Public Function LinkTable( _
        ByVal strDatabaseSource As String, _
        ByVal strTableSource As String, ByVal strTableDestination As String _
    ) As Boolean
    Dim dbSource As DAO.Database
    Dim dbTarget As DAO.Database
    Dim dbDestination As DAO.Database
    Dim tdf As DAO.TableDef
    
On Error GoTo LinkTable_Err

    Call unlinkTable(strTableDestination)
    Set dbSource = DBEngine.Workspaces(0).OpenDatabase(strDatabaseSource)
    Set dbDestination = CurrentDb
    Set tdf = dbDestination.CreateTableDef(strTableDestination)
    tdf.Connect = ";DATABASE=" & strDatabaseSource
    tdf.SourceTableName = strTableSource
    dbDestination.TableDefs.Append tdf
    LinkTable = True

LinkTable_Err:
    If Err.Number <> 0 Then
	MsgBox Err.Description, vbCritical, cDialogTitle
	Err.Clear
	LinkTable = False
    End If
    
    If Not (dbSource Is Nothing) Then
        dbSource.Close
        Set dbSource = Nothing
    End If
    Set dbDestination = Nothing
    Set tdf = Nothing
End Function 

Last Updated: 29 July 2014

Hits: 9101

If you want to join two tables in MS Access using the LIKE operator you will fail receiving an error message stating the following.

"JOIN expression not supported."

MS Access SQL

The SQL producing above error looks as follows.

SELECT *
FROM RULE LEFT JOIN ENTALL ON CAS1 LIKE ('*' & RULE.[SOD CAT CLEAN] & '*');

 You can fix this by changing the SQL to define the JOIN condition in the WHERE part of a statement.

Last Updated: 11 August 2015

Hits: 9044

The following looked simple enough for me.

Source Code

Here a solution, which works for me however you have to be careful since it relies on a certain environment variable to be present. If this environment variable is not available the method automatically assumes the os to operate in 32 bit.

' @Author - Alexander Bolte
' @ChangeDate - 2016-03-27
' @Description - Determines if a users operating system is of 64 bit or 32 bit.
' In a 32 bit windows operating system the environment variable "ProgramW6432" simply does not exist.
' @Returns true, if the default directory for 32 bit applications is C:\Program Files (x86), else false.
Function Is64bit() As Boolean
    Is64bit = Len(Environ("ProgramW6432")) > 0
End Function

Unfortunately thie below did not work for me as it returned "x86" no matter if I started it on a 64 bit or 32 bit os.

Sub testArchitecture()
    MsgBox Environ("PROCESSOR_ARCHITECTURE")
End Sub

Last Updated: 27 March 2016

Hits: 9101