18 thoughts on “Syntax Error in FROM Clause”

1. MySQL would be an interesting touch to DDE… :)

2. 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!

3. doco: You can use

   Format(dateFromWrkSht, “yyyy-mm-dd”)

   to get it in the right format.

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

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

6. Dennis: Thanks for the list.

   I don’t know how to find what version of MDAC I’m using.

   SQLServer Express is a fine option. How long will that be free?

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

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

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

10. I’ve had this problem before when there is a field name by the same name as the table name or possibly a user-defined function with the same name as the table.

    This is not the case in your example, but you also should be aware of reserved words:
    http://support.microsoft.com/kb/q209187/

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

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

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

14. Hi,

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

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

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

16. in the where clause there is a less than greater than sign (not equal to)!!!!!

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

18. I fixed this problem by taking spaces out of table names. Of course, after trying a dozen more complex solutions.

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.