VBA and Access?
I need to write a VBA function that will run sql commands on an Access DB file. I don't remember how to do this. I need to be able to go over each field and each row of the resultset so I can write out the data in an appropriate fashion for a csv file. Can I see an example? I've looked online, but nobody keeps examples of vba anymore...
I don't want to go directly to csv. I want to control the data between the sql statement and the file. I remember at somepoint I could say ".Fields(0)" or some other int.
- BlueFeatherLv 61 decade agoFavorite Answer
Here is an example (in Access 2003) that might help
Dim tbl As TableDef, fld As Field, i As Integer
For Each tbl In CurrentDb.TableDefs
If tbl.name = "CUSTOMERS" Or tbl.name = "ORDERS" Then
i = 0
For Each fld In tbl.Fields
Debug.Print Space(4) & fld.name
i = i + 1
Attempting to access the Value property in the Fields collection is not allowed, so you can't use the preceding example to get field values. Instead, you could do somthing like the following to generate string values that are compatible with the csv format.
Dim rs As DAO.Recordset, ss As String
Set rs = CurrentDb.OpenRecordset("ORDERS", dbOpenTable)
rs.Index = "PrimaryKey"
While Not rs.EOF
ss = rs!CustomerID & ", " & rs!OrderID & ", " & rs!OrderDate
rs.Close: Set rs = Nothing
Yahoo is compressing the Set rs statement, so here it is on 2 lines:
Set rs = CurrentDb.OpenRecordset
- Anonymous1 decade ago
This is a cumbersome process, converting the data to csv. I doubt there are any scripts posted for this. From the main database window you can select the code window and write it straight in. It is partly automated to guide your scripts.
- jimponderLv 51 decade ago
Can you set it up in Access and then 'view source' or 'view code' It will show you the sql commands.Source(s): IT Pro.