Access中Select Into创建临时表出现语法错误求助

Let's break down what's causing the syntax error in your code and fix it step by step. First, here's the original code you shared:

Function CreateTempTable()
Dim db As Database
Set db = CurrentDb()
db.Execute "Select [Concept Name],[Store Number ID] into" & TempTable & _
"from TblLodgingReport"
MsgBox "table is creat..."
End Function

Key Issues Causing the Syntax Error #

• Missing spaces in SQL string concatenation: When you glue the strings together, there are no spaces between into and your temp table name, or between the table name and from. This results in a malformed SQL statement like ...intoTempTablefrom... which the database can't parse.
• Undefined/Unquoted Temp Table Name: The TempTable variable isn't declared anywhere, and even if it was, you need to handle the table name properly so SQL recognizes it as a table identifier, not part of the keyword.
• Minor typo: creat in your MsgBox should be created (just a small tweak for clarity).

Corrected Code #

Add Option Explicit at the top of your module (this forces variable declaration and prevents silent typos) and fix the SQL syntax:

Option Explicit ' Mandatory at module top to catch undeclared variables

Function CreateTempTable()
    Dim db As Database
    Dim TempTable As String ' Declare your temp table name variable
    
    ' Define your temp table - use # for Access local temp tables (auto-deleted on close)
    TempTable = "#Temp_LodgingData"
    ' Use ## for global temp tables (visible to all connections) if needed
    
    Set db = CurrentDb()
    
    ' Fixed SQL with proper spacing and valid table name formatting
    db.Execute "SELECT [Concept Name], [Store Number ID] INTO " & TempTable & _
               " FROM TblLodgingReport"
    
    MsgBox "Table created successfully!"
End Function

What Each Fix Does #

1. Option Explicit: Catches mistakes like misspelling TempTable (which would otherwise create an empty variable and break your SQL silently).
2. Declared TempTable: Explicitly defines the name of your temp table. Using # at the start makes it an Access local temp table, which gets automatically deleted when you close the database—perfect for temporary data storage.
3. Proper SQL Spacing: Added spaces after INTO and before FROM so the database can correctly parse the structure of your SELECT INTO statement.
4. Clearer MsgBox: Fixed the typo and made the success message more descriptive.

内容的提问来源于stack exchange，提问作者user8603758