Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access 2010 VBA ETL code sample

Posted on 2013-11-08
5
Medium Priority
?
1,355 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 2000 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 49

Expert Comment

by:Dale Fye
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

824 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