Solved

Access 2010 VBA ETL code sample

Posted on 2013-11-08
5
1,163 Views
Last Modified: 2013-11-11
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
Comment
Question by:TOWELLR
5 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39633059
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39633118
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
 
LVL 57
ID: 39633179
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
 

Author Comment

by:TOWELLR
ID: 39633308
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
 

Author Closing Comment

by:TOWELLR
ID: 39639787
Worked perfectly.  I made an additional step of converting this code to a function so I could attach it to a form button.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Best way to create dynamic, short cut menu 7 28
Programmer 14 49
MS Access 2010 Close Form  Event - Stop Form Closing 4 28
access query to sql server 3 20
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question