source to target

I don't understand what you mean. Can you explain further?

Basic ETL stuff. Within access i have to take stuff from a staging table, transform it (lookups, verify format etc) and then load it into a target table that can be reported on.

hmmm that is what i figured.  How do you suggest i do this sort of ETL? I am planning on creating a bunch of queries that i will call from the VBA

Queries are the simplest solution unless you have some complex multi-record processing to handle.

Most of the stuff is pretty simple.... verifying that required fields have data, making sure certain fields have valid data where i will verify in a lookup table (for example US states), concatenation of fields etc.  I think there might be some stuff that is more complex then that but that sums it up pretty good i think.

Queries and basic code logic is what I typically use, regardless of the platform. If you just have a handful of rules - no more than 10 - 15 - then often you just write code blocks that handle these. If you have more than that, I'd consider setting up a table to hold the rules, along with a method to order, prioritize, and set their severity level (along with what happens if they fail the tests - do you abort, or just continue and report, etc).

OK great. That helps.

It is not clear if you need help with any of the specific coding, (or if I am understanding your question correctly)
...But, in any event, ...here are two very simple examples of some validation loops:

'Checks for null values
Dim strMsg As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT ID, YourRequiredField, YourStateField FROM YourTable WHERE Isnull(YourRequiredField)")
rst.MoveFirst
Do Until rst.EOF
    strMsg = strMsg & vbCrLf & "ID: " & rst!ID & ", is missing data."
    rst.MoveNext
Loop
MsgBox strMsg

Select allOpen in new window

'Checks for non-matching values
Dim strMsg As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT YourTable.ID, YourTable.YourRequiredField, YourTable.YourStateField FROM YourTable")
rst.MoveFirst
Do Until rst.EOF
    If IsNull(DLookup("State", "tblStates", "State=" & "'" & rst!YourStateField & "'")) Then
        strMsg = strMsg & vbCrLf & "ID: " & rst!ID & "'s value of: " & "'" & rst!YourStateField & "'" & " is invalid or missing "
    End If
    rst.MoveNext
Loop
MsgBox strMsg

Select allOpen in new window

Obviously you can do things like; load a table with the results, ...etc

JeffCoachman

I've requested that this question be closed as follows:

Accepted answer: 0 points for vbnetcoder's comment #a40986926

for the following reason:

ty

ty