Solved

Access 2010 VBA ETL code sample

Posted on 2013-11-08
5
1,198 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

735 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