Access Temporary Tables 1

[vjzone]

I've read through some of the posts here and elsewhere and can't seem to find the exact syntax for creating a temporary table within Access. I want to use the SQL design view (under Query objects) within Access to type in the command and then execute the command, see the table, exit Access, launch Access and no longer see the table-a true temp table. Can someone show me the exact syntax for a table named "TestData" for instance? Thanks a bunch...

 

[dhookom]

This is directly from Access Help examples:

CREATE TABLE Friends
([FriendID] integer,
[LastName] text,
[FirstName] text,
[Birthdate] date,
[Phone] text,
[Notes] memo,
CONSTRAINT [Index1] PRIMARY KEY ([FriendID]));

There are no "temporary" tables in Access. You could open a hidden form when your application opens. Set the On Close event of the form to delete the table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lameid]

dhookom is right in that you can't create temp tables. However deleting and making tables repeatedly will lead to database bloat. Assuming the format of your temp tables are fixed, you can put them in a separate file. Then you can copy the file to a specific location on your hard drive and link to those tables. As long as you don't access the tables when the file doens't exist, you are golden. Then insead of making a temp table, just append to it.

Statements you might find usefull...

FileCopy source, destination
Kill pathname

To move a file use the Name statement.

 

[dhookom]

I have a data definition table in one of my applications that creates a separate back-end database file and then creates all the tables and fields (with indexes) and links them to my front-end.

The dd table is in my primary front-end so I can redefine the "temporary" tables at any time. My table structure is:
[tt][blue]
ztblTempStructure
========================
TableName text
FieldName Text
FieldType Number (integer)
FieldSize Number (integer)
Indexed Yes/No
PrimaryKey Yes/No
[/blue][/tt]
The code requires a reference to the MS DAO object library:

Function BldTempTables() As Boolean
  '============================================================
  '  Programmer: DHookom
  '  Revision #:
  ' Called From:
  '        Date: 7/5/01
  '  Parameters:
  '============================================================
    On Error GoTo BldTempTables_Err
    Dim strErrMsg As String 'For Error Handling

    'Dim the objects
    Dim dbThis As DAO.Database
    Dim dbTemp As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim ndx As DAO.Index
    Dim rsStruct As DAO.Recordset   'the struct table
    
    'Dim the variables
    Dim strFolder As String         'the folder this application is located in
    Dim strThisDBName As String     'the name of this MDB
    Dim strTempDBName As String     'The name of the temp mdb
    Dim strTableName As String      'the table name
    
    Set dbThis = CurrentDb
    strThisDBName = dbThis.Name
    strFolder = Left(strThisDBName, Len(strThisDBName) - Len(Dir(strThisDBName)))
    strTempDBName = strFolder & "PrdRptTemp.MDB"
    On Error Resume Next
    Kill strTempDBName 'if the old one exists, delete it
    On Error GoTo BldTempTables_Err
    'Create the new empty database
    Set dbTemp = CreateDatabase(strTempDBName, dbLangGeneral)
    Set rsStruct = dbThis.OpenRecordset("Select TableName, FieldName, FieldType, FieldSize, Indexed " & _
            "FROM ztblTempStructure ORDER BY TableName")
    With rsStruct
        If Not .EOF Then
            .MoveFirst
            Do Until .EOF
                strTableName = !TableName
                Set tdf = dbTemp.CreateTableDef(strTableName)
                Do Until !TableName <> strTableName
                    Select Case !FieldType
                        Case dbText
                            Set fld = tdf.CreateField(!FieldName, !FieldType, !FieldSize)
                            fld.AllowZeroLength = True
                        Case Else
                            Set fld = tdf.CreateField(!FieldName, !FieldType)
                    End Select
                    
                    tdf.Fields.Append fld
                    tdf.Fields.Refresh
                    .MoveNext
                    If .EOF Then
                        Exit Do
                    End If
                Loop
                dbTemp.TableDefs.Append tdf
                dbTemp.TableDefs.Refresh
    
            Loop
        End If
        .Close
    End With
    
    'Create the indexes
    Set rsStruct = dbThis.OpenRecordset("Select TableName, FieldName, FieldType, Indexed, PrimaryKey " & _
            "FROM ztblTempStructure WHERE Indexed = -1 OR PrimaryKey = -1 ORDER BY TableName")
    With rsStruct
        .MoveFirst
        If Not .EOF Then
            .MoveFirst
            Do Until .EOF
                Set tdf = dbTemp.TableDefs(!TableName)
                'Debug.Print tdf.Name
                strTableName = !TableName
                Do Until !TableName <> strTableName
                    'Debug.Print "-" & !FieldName
                    Set ndx = tdf.CreateIndex(!FieldName)
                    Set fld = ndx.CreateField(!FieldName, !FieldType)
                    ndx.Fields.Append fld
                    'set up the primary key Field.
                    If !PrimaryKey = True Then
                        ndx.Primary = True
                    End If
                    tdf.Indexes.Append ndx
                    tdf.Indexes.Refresh
                    .MoveNext
                    If .EOF Then
                        Exit Do
                    End If
                Loop
            Loop
        End If
        .Close
    End With
    Set rsStruct = dbThis.OpenRecordset("Select Distinct TableName From ztblTempStructure")
    'relink the tables
    With rsStruct
        .MoveFirst
        Do Until .EOF
            DoCmd.DeleteObject acTable, !TableName
            DoCmd.TransferDatabase acLink, "Microsoft Access", strTempDBName, acTable, !TableName, !TableName
            dbThis.TableDefs.Refresh
            .MoveNext
        Loop
        .Close
    End With
    Set rsStruct = Nothing
    Set dbThis = Nothing
    Set dbTemp = Nothing
    BldTempTables = True

BldTempTables_Exit:
    Exit Function

BldTempTables_Err:
    Select Case Err
        Case Else
            strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) & vbCrLf
            strErrMsg = strErrMsg & "Error Description: " & Err.Description
            MsgBox strErrMsg, vbInformation, "BldTempTables"
            BldTempTables = False
            Resume BldTempTables_Exit
    End Select
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

Duane, you rock!

les

 

[dhookom]

Les,
Thanks for the compliment. Since you like it so much, I have submitted the code as a FAQ in the forum.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

I've always thought that the inability of Access to create and use temp tables was one of it's biggest drawbacks. I'm sure that many people will find this extremely helpful!

les

have a great weekend! I'll be spending mine at my 20 year HS reunion!!