Access Version Issues

I have a problem. In my office we have multiple versions of Access. The code I will attach here was developed in 2003. it works in some versions but apparently not 2010 or above. In a nut shell what it does is create a new database, create a new table in the database and then links the table. The reason I do this is because the information I need is from a pass through query and I can not have a sub form based on a pass through. Therefore in order to prevent database bloat I create the new database. I don't have to do it this way but it is what works for me, until recently, so if anybody has a better way of doing what I want please feel free to share that too.

Sub CreateTempTables()
Dim strTempDatabase As String
' This subroutine illustrates how to use a temporary MDB in your app.
' If the temporary MDB is present then delete it.
'    The name of the temporary MDB created is the same as the current Front End (FE) name with
'    " temp" added to the end of the name.
' Create the temporary MDB.
' Create the temporary table(s) required in the temporary MDB.
' Link to those tables within your current FE
'   Do whatever you want
' Unlink the tables'
' Delete the temporary MDB


Dim tdfNew As TableDef, rs As Recordset
Dim wrkDefault As Workspace
Dim dbsTemp As Database
Dim strTableName As String

On Error GoTo LogError
' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)
strTempDatabase = Left$(CurrentDb.NAME, Len(CurrentDb.NAME) - 4) & "temp.mdb"

' Make sure there isn't already a file with the name of
' the new database.

If Dir(strTempDatabase) <> "" Then Kill strTempDatabase

'Create a new temp database
Set dbsTemp = wrkDefault.CreateDatabase(strTempDatabase, dbLangGeneral)
strTableName = "tblCarrierLoadHistory"

'strBracketedTableName = "[" & strTableName & "]"
' Delete the link to the temp table if it exists

If TableExists(strTableName) Then
   CurrentDb.TableDefs.Delete strTableName
End If
    
' Create the temp table
Set tdfNew = dbsTemp.CreateTableDef(strTableName)
With tdfNew
    .Fields.Append .CreateField("BCCARR", dbText, 8)
    .Fields.Append .CreateField("Origin", dbText, 30)
    .Fields.Append .CreateField("Dest", dbText, 30)
    .Fields.Append .CreateField("ORCOMC", dbText, 6)
    .Fields.Append .CreateField("Rev", dbDouble)
    .Fields.Append .CreateField("BAmt", dbDouble)
    .Fields.Append .CreateField("ORODR#", dbText, 7)
    .Fields.Append .CreateField("NegDate", dbDate)
    dbsTemp.TableDefs.Append tdfNew
End With
dbsTemp.TableDefs.Refresh

   ''' Link to the Import tables in the temp MDB ''''''
Dim tdfLinked As TableDef
Set tdfLinked = CurrentDb.CreateTableDef(strTableName)
tdfLinked.Connect = ";DATABASE=" & strTempDatabase
tdfLinked.SourceTableName = strTableName
CurrentDb.TableDefs.Append tdfLinked
CurrentDb.TableDefs.Refresh
RefreshDatabaseWindow
Set rs = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset, dbAppendOnly)
    
'write the results of the query into the table
CurrentDb.Execute ("INSERT INTO tblCarrierLoadHistory Select PTCustomerService.* from PTCustomerService")
CurrentDb.TableDefs.Refresh
    

    
'     'Unlink the tables
'    CurrentDb.TableDefs.Delete strTableName
'
'     'Delete the temp mdb
'    strTempDatabase = Left$(CurrentDb.NAME, Len(CurrentDb.NAME) - 4) & " temp.mdb"
'    Kill (strTempDatabase)
ExitPoint:
    On Error GoTo 0
    rs.Close
    dbsTemp.Close
    Set rs = Nothing
    Set dbsTemp = Nothing
    Exit Sub
LogError:

    If ERR.Number = 70 Then
        Exit Sub
    Else
        Call LogError(ERR.Number, ERR.Description, "Create Tables" & " Create Load History Tables", , True)
        Resume ExitPoint
    End If

    
End Sub

Function TableExists(strTableName As String) As Integer
    ' Checks for the existance of a specific table

    'Added the tabledefs.refresh as tables just added in this session weren't
    '   being picked up.

    Dim dbDatabase As Database, tdefTable As TableDef

    On Error Resume Next
    Set dbDatabase = DBEngine(0)(0)
    dbDatabase.TableDefs.Refresh
    Set tdefTable = dbDatabase(strTableName)
    
    ' If an error occurred the tabledef object could not be accessed and
    ' therefore doesn't exist. This could cause problems in a secured environment
    ' though as access may be denied.
    If ERR = 0 Then
        TableExists = True
    Else
        TableExists = False
    End If

End Function

Select allOpen in new window