Access VBA SELECT语句报错：保留字/拼写/标点问题排查求助

Hey there, let's work through that annoying SQL syntax error you're getting in your Access VBA code. The error message mentions reserved words or punctuation issues, and you're right to suspect punctuation—here's exactly what's going wrong and how to fix it:

核心问题：未闭合的字符串引号 #

Your problematic strMakePaTablesSQL line is missing a closing double quote at the end, which means the entire SQL string isn't properly terminated. Access can't parse an incomplete SQL statement, hence the error.

错误的代码片段： #

strMakePaTablesSQL = "SELECT [all vendor Rebates].[Key_Code_Name] as [Supplier Name], [all vendor Rebates].[Vendor_Name], " _ & "[all vendor Rebates].[Contract_ID], [all vendor Rebates].[EXP_DATE], [all vendor Rebates].[Contract_Status], [all vendor Rebates].[Price Book Priority] as [High Priority Customer], [all vendor Rebates].[GPO Or biosite] as [GPO Indicator], [all vendor Rebates].[LTM_Rebate_Dollars] as [LTM Rebate Dollars] into [" & strTableName & "] From [all vendor Rebates] Where [Vendor_Name] = '" & strSupplier & "'

Notice the end of the line only has a single quote (') wrapping the strSupplier value, but no closing double quote (") to finish the VBA string literal. We'll fix that, plus add a semicolon to make the SQL syntax more standard.

修正后的代码： #

strMakePaTablesSQL = "SELECT [all vendor Rebates].[Key_Code_Name] as [Supplier Name], [all vendor Rebates].[Vendor_Name], " _ 
& "[all vendor Rebates].[Contract_ID], [all vendor Rebates].[EXP_DATE], [all vendor Rebates].[Contract_Status], [all vendor Rebates].[Price Book Priority] as [High Priority Customer], [all vendor Rebates].[GPO Or biosite] as [GPO Indicator], [all vendor Rebates].[LTM_Rebate_Dollars] as [LTM Rebate Dollars] into [" & strTableName & "] From [all vendor Rebates] Where [Vendor_Name] = '" & strSupplier & "';"

I adjusted the line break formatting slightly for readability, but the critical fix is adding "; at the end to close both the SQL statement and the VBA string.

额外调试与优化建议 #

• Debug with Debug.Print: Add Debug.Print strMakePaTablesSQL right before executing the statement. This will print the full generated SQL to the VBA Immediate Window—you can copy that SQL directly into an Access query to test it, which makes syntax issues way easier to spot.
• Your special character handling: You're already doing a great job cleaning up strSupplier by replacing special characters like ', /, and *—this reduces the chance of SQL injection or syntax breaks from unexpected values. Keep that up!

完整修正后的函数片段 #

Here's the fixed section of your MakeTableVN function:

'... (rest of your existing code remains the same)
strMakePaTablesSQL = "SELECT [all vendor Rebates].[Key_Code_Name] as [Supplier Name], [all vendor Rebates].[Vendor_Name], " _ 
& "[all vendor Rebates].[Contract_ID], [all vendor Rebates].[EXP_DATE], [all vendor Rebates].[Contract_Status], [all vendor Rebates].[Price Book Priority] as [High Priority Customer], [all vendor Rebates].[GPO Or biosite] as [GPO Indicator], [all vendor Rebates].[LTM_Rebate_Dollars] as [LTM Rebate Dollars] into [" & strTableName & "] From [all vendor Rebates] Where [Vendor_Name] = '" & strSupplier & "';"
dbPa.Execute strMakePaTablesSQL
Debug.Print strMakePaTablesSQL ' Add this to debug generated SQL
Debug.Print "C:\Testing\Expiring rebates\Vendor_Files\" & strSupplier & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strTableName, "C:\Testing\Expiring rebates\Vendor_Files\" & strSupplier & ".xls", True
DoCmd.DeleteObject acTable, strTableName
'... (rest of your existing code remains the same)

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