Today, I was replicating a macro that I have authored and ran successfully on countless occasions. Today, when I tried to replicate the VBA coding in another file, I received this error: "Run-time error '3265': Item not found in this collection." The purpose of this macro is to have Access open in the background from the database path that the user specifies (dbpath = Range("C4").Value), run a query that I have defined and input one parameter (Period = Sheets("Input").Range("C2").Value). The error is in line 15.
Here is my code:
1. Sub Refresh_Data_Tables()
2. '
3. ' Refresh_Data_Tables Macro
4. ' This macro will clear old data and copy new data from Access.
5. '
6.
7. Period = Sheets("Input").Range("C2").Value
8. dbpath = Range("C4").Value
9.
10. Set oAccess = CreateObject("Access.database")
11. oAccess.Application.OpenCurrentDatabase (dbpath)
12. oAccess.Application.Visible = False
13. Set Db = oAccess.CurrentDb()
14. Set qdf = Db.QueryDefs("qry_PE Open")
15. qdf.Parameters(0) = Period
16. Set rstyear = qdf.OpenRecordset
17. fldcount = rstyear.Fields.Count
18. If rstyear.EOF <> True Then
19. rstyear.MoveLast
20. rstyear.MoveFirst
21. End If
22. Sheets("Open PEs").Select
23. Range("A6:I5000").Select
24. Selection.ClearContents
25. Range("A6").Select
26. Sheets("Open PEs").Range("A6").CopyFromRecordset rstyear
27. Set qdf = Nothing
28. Set rstyear = Nothing
29.
30. End Sub
Any help is appreciated. Thanks!
Here is my code:
1. Sub Refresh_Data_Tables()
2. '
3. ' Refresh_Data_Tables Macro
4. ' This macro will clear old data and copy new data from Access.
5. '
6.
7. Period = Sheets("Input").Range("C2").Value
8. dbpath = Range("C4").Value
9.
10. Set oAccess = CreateObject("Access.database")
11. oAccess.Application.OpenCurrentDatabase (dbpath)
12. oAccess.Application.Visible = False
13. Set Db = oAccess.CurrentDb()
14. Set qdf = Db.QueryDefs("qry_PE Open")
15. qdf.Parameters(0) = Period
16. Set rstyear = qdf.OpenRecordset
17. fldcount = rstyear.Fields.Count
18. If rstyear.EOF <> True Then
19. rstyear.MoveLast
20. rstyear.MoveFirst
21. End If
22. Sheets("Open PEs").Select
23. Range("A6:I5000").Select
24. Selection.ClearContents
25. Range("A6").Select
26. Sheets("Open PEs").Range("A6").CopyFromRecordset rstyear
27. Set qdf = Nothing
28. Set rstyear = Nothing
29.
30. End Sub
Any help is appreciated. Thanks!