从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进行选择。

3 个解决方案

#1

更多相关文章

随机推荐