Getting "[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Microsoft.'

I'm getting an error, "[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '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 D开发者_JS百科ata 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

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;

     strSQL = "SELECT * INTO SalesOrders " & _
       "FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0'," & _
       "'Data Source=C:\Workbook.xlsx;" & _
       "Extended Properties=Excel 12.0')...[Sales Orders]"

Try the following syntax

xls

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[FilePath];Extended Properties=”Excel 8.0;HDR=YES;IMEX=1”

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)

IMEX=1 specifies that the driver should always read the “intermixed” data columns as text

ref: http://weblogs.asp.net/rajbk/archive/2009/05/02/uploading-an-excel-file-to-sql-through-an-asp-net-webform.aspx