Link to home
Create AccountLog in
Avatar of AxiServices
AxiServices

asked on

VBA query save to CSV in access

I want to query one table and get a list of values (valueA) from one column then step through each value and query another table and use the valueA for the where clause then save the new recordset to csv before going to the next valueA
something like this but in vba
dim arr as array = "select name from table1"
 
for each ar in arr
recordset = select * from table2 where name = " ar.tostring
recordset.save("name.csv")
next

Open in new window

Avatar of dublingills
dublingills
Flag of Ireland image

Not the most elegant solution because it bloats the database but certainly the simplest that I have used:

dim a as recordset

set a = currentdb.openrecordset("select name from table1")
while not a.eof
    currentdb.createquerydef("temp","select * from table2 where name = '" & a!name & "'")
    docmd.transfertext(acExport,,"temp","filename")
    currentdb.querydefs.delete("temp")
    a.movenext
wend
a.close

You can also iterate through each field in the recordset and append a comma then write this to an open file if you want neater code.  In this case I suggest you look at something like:

dim fileNumber As integer
dim a as recordset
dim count as integer
dim datarow as string
fileNumber = FreeFile()
   open filename" for output as #fileNumber
set a = currentdb.openrecordset("select name from table1")
while not a.eof
    for count = 0 to a.fields.count-1
    next
    print #filenumber, datarow
       a.movenext
wend
a.close
close #filenumber
Next


Remember to compact the database.
ASKER CERTIFIED SOLUTION
Avatar of dublingills
dublingills
Flag of Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account