我正在运行以下代码,以从Access到Excel导入数据并遇到运行时错误:
"syntax error in FROM clause."
在Access的表有四列:Date
,Time
,Tank
,Comments
,和我想导入Time
和Tank
基于电子表格的日期。我想订购的顺序这些列Tank
,Time
。
错误在该行中:
.Open "Select [Time], [Tank] FROM [UnitOneRouting] WHERE [Date] = " & RpDate & " ORDER BY Tank, Time", cn, adOpenStatic, adLockOptimistic, adCmdTable
代码段:
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
从SELECT
Access将接受的语句开始。使用字符串变量来保存该语句。然后,您可以Debug.Print
使用变量并在立即窗口中检查语句文本。为了进行故障排除,您还可以从此处复制语句文本,然后将其粘贴到新的Access查询的SQL视图中。
这是一个代码示例,在这里我为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
这是该SELECT
代码产生的语句...
SELECT u.Time, u.Tank
FROM UnitOneRouting AS u
WHERE u.Date = #2014-9-26#
ORDER BY u.Tank, u.Time;
拥有有效的Access SQLSELECT
语句后,您将需要修复记录集.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
因此,我认为您正在处理错误消息令人误解的情况。“ FROM子句中的语法错误”表明问题出在SELECT
语句中。但是,一旦您拥有有效的密码SELECT
,由于,您仍然会得到相同的错误文本adCmdTable
。请勿adCmdTable
用于SELECT
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句