looping through the accessdata set from access

Question

I have a access database. The database have a table. Table contain several fields including url field. I have created a form to migrate ms access data to oracle. Then I added a button to save data to the oracle. But before saving the url , some of the characters needed to be replaced by proper characters.

Private Sub Command59_Click()

    accesstableDataSet = currentAccessSheet.gettable('tableToMigrate')
    foreach ( record in accestableDataSet){
        rowUrl = record.url
        url = doencode(rowUrl)
        exportToMysql(url)

}

How can i do something like above by writing to a access form button ?

When you say "replaced by proper characters", can you be more specific? Can you give us some examples? — PowerUser, Nov 18 '13 at 15:43
Use a DAO Loop: http://stackoverflow.com/questions/5864160/code-to-loop-through-all-records-in-ms-access/5869361#5869361 — HK1, Nov 18 '13 at 19:28
rinjan (or HK1), please provide as an answer and select the answer as accepted. That way others can be better helped. — Smandoli, Dec 02 '13 at 22:41
Rather than duplicate the answer to the question which @HK1 linked to, I am voting to close this question as a duplicate. — StockB, Dec 13 '13 at 14:34

score 0 · Accepted Answer · answered Dec 29 '13 at 12:17

I don't like DAO. I like ADO ;) even if there are some limitations in MS Access, ADO provides access to a broader variety of data sources than DAO, and exposes some features of the Jet 4.0 database engine that aren't available from DAO.

Dim rst As ADODB.Recordset
Dim sSQL AS String, sUrl AS String

sSQL = "SELECT *" & vbCr & _
    "FROM TableToMigrate"
Set rst = New ADODB.Recordset
rst.Open sSQL, CurrentProject.Connection, adOpenStatic
With rst
    'fill rst object
    .MoveLast
    .MoveFirst
    'proccess through the
    Do While Not rst.EOF
        'get Url and decode it
        sUrl = DecodeUrl(.Fields("Url"))
        ExportToMySQL(sUrl)
        .MoveNext
    Loop
    .Close
End With

I assume that you know how to write DecodeUrl and ExportToMySQL functions/procedures ;)

looping through the accessdata set from access

1 Answers1