Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1420
  • Last Modified:

Access 2010 VBA ETL code sample

I am new to VBA and need a simple sample to demonstrate the following:
ETL to select records from a table, transform some data, insert into another table

Details:

TableA  Col1, Col2, Col3, Col4

TableB, Col1, Col2, Col3, Col4

How to:

Select all records from TableA (for processing ie Oracle cursor)
Put all field values in a variable ie p_col1, p_col2 etc
Loop through all records

CASE TableA.col1 = ‘1’
      P_newvalue = Concat p_col2 with p_col3
      Insert  into TableB fields (Col2) values (p_newvalue)

CASE tableA.col1 = ‘2’
      Different conversion logic

Thanks in advance
0
TOWELLR
Asked:
TOWELLR
1 Solution
 
mbizupCommented:
You could use a recordset to loop through all records (I'm assuming col1 is numeric and col2 text):


dim strSQL as string
dim rs as dao.recordset
dim P_newvalue

strSQL = "Select * from [TableA]"
Set rs = currentdb.openrecordset strsql, dbopendynaset

if rs.recordcount = 0 then
   msgbox "no records"
   exit sub
end if

do until rs.eof
    select case rs!Col1
         Case 1
               P_newvalue =  p_col2 & p_col3
               strSQL = " Insert  into TableB fields (Col2) values ('" & p_newvalue & "')"
               currentdb,execute strSQL
        case 2
        ' etc

    end select
  rs.movenext
loop
rs.close
set rs= nothing

Open in new window

0
 
Dale FyeCommented:
Or you could write a series of APPEND queries using the WHERE Clause to determine what gets inserted into TableB

INSERT INTO TableB (2)
SELECT [Col2] & [Col3] FROM TableA WHERE [Col1] = 1
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
You question really is too wide ranging.  There are any number of methods you can use to read data from a table, transform it in some way, and then write it back into a table.

Jim.
0
 
TOWELLRAuthor Commented:
Thanks mbizup!

That is the exact syntax framework I was looking for.
I think I will be able to get that to work.
Will test and post results.
0
 
TOWELLRAuthor Commented:
Worked perfectly.  I made an additional step of converting this code to a function so I could attach it to a form button.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now