0

Basically, I want to extract one column from access base on my query in VBA. My sample code are below, No error were found but the only thing that is working is it just open the excel file were the data from access should be copied.

'Set db = OpenDatabase("\\location\names.mdb")
Set rs = db.OpenRecordset("select first_name from customerinfo " _
& "where datehired between #" & (DTPicker1) & "#  and #" & (DTPicker2) & "# ;")


If rs.RecordCount <> 0 Then

Dim x As Integer
Dim count As Integer
Dim PATH, file As String
PATH =("\\location\Extracted Data.xlsm")
file = Right$(PATH, Len(PATH) - InStrRev(PATH, "\"))
Workbooks.Open PATH
Workbooks(file).Activate

count = rs.RecordCount
For x = 2 To count
Workbooks(file).Sheets("extracted").Range("a" & x) = rs.Fields("first_name")

Next
End If'

I should have 3 result to be copied in my excel. can someone help me find what seems to be missing in my code? :(

Improve this question

1 Answer 1

Reset to default
1

For one, you're using .RecordCount before fully loading the recordset on a dynaset. That probably returns 1, since only 1 record has been loaded yet, making your code skip over For x = 2 To count (since that's for x=2 to 1)

Secondly, you're not actually moving the recordset.

A somewhat better approach (barring other errors I might've missed):

x = 2
Do While Not rs.EOF 'While not at the end of the recordset
    Workbooks(file).Sheets("extracted").Range("a" & x) = rs.Fields("first_name")
    x = x + 1
    rs.MoveNext 'Move to next record
Loop
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks @erik, your idea actually works and I just added some lines to make it suit for what i needed. thanks