vba 获取“[Microsoft][ODBC SQL Server 驱动程序][SQL Server]'Microsoft.' 附近的语法不正确。
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2521545/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting "[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Microsoft.'
提问by brohjoe
I'm getting an error, "[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Microsoft.'
我收到错误消息，“[Microsoft][ODBC SQL Server 驱动程序][SQL Server]'Microsoft.' 附近的语法不正确。
Here is the code:
这是代码：
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Public Sub loadData()
'This was set up using Microsoft ActiveX Data Components version 6.0.
'Create ADODB connection object, open connection and
' construct the connection string object which is the DSN name.
Set conn = New ADODB.Connection
conn.ConnectionString = "sql_server"
conn.Open
'conn.Execute (strSQL)
On Error GoTo ErrorHandler
'Open Excel and run query to export data to SQL Server.
strSQL = "SELECT * INTO SalesOrders " & _
"FROM OPENDATASOURCE(Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Workbook.xlsx;" & _
"Extended Properties=Excel 12.0; [Sales Orders])"
conn.Execute (strSQL)
'Error handling.
ErrorExit:
'Reclaim memory from the cntection objects
Set rst = Nothing
Set conn = Nothing
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbCritical
Resume ErrorExit
'clean up and reclaim memory resources.
conn.Close
If CBool(cnt.State And adStateOpen) Then
Set rst = Nothing
Set conn = Nothing
End If
End Sub
采纳答案by Alex K.
The provider argument passed to OPENDATASOURCE is a string so must be quoted. Your also addressing the sheet within the OPENDATASOURCE call, which is incorrect;
传递给 OPENDATASOURCE 的 provider 参数是一个字符串，因此必须用引号引起来。您还在 OPENDATASOURCE 调用中对工作表进行寻址，这是不正确的；
strSQL = "SELECT * INTO SalesOrders " & _
"FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0'," & _
"'Data Source=C:\Workbook.xlsx;" & _
"Extended Properties=Excel 12.0')...[Sales Orders]"
回答by Raj Kaimal
Try the following syntax
试试下面的语法
xls
xls
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[FilePath];Extended Properties=”Excel 8.0;HDR=YES;IMEX=1”
xlsx
xlsx
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[FilePath];Extended Properties=Excel 12.0 Xml;HDR=YES;IMEX=1
HDR=Yes specifies that the first row of the data contains column names and not data so set it accordingly (Yes, No)
HDR=Yes 指定数据的第一行包含列名而不是数据，因此相应地设置它（是，否）
IMEX=1 specifies that the driver should always read the “intermixed” data columns as text
IMEX=1 指定驱动程序应始终将“混合”数据列作为文本读取
参考：http: //weblogs.asp.net/rajbk/archive/2009/05/02/uploading-an-excel-file-to-sql-through-an-asp-net-webform.aspx
