Solved

Access 2010 VBA ETL code sample

Posted on 2013-11-08
5
1,237 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 48

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 58
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

728 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