Syntax Error in FROM Clause

The company for whom I now work buys some version of Microsoft Office that includes Powerpoint and Publisher, but not Access. I’d be more specific, but I don’t really know what the versions of Office are. But no Access? I need to figure out a way to fix that!

In the mean time, I’ve downloaded Nwind.mdb from Office Online, which I assume is the same as the Northwind.mdb sample database that ships with Access. When I attempt to create an external data query in Excel 2003, I get this message at the end of the query wizard:

error dialog

I don’t typically use the query wizard, but I got the same error message in the MS Query interface when I tried to add a field by dragging it from the tables area to the query area. Furthermore, I got the same error when I tried to manually enter this SQL statement:

SELECT * FROM Categories

I’m not quite sure how I figured this next one out, but the problem was fixed by manually entering (manually means in the SQL dialog box from within MS Query):

SELECT * FROM [Categories]

I wasn’t able to avoid the error using any kind GUI – it had to be manually typed.

When I use ADO via VBA, I’m not required to enclose the table name in brackets in any SQL statements I write. The connection statement I use to connect via ADO is exactly the same as the Connection property of the QueryTable object except that the ODBC is removed from the front of it. Does that mean the ODBC driver is the problem? Why do I have an ODBC driver for Access if I don’t have Access installed? Is it time to learn MySQL so I don’t have to pay for Access?

Posted in Uncategorized

18 thoughts on “Syntax Error in FROM Clause

  1. Seriously, I have had other problems with MSQ. I just finished a project which had need for a SELECT * FROM table WHERE(((date)=#” & dateFromWrkSht & “#));”

    The date field in the Access table was formated “Short Date”. The date from the worksheet was formatted “Short Date”; still the two were not compatable and would not recognize each other. I believe because Time is appended to the date in the worksheet. My workaround was to place this =DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())) formula in the cell for date and formatted “Short Date” and now works marvelously!

  2. Dick,

    # There exist several free dbs if You follow the following URL:
    http://www.excelkb.com/instantforum41/Topic2-17-1.aspx

    # In general we don’t need MS Access in order to create MDBs as the Microsoft Jet Database Engine is bundled with several MSFT-products. Classic VB offer a good alternative ;)

    # Which version of MDAC do You use? The ODBC Driver is shipped with the version 2.5 and earlier.

    # As for the issue You got I’m not sure what may cause it but I would also suspect the ODBC Driver.

    I downloaded & installed the db and run the same query as You did without any errors…

    # It’s rather common that corporates use a version of MS Office excluding MS Access. At least in my part of the world.

    Kind regards,
    Dennis

  3. MySQL would be a nice alternative. Or any other free database for that matter, just like Dennis points out.

    If you must use mdb files I would personally use DAO rather then ADO but that is perhaps another discussion.

    Quit possibly the ODBC driver is causing problems. But why bother with ODBC anyway Dick? OLEDB is a better (improved) successor of ODBC (although the inners work differently) and I never had any strange problems using that.

    Rembo

  4. Rembo: No, let’s have that discussion now. :) I always use ADO, but only because it’s the newer technology. I’m interested to know why you would choose DAO.

    How do I use OLEDB with MSQuery? Do I specifiy it in the DSN or is that DSN-less. If so, I’m at a loss how to use it.

  5. Dick,

    How to check for MDAC version
    http://support.microsoft.com/default.aspx?kbid=301202

    Startpage for MS Data
    http://msdn.microsoft.com/data/Default.aspx

    Since Oracle and MySQL et al offer free versions of their RDBMS I assume that MSFT will keep the Express edition for free as well.

    It seems that it also replace the previous free version of SQL Server, the SQL Server 2000 MDE.

    I recently started to explore SQL Server 2005 (developer version) and it differ a lot compared with 2000. Actually, I’m rather confused at present ;-)

    DAO is optimized to be used with Microsoft Jet Engine so Remco have a point with it.

    Kind regards,
    Dennis

  6. Dick,

    Alright, let’s have the discussion now :-)
    I prefer using DAO for Access databases for the simple reason that it outperforms the more generic ADO method when accessing a mdb file. I wrote a post about ADO vs DAO that you can find here:

    http://www.excelkb.com/instantforum41/Topic74-14-1.aspx#bm79

    Not to advertise but we had a related discussion on Colo’s forum about ADO/DAO as well that you can find here:

    http://www.puremis.net/excel/cgi-bin/yabb/YaBB.pl?num=1132620479/19#19

    You don’t use OLE DB with MSQuery, it’s done from VBA. For a connection string see:

    http://www.codeproject.com/database/connectionstrings.asp#OLE%20DB%20Jet

    General issue is that I wouldn’t use ODBC if I don’t have to simply because there are more efficient ways to retrieve data from an Access database (read: mdb file).

    When using OLEDB the Data Source Name is not relevant. DSN or DSN-less are terms that come along with ODBC.

    Remco

  7. In defence of poor “Old” DAO. It is also shorter and much cleaner to write than RDO or ADO. The object structure is easy and if you know you only need to link to Access the benefits can be great.

    The speed difference is negligable at best and I am quite happy to accept that ADO could out run DAO in the 100m Sprint, but the cost is unreadable code for the poor untrained sap who has to maintain the code you wrote. Performance is rarely a significant issue when compared with ease of decoding and no one ever takes Access seriously enough to give it the resources and documentation of a real Big System. Lets face it you didn’t choose Access because of it’s performance so why start to worry now.

  8. I ran into this problem while bulding a query in Excel from an Access file. I moved the Access file to another directory on my machine, and it fixed the problem. I am thinking there was an issue in the path to the file name that was conflicting with the SQL. Perhaps a period in one of the directory names or something.

  9. I had exactly the same problem when building a query in Excel from an Access file. The problem was a “.” in one of the foldernames. Moved the file, and everything work again.

  10. Hi,

    How to use where clause in sql in excel programming to retrive data

    like “select* frim Sheet1 where column = “alam”

  11. Hope this will help:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection

    cn.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _
    “Data Source=” & “C:Documents and SettingsAll UsersDesktopFILENAME.XLS” & “;” & _
    “Extended Properties=Excel 8.0;”

    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient

    rs.Open “SELECT * FROM [DATA$] Where xCURRENT xNEW”, cn

    Debug.Print rs.RecordCount

    Do Until rs.EOF
    Debug.Print rs(“ItemCode”).Value + ” : ” & CStr(rs(“xNew”).Value)
    rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing

    cn.Close
    Set cn = Nothing

  12. Welcome to the world of SQL and data access across heterogeneous databases and technologies. When you have multiple database engines and different connection mechanisms, you have this diversity that is outside of Excel – which is monolithic.
    DAO is the most intimate object model to Jet. And it has come back to be the recommended MS object model for Jet in Office 2007. ADO is better for heterogeneous work in say .asp web pages where you want one object model to connect to Access, SQL Server and Oracle without thinking twice. However. ADO is already old because ADO.NET is the object model for current .NET technology.
    There is a difference between raw data store and data format. This is more apparent in a database than a spreadsheet. Access stores a datetime field. There is no date only field like sql server. So regardless of what the format is, long date, short date, British style date or American style date, the data is stored as a number – the whole number is the days, the fraction is the time – this is in Excel and in Access. To have a pure date, the time portion is zero. SQL dates are in American style.
    Jet SQL deviates from ANSI SQL by incorporating VBA Functions – so you can carry out VBA function calculations within Jet SQL. Jet/VBA has a NOW() and a DATE() – NOW has a time component, DATE does not.
    ODBC and OLEDB are intermediate layers gluing the JET (now called ACE) engine to VBA. OLEDB is more efficient and more intimate. ODBC is more abstracted and Jet/ODBC is only a single tier driver unlike MySQL, Oracle, SQL server. Use OLEDB if you can.

Leave a Reply

Your email address will not be published. Required fields are marked *