Temp Database problems with Access 2007

Greetings,

I've been using Tony Toew's Temp table module and I've now upgraded to
Acesss 2007. The database is still in 2003 format. The problem I'm
having is that the temp tables are being created in the Access 2007
format when the code runs and I receive the following error.

Microsoft Office Access does not support linking to an Access database
or Microsoft Office Excel workbook saved in a format that is a later
version than the current database format.

How can I tell Access to create the temp database in 2003 format?

I'm sure many of you are familar with Tony's module but I'll post here
I case you aren't.

Option Compare Database
Option Explicit

Sub TempTablesSample()

' 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

' While this code is copyright 2000 by Tony Toews it's all code strung
together from the online help so do
' whatever you like with this. At your own risk.

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

' 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 = "temp Import Materials"

'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("Invoice", dbLong)
.Fields.Append .CreateField("InvoiceItemNumber", dbInteger)
.Fields.Append .CreateField("InvoiceMaterialCode", dbText)
.Fields.Append .CreateField("Line2", dbText)
.Fields.Append .CreateField("Line3", dbText)
.Fields.Append .CreateField("LengthOrQuantity", dbDouble)
dbsTemp.TableDefs.Append tdfNew
End With


dbsTemp.TableDefs.Refresh

Dim tdfLinked As TableDef

' Link to the Import tables in the temp MDB
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)


' Do your logic to the temp tables here.


RS.Close
dbsTemp.Close

CurrentDb.TableDefs.Refresh

Set RS = Nothing
Set dbsTemp = Nothing

' Unlink the tables
CurrentDb.TableDefs.Delete strTableName

' Delete the temp mdb
strTempDatabase = Left$(CurrentDb.Name, Len(CurrentDb.Name) - 4) &
" temp.mdb"
Kill (strTempDatabase)



tagExit:

Exit Sub

tagError:
DoCmd.Hourglass False
If Err.Number = 70 Then
MsgBox "Unable to delete temporary database as it is locked."
& vbCrLf & vbCrLf & _
"Import cancelled."
Else
MsgBox Err.Description, vbCritical
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

.

Relevant Pages

  • Re: Temp database wouldnt open from jump drive
    ... > When my app starts up, it creates a temporary database. ... > is created from a 'model' database that is in the same folder as the ... > Then code opens the temp database and links to the tables. ...
    (comp.databases.ms-access)
  • Temp database wouldnt open from jump drive
    ... When my app starts up, it creates a temporary database. ... Then code opens the temp database and links to the tables. ... So I copied all the necessary files to a folder on a "jump" drive. ...
    (comp.databases.ms-access)
  • temp db
    ... anything in terms of creating the database. ... The purpose is to create a temp db for all the ... ' This subroutine illustrates how to use a temporary MDB in your app. ... MsgBox "creating new" ...
    (microsoft.public.access.formscoding)
  • Re: URGENT DB HELP
    ... The database is downloaded to the FrontPage temporary directory, ... the FP temp directory is located at ... Is there a default temp folder that it downloads to? ...
    (microsoft.public.frontpage.programming)
  • Re: temp db
    ... If the database is not in use, you can create a copy just by copying the MDB ... The purpose is to create a temp db for all the ... ' This subroutine illustrates how to use a temporary MDB in your app. ...
    (microsoft.public.access.formscoding)