从Access表将数据导入Excel，从子句中出现语法错误

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

我正在运行这段代码，以便从对Excel的访问中导入数据，并获得运行时错误:

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

Access中的表有4列:Date、Time、Tank、Comments，我想根据电子表格中的日期导入Time和Tank。我想在订购的坦克，时间里订购这些列。

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

3 个解决方案

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

从一个SELECT语句开始，该语句将接受访问。使用字符串变量来保存语句。然后你可以调试。打印变量并检查当前窗口中的语句文本。对于故障排除，您还可以从那里复制语句文本并将其粘贴到新的访问查询的SQL视图中。

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

下面是一个代码示例，其中我硬编码了RpDate的值……只是为了保持简单。

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语句……

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.

一旦您有了一个有效的Access SQL SELECT语句，您将需要修复记录集. open调用，为它提供可接受的选项值。adCmdTable会导致错误，因为记录集的数据源是SELECT语句，而不是表。

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

我认为你所面对的是错误信息具有误导性的情况。“FROM子句中的语法错误”表明问题在SELECT语句中。但是，一旦您有了一个有效的选择，由于adCmdTable，您仍然会得到相同的错误文本。不要使用adCmdTable进行选择。

#2

Do you have an example of your SQL request?

您有SQL请求的示例吗?

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

我想日期的格式有问题……你应该试着用这个char #来包装你的约会(RpDate)，就像这样:

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

#3

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

正如我的评论提到的，我对您这里的代码有点困惑。您正在尝试根据记录的日期来选择记录，但是当您将RbDate变量调暗时，您正在将其设置为一个范围?这不仅不是访问表的有效属性，即使不能将日期与范围进行比较。如果你在Excel中，你可以

Dim RbDate as Date

昏暗的RbDate日期

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

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

但由于您不在excel中，所以这并不适用。我建议使用一个参数，以确定它是否会经常更改，否则只需要按照下面的方式硬编码到代码中。

Dim RbDate as Date

昏暗的RbDate日期

RbDate = #2017/11/23#

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)

然后，您可以在SQL查询字符串中使用这个值，因为当前RbDate可能是空的，或者只是不能与[Date]相比。您可以尝试这一点，通过“步进”来运行您的代码，按F8，并且每次执行时，它将执行一行代码，在您经过(至少一行代码之后)

Set RpDate = Range("B2").Cells

设置RpDate =范围(B2).Cells

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

将光标悬停在“RpDate”上，它会告诉您变量中存储了什么

#1