Import Data into Excel from Access Table, Syntax error in FROM Clause

Question

I am running this code to import data from Access to Excel and getting a run-time error:

 "syntax error in FROM clause."

The table in Access has four columns: Date, Time, Tank, Comments, and I want to import Time and Tank, based on a date in the spreadsheet. I want to order these columns in the order Tank, Time.

The error is in the line:

.Open "Select [Time], [Tank]  FROM [UnitOneRouting] WHERE [Date] = " & RpDate & " ORDER BY Tank, Time", cn, adOpenStatic, adLockOptimistic, adCmdTable

Code Snippet:

Sub ADOImportFromAccessTable()
    Dim DBFullName As String
    Dim TableName As String
    Dim TargetRange As Range
    Dim RpDate As Range

    DBFullName = "U:\Night Sup\Production Report 2003 New Ver 5-28-10_KA.mdb"
    TableName = "UnitOneRouting"
    Worksheets("TankHours").Activate
    Set TargetRange = Range("C5")
    Set RpDate = Range("B2").Cells


    Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
        Set TargetRange = TargetRange.Cells(1, 1)
        ' open the database
        Set cn = New ADODB.Connection
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
            "U:\Night Sup\Production Report 2003 New Ver 5-28-10_KA.mdb" & ";"
        Set rs = New ADODB.Recordset
           With rs
        ' open the recordset
        ' filter rows based on date
            .Open "Select [Time], [Tank]  FROM [UnitOneRouting] WHERE [Date] = " & RpDate & " ORDER BY Tank, Time", cn, adOpenStatic, adLockOptimistic, adCmdTable
         rs.Open , TargetRange
         TargetRange.CopyFromRecordset rs

        End With
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing
    End Sub

Put a breakpoint on the .Open "Select [Time] ... line and run your code. When the code breaks on that line, copy the entire SQL string including the first and last quotation marks. In the immediate window, type "?" and then paste the SQL string and hit enter. What is the resulting String? This is exactly what gets sent to Access as the request, so the problem should be visible. Add it to your question down at the bottom and that will help us help you :D — Blackhawk
– Blackhawk, Commented Sep 26, 2014 at 13:34
First things first, using "Date" as a field name should be avoided as it is reserved. — pegicity
– pegicity, Commented Sep 26, 2014 at 14:02
Also you are using excel spreadsheet functions on an access DB, there is no "Range" for an access table (AFAIK). I am a little confused about what you are trying to do here, if you are trying to select values from a table in access as your coding seems to indicate then the first thing is you need to use access functions. — pegicity
– pegicity, Commented Sep 26, 2014 at 14:14
This is the request that VBA/SQL sends to ACCESS Select [Time], [Tank] FROM [UnitOneRouting] WHERE [Date] = 9/15/2014 ORDER BY Tank, Time — Kish
– Kish, Commented Sep 26, 2014 at 14:27

HansUp · Accepted Answer · 2014-09-26 15:55:17Z

1

Start with a SELECT statement which Access will accept. Use a string variable to hold the statement. Then you can Debug.Print the variable and inspect the statement text in the Immediate window. For troubleshooting, you can also copy the statement text from there and paste it into SQL View of a new Access query.

Here is a code example, where I hard-coded the value for RpDate ... just to keep it simple.

Dim RpDate
Dim strSelect As String
RpDate = #9/26/2014#
strSelect = "SELECT u.Time, u.Tank" & vbCrLf & _
    "FROM UnitOneRouting AS u" & vbCrLf & _
    "WHERE u.Date = " & Format(RpDate, "\#yyyy-m-d\#") & vbCrLf & _
    "ORDER BY u.Tank, u.Time;"
Debug.Print strSelect

This is the SELECT statement produced by that code ...

SELECT u.Time, u.Tank
FROM UnitOneRouting AS u
WHERE u.Date = #2014-9-26#
ORDER BY u.Tank, u.Time;

Once you have a valid Access SQL SELECT statement, you will need to fix the recordset .Open call to give it acceptable option values. adCmdTable causes an error because your recordset's data source is a SELECT statement, not a table.

' next line throws error -2147217900, "Syntax error in FROM clause."
.Open strSelect, cn, adOpenStatic, adLockOptimistic, adCmdTable

'either of the next 2 lines works ...
'.Open strSelect, cn, adOpenStatic, adLockOptimistic
.Open strSelect, cn, adOpenStatic, adLockOptimistic, adCmdText

So I think you're dealing with a situation where the error message is misleading. "Syntax error in FROM clause" suggests the problem is in the SELECT statement. However, once you do have a valid SELECT, you will still get that same error text due to adCmdTable. Do not use adCmdTable for a SELECT.

edited Sep 26, 2014 at 15:55

answered Sep 26, 2014 at 15:50

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Kish Over a year ago

This helped a lot. The code ran through to the end and imported the data from the access table. I have two new problems now: The data is still in the order Time, Tank instead of Tank, Time. And it is being sorted (by row) alphabetically based on Tank, but I want it sorted based on time. Should I ask this in a different question?

HansUp Over a year ago

ORDER BY u.Tank, u.Time tells Access to sort first by Tank. Among rows with the same Tank values, they will then be sorted by Time. If that's not the sort order you want, paste the SELECT statement text into SQL View of a new Access query. Then switch to Design View and adjust the ordering to display what you want when you switch to Datasheet View.

Kish Over a year ago

I am doing this programming in VBA-Excel, and to be honest it is the first program I am really doing on my own that is remotely SQL-related. I do not have much idea of what you just mentioned. I just switched the program to have the rows sorted on based on time. What I really want though is to have the columns swapped in Excel. In the Access table, Time is column 2 and Tank is column 3. When I import into Excel, I want Tank to be the first column and Time to be the second. So far it is being imported with Time being before Tank. Can I program that in VBA?

HansUp Over a year ago

Do you have MS Access in your MS Office installation?

Kish Over a year ago

Yes I do. I can go ahead and switch that. I just understood what you mentioned. But that's how I want it in the database. It is easier to read that way in Access. But it is easier to read in Excel when it is reversed. Based on how things are set up. To change the set-up will involve a lot more reprogramming than finding a program fix when importing. What I can do though, is to select and swap these columns after they are imported, in the same macro.

|

Jagger · Accepted Answer · 2014-09-26 13:17:56Z

0

Do you have an example of your SQL request?

I think there's a problem in the date's format... you should try to wrap your date (RpDate) whith this char #, like this :

.Open "Select [Time], [Tank]  FROM [UnitOneRouting] WHERE [Date] = #" & RpDate & "# ORDER BY Tank, Time", cn, adOpenStatic, adLockOptimistic, adCmdTable

edited Sep 26, 2014 at 13:17

Jagger

10.6k11 gold badges60 silver badges100 bronze badges

answered Sep 26, 2014 at 13:13

Derf

1

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

0

As my comment mentions I am a little confused by your code here. You are trying to select records based on their date, but when you dim your RbDate variable you are setting it to a range? This is not only not a valid property of an Access table, even if it were you cannot compare a date to a range. If you were in Excel you could

Dim RbDate as Date

RbDate = ActiveWorksheet.Range("B2").Value

But as you are not in excel this does not apply. I would suggest using a parameter for date if it will constantly change, otherwise just hardcode the date right into your code in the following way

Dim RbDate as Date

RbDate = #2017/11/23#

You can then use this as a value in your SQL query string, as currently RbDate is probably empty or simply not comparable to [Date]. You can try this to be sure, run your code by "Stepping", press F8 and each time you do it will execute one line of code, after you get past (at least one line of code after)

Set RpDate = Range("B2").Cells

hover your curser over "RpDate" and it will tell you what is stored in the variable

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 26, 2014 at 14:26

pegicity

3691 gold badge3 silver badges12 bronze badges

3 Comments

Kish Over a year ago

I run the code step by step and get passed that part. The RpDate is a cell in Excel that I cannot afford to hard code. I want to use that date to compare with the dates in the Access table, and select data that matches date. I am doing this programming in VBA-Excel.

pegicity Over a year ago

Ah that would be why I was confused, so do what I said with Diming RbDate as a Date and assign it to Activeworksheet.Range("B2").value and see if that helps

Kish Over a year ago

this got me into compile problems.

Collectives™ on Stack Overflow

Import Data into Excel from Access Table, Syntax error in FROM Clause

3 Answers 3

6 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related