Incorrect syntax near 'Microsoft'

Hi Experts,

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 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






brohjoeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
RTFM: http://msdn.microsoft.com/en-us/library/ms179856.aspx
strSQL = "SELECT * INTO SalesOrders FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0'," & _
    "'Data Source=C:\Workbook.xlsx;Extended Properties=Excel 12.0')...[Sales Orders]"

Open in new window

0
brohjoeAuthor Commented:
Ok, Thanks Angellll.  What I get from the MSDN website that I might not have done is this:

"OPENDATASOURCE can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. When these options are not set, the default behavior does not allow for ad hoc access."

How do I enable the DisallowAdhocAccess registry option and set it to zero?  Also, I didn't see 'Ad Hoc Distributed Queries' option in the Configuration Manager.   Where would I set this?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

brohjoeAuthor Commented:
The instructions are for SQL Server.  I'm using SQL Server Management Studio Express 2005.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see, actually, my first link was not correct:
http://msdn.microsoft.com/en-us/library/ms179856%28SQL.90%29.aspx

for sql 2005, the DisallowAdhocAccess does not apply, actually
0
brohjoeAuthor Commented:
I'm going to need a little hand holding here.  What do I do?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
did you try my code?
* yes => errors?
 + yes => which ones?
 + no => OK
* no => well, please try
0
brohjoeAuthor Commented:
I used your code:

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

Got the same error.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the same "incorrect syntax near Microsoft ... " ?
that's not possible.-..
0
brohjoeAuthor Commented:
This is what I'm trying to do.  I'm trying to export data from an Excel spreadsheet to SQL Server Management Studio 2005 on the same machine.  I have a DSN set up for the SQL Server instance.  Connectivity does not seem to be an issue.  The issue is this error.  You are welcome to test this.  I"m sure you have Excel and SQL Server on your machine.

Let me know.
0
brohjoeAuthor Commented:
You do realize that the only difference between your code and mine is the parentheses after 'Excel 12.0'.  I'm here to tell you it made no difference.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no, there are more differences, more single quotes

see, you had:
OPENDATASOURCE(Microsoft.AC ...

I have:
OPENDATASOURCE('Microsoft.AC ...

there are more quotes around ...
0
brohjoeAuthor Commented:
Ok, now I'm starting to see something different.   I put in you code exactly

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

I get a new error now:

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider "Microsoft.ACE.OLEDB.12.0 for linked server "(null): returned message "Unspecified error".
0
brohjoeAuthor Commented:
I think it may have something to do with the single quote mark.  When you put a  closing ' at the end of this string after [Sales Orders], you get an "Unclosed quotation mark after the character string."  And if you delete the single quote in from of "Data", you get

"Incorrect syntax near 'Data'.

I'm not quite understanding the rhyme and reason behind the single quotes.  Like, why is only a single quote is used in front of 'Data' , but no closing single quote on that line?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have this:

strSQL = "  SELECT  ....  "
the double quotes are to delimit the string, which holds the sql

if you remove that part, you end up with this:
SELECT * INTO SalesOrders FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=C:\Workbook.xlsx;Extended Properties=Excel 12.0')...[Sales Orders]

and there, you have inside the opendatasource 2 arguemtns, actually:
'Microsoft.ACE.OLEDB.12.0'
'Data Source=C:\Workbook.xlsx;Extended Properties=Excel 12.0'



0
brohjoeAuthor Commented:
Ok got it.   Any ideas about the "Unspecified error."?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, no. that's usually some of the not so helpful error messages from MS which is very difficult to handle :(
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brohjoeAuthor Commented:
Ok, well thanks anyway for your time.
0
brohjoeAuthor Commented:
Question not answered, but I got some understanding of some syntax issues.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.