问题描述
我正在尝试从Access导入数据到Excel。访问表中有四列:日期,时间,储罐,注释。在导入时间和储罐列时,我会根据日期对它们进行排序。另外,我分别导入它们,因此可以交换时间,储罐到储罐,时间的列顺序。在编程中,我必须为此关闭并打开ADO连接。我想通过避免关闭连接而不必再次打开它来提高程序的效率。有什么建议/解决方案吗?谢谢。
I am trying to import data from Access to Excel. There are four columns in the Access table: Date, Time, Tank, Comments. On importing the Time and Tank columns, I sort them based on date. Additionally, I import them separately so I can swap the column order form Time, Tank to Tank, Time. In the programming I have to close and open the ADO connection for that. I want to make the program more efficient by avoiding closing the connection and having to open it again. Any suggestions/solutions? Thanks.
Sub ADOImportFromAccessTable()
Dim DBFullName As String
Dim TankRange As Range
Dim TimeRange As Range
Dim RpDate
Dim TankSelect As String
Dim TimeSelect As String
Dim r As Long
DBFullName = "U:\Night Sup\Production Report 2003 New Ver 5-28-10_KA.mdb"
Worksheets("TankHours").Activate
Set TankRange = Range("C5")
Set TimeRange = Range("D5")
Set RpDate = Range("B2").Cells
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TankRange = TankRange.Cells(1, 1)
Set TimeRange = TimeRange.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
TankSelect = "SELECT u.Tank" & vbCrLf & _
"FROM UnitOneRouting AS u" & vbCrLf & _
"WHERE u.Date = " & Format(RpDate, "\#yyyy-m-d\#") & vbCrLf & _
"ORDER BY u.Time, u.Tank;"
.Open TankSelect, cn, adOpenStatic, adLockOptimistic, adCmdText
TankRange.CopyFromRecordset rs
'End With
'rs.Close
' Set rs = Nothing
cn.Close
' Set cn = Nothing
' 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
TimeSelect = "SELECT u.Time" & vbCrLf & _
"FROM UnitOneRouting AS u" & vbCrLf & _
"WHERE u.Date = " & Format(RpDate, "\#yyyy-m-d\#") & vbCrLf & _
"ORDER BY u.Time, u.Tank;"
.Open TimeSelect, cn, adOpenStatic, adLockOptimistic, adCmdText
TimeRange.CopyFromRecordset rs
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
推荐答案
记录集列按 Select
语句的顺序返回。因此,如果您想首先使用 Tank
,然后像这样首先列出它: TankSelect = SELECT u.Tank,u.Time
...其余代码
Recordset columns are returned in the order of your Select
statement. So if you want Tank
to be first then list it first like this: TankSelect = "SELECT u.Tank, u.Time
... rest of your code
简单示例:
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
TankSelect = "SELECT u.Tank, u.Time" & vbCrLf & _
"FROM UnitOneRouting AS u" & vbCrLf & _
"WHERE u.Date = " & Format(RpDate, "\#yyyy-m-d\#") & vbCrLf & _
"ORDER BY u.Tank;"
rs.Open TankSelect, cn, adOpenStatic, adLockOptimistic, adCmdText
TankRange.CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
您可以还可以使用 GetRows
将特定字段返回到数组。这还使您可以操纵结果而不必对数据库进行任何其他调用。这是一个示例:
You can also return specific fields to an array by using GetRows
. This also allows you to manipulate your results without having to make any other call to the database. Here is an example:
Dim FieldsToSelect(0 To 1) As Variant
FieldsToSelect(0) = "TankVal"
FieldsToSelect(1) = "TimeVal"
With rs
TankSelect = "SELECT u.Tank AS TankVal, u.Time AS TimeVal" & vbCrLf & _
"FROM UnitOneRouting AS u" & vbCrLf & _
"WHERE u.Date = " & Format(RpDate, "\#yyyy-m-d\#") & vbCrLf & _
"ORDER BY u.Tank;"
.Open TankSelect, cn, adOpenStatic, adLockOptimistic, adCmdText
ResultsArray = .GetRows(Fields:=FieldsToSelect)
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
'Do what you want with array of results
ResultsArray
将按照声明它们的顺序列出字段结果在 FieldsToSelect
The ResultsArray
will list the field results in the order that you declare them in FieldsToSelect
当然,另一种选择是循环通过记录集并将特定字段输出到特定单元格中。
Of course, another option is to just loop through your recordset and output the specific fields into specific cells.
这篇关于打开/关闭ADO连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!