CODEWITHSUNDEEP
excelms-accessvba
user11096783
user11096783

Reputation:

Extract to excel from Access database using vba

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? :(

Upvotes: 0

Views: 63

Answers (1)

Erik A
Erik A

Reputation: 32682

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

Upvotes: 1

Related Questions