A little terminology
SQL mainly refers to a mostly universal language syntax to work with databases. To the best of my knowledge, all versions are similar, if not identical in syntax structure. Once you learn one, you have a major headstart learning others. Unless you're working interactively only, most of the time you end up including SQL within what you're calling 'macros' to achieve a desired result.
Macros, in my opinion, is a dated term and 'old functionality'. The actual implementation within Access97+ is really a partial-step towards VBA programming. Although you can specify a series of actions to be performed, you lack the ability to handle errors, or handle branching logic.
Visual Basic for Applications programming on the other hand, probably shouldn't be termed 'Macros' - you create Functions and Subroutines that are saved in within Modules that collectively form a Procedure for performing whatever task is needed. Interestingly, at least within Access, many of the VBA Methods are really Macros (DoCmd.?????). The latter is why sometimes what you call things might be viewed as a grey area. Why calling it a 'Macro' isn't 100% incorrect.
Repeating Bat17 - in most cases, running a SQL query is probably going to run alot faster than doing it from code. A common VBA technique is to 'walk the recordset'. Basically, this takes the form of:
Code:
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Set dbs = CurrentDB()
Set rs = dbs.OpenRecordset(sqlCommand, dbOpenSnapshot)
With rs
Do Until rs.EOF
' Code to look at the fields within each given record.
.Movenext
Loop
End With
The above uses DAO (Data Access Objects) to define variables to open the database, open a recordset, identify what is in the recordset, and then loop through it within a Do...Loop until it finds the End of File (rs.EOF) .MoveNext is really the command "rs.MoveNext" but the 'With..End With' structure allows you to avoid typing some of the information out.
This lets you refer to fields within each record as:
Code:
.Fields(fieldname).Value
!fieldname
.Fields(a_column_number).Value
vs
Code:
rs.Fields(fieldname).Value
rs!fieldname
rs.Fields(a_column_number).Value
Also repeating Bat17, depending on what you need to do, you may end up updating individual fields/records using recordset objects, or, particularly, when there are a lot of records to update, you use SQL instead.
Code:
strSQL = "Update tblname Set fieldname1 = value1 "
strSQL = strSQL & "WHERE indexfield = value2"
The above demonstrates syntax to update fieldnam1 in tablename where an index field's value is something. The WHERE Parameter is optional. Removing it would update ALL records in the table regardless of whether there is only 1 or 50,000.
The latter is why SQL is so powerful and easy to use. Where else could you use a total of 5 words to update an unlimited number of records?
The "walking the recordset" option above required 9 lines and I didn't even include the the ones that would do the field update. A VBA using SQL option would only require none of the above - one line for the Query, one for the command to execute it.
I realize I just wrote a novel for what may be a request for a quick answer, but lets just say I'm in a talkative mood today.
Mike
