因此,我想将查询结果插入到数组中,然后在数组上循环以将新行插入到新表中。
下面是我的代码:
Public cnn As New ADODB.Connection
Public db As DAO.Database
Public Sub SUD_Main()
Set db = Access.Application.CurrentDb
Set cnn = CurrentProject.Connection
Refreash
End Sub
Private Sub Refreash()
Dim DataArr() As String
Dim p As Variant
Dim sql As String
Dim XDATA As New ADODB.Recordset
Dim RDS As DAO.Recordset
Set RDS = db.OpenRecordset("tbl_dets")
sql = "SELECT DISTINCT NAME FROM Types_tbl WHERE NAME LIKE 'Rob%'"
XDATA.Open sql, cnn, adOpenStatic
'''
'' HERE I WANT TO FILL DataArr FROM XDATA
'''
DataArr = XDATA.GetRows
XDATA.Close
For Each p In DataArr
sql = "SELECT DISTINCT TID FROM Types_tbl WHERE NAME ='" & p & "'"
XDATA .Open sql, cnn, adOpenStatic
Do Until XDATA.EOF
DoEvents
'''Inserting new records to tbl_dets
RDS.AddNew
RDS!Name = p
RDS!TID= XDATA!TID
RDS.Update
XDATA.MoveNext
Loop
XDATA.Close
Next
End Sub
所以我错过了什么?我认为错误在数组中,但不知道如何修复它。
最佳答案
你的代码基本上是有效的,但是你犯了一些错误。GetRows
返回包含行号和字段的多维数组。因此,你不能把它串起来。您需要使用variant类型的数组。
Private Sub Refreash()
Dim DataArr() As Variant
Dim p As Variant
Dim sql As String
Dim XDATA As New ADODB.Recordset
Dim RDS As DAO.Recordset
Set RDS = db.OpenRecordset("tbl_dets")
sql = "SELECT DISTINCT NAME FROM Types_tbl WHERE NAME LIKE 'Rob%'"
XDATA.Open sql, cnn, adOpenStatic
'''
'' HERE I WANT TO FILL DataArr FROM XDATA
'''
'Make sure XData fetches all records
XData.MoveLast
XData.MoveFirst
DataArr = XDATA.GetRows
XDATA.Close
For Each p In PATTs
sql = "SELECT DISTINCT TID FROM Types_tbl WHERE NAME ='" & p & "'"
XDATA .Open sql, cnn, adOpenStatic
Do Until XDATA.EOF
DoEvents
'''Inserting new records to tbl_dets
RDS.AddNew
RDS!Name = p
RDS!TID= XDATA!TID
RDS.Update
XDATA.MoveNext
Loop
XDATA.Close
Next
End Sub
注意,对于这个sub还有一些我仍然不理解的地方,可能是因为它还没有完成,比如它在设置之后没有在任何地方使用DataArr,并且使用了ADO和DAO(在这种情况下,我宁愿只使用DAO)。