Question

提示 INSERT INTO 语句的语法错误，请大家帮忙解决，谢谢！

Private Sub Command3_Click()
Dim strSQL As String
Dim strconn As String ' 定义 Excel 连接字符串
Dim cn As ADODB.Connection ' 定义 Excel 连接
Set cn = New ADODB.Connection
Dim rstschema As ADODB.Recordset
' 初始化 commandialog1 的属性，选取 Excel 文件，文
' 件名保存在 CommanDialog1.filename 中备用
CommonDialog1.Filter = " 电子表格文件 (.xls) |*.xls"
CommonDialog1.DialogTitle = " 请选择要导入的文件"
CommonDialog1.ShowOpen
' 设置连接 SQL 数据库的连接字符串
'strtemp = "[odbc;Driver= {SQL Server} ;Server=172.16.183.144;Database=山东电费台帐;UID=123;PWD=123] "
' 设置 Excel 数据连接
strconn = "Provider =Microsoft.Jet.OLEDB.4.0;Data Source = " & CommonDialog1.FileName & ";Extended Properties=Excel 8.0 "
cn.Open strconn
Set rstschema = cn.OpenSchema(adSchemaTables)
' 设置 Excel 记录集
' 循环将 Excel 数据文件中的各个数据表写入数据库
Do Until rstschema.EOF
strSQL = " insert into [odbc:Driver= {SQL Server} ;Server=172.16.183.144;Database=山东电费台帐;UID=wangyu;PWD=wangyu].[山东电费台帐].[dbo].[Sheet1$] select * from [" & rstschema!TABLE_NAME & "] "
cn.Execute strSQL, , adExecuteNoRecords
'MsgBox " 成功导入--"& CommonDialog1.File－Name & " --文件中--"& rstschema! TABLE_NAME & "---的数据到 SQL 数据库中！" , vbExclamation + vbOKOnly
rstschema.MoveNext
Loop
' 释放资源
rstschema.Close
cn.Close
Set cn = Nothing
Set rstschema = Nothing
End Sub

Answer 1

strSQL = " insert into [dbo].[Sheet1$] select * from [" & rstschema!TABLE_NAME & "] "