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...

Update:

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.

3 Answers

Relevance
  • 1 decade ago
    Favorite 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

        Debug.Print tbl.name

        i = 0

        For Each fld In tbl.Fields

            Debug.Print Space(4) & fld.name

            i = i + 1

        Next

      End If

    Next

    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

        Debug.Print ss

        rs.MoveNext

    Wend

    rs.Close: Set rs = Nothing

    Yahoo is compressing the Set rs statement, so here it is on 2 lines:

    Set rs = CurrentDb.OpenRecordset

    ("ORDERS", dbOpenTable)

    §

  • Anonymous
    1 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.

  • 1 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.
Still have questions? Get your answers by asking now.