?
Solved

Incorrect syntax near 'Microsoft'

Posted on 2010-03-25
19
Medium Priority
?
1,404 Views
Last Modified: 2012-06-27
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






0
Comment
Question by:brohjoe
  • 11
  • 8
19 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 28650548
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
 

Author Comment

by:brohjoe
ID: 28653958
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 28655578
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 

Author Comment

by:brohjoe
ID: 28656460
The instructions are for SQL Server.  I'm using SQL Server Management Studio Express 2005.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 28656983
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
 

Author Comment

by:brohjoe
ID: 28657204
I'm going to need a little hand holding here.  What do I do?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 28658157
did you try my code?
* yes => errors?
 + yes => which ones?
 + no => OK
* no => well, please try
0
 

Author Comment

by:brohjoe
ID: 28659137
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 28661601
the same "incorrect syntax near Microsoft ... " ?
that's not possible.-..
0
 

Author Comment

by:brohjoe
ID: 28664781
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
 

Author Comment

by:brohjoe
ID: 28666178
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 28666296
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
 

Author Comment

by:brohjoe
ID: 28669951
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
 

Author Comment

by:brohjoe
ID: 28670428
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 28671862
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
 

Author Comment

by:brohjoe
ID: 28672057
Ok got it.   Any ideas about the "Unspecified error."?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 28673533
sorry, no. that's usually some of the not so helpful error messages from MS which is very difficult to handle :(
0
 

Author Comment

by:brohjoe
ID: 28673682
Ok, well thanks anyway for your time.
0
 

Author Closing Comment

by:brohjoe
ID: 31707418
Question not answered, but I got some understanding of some syntax issues.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question