Using VBA to Loop through Access Table

[David McCulloch]

I am relatively new to Access VBA coding and need some help.

QUESTION:
Using Access 2003 and VBA, how can I loop through a table and bring some of
a row's columns into VB variables?

PSEUDO CODE:
Open table
Do Until EOF
var1 = ...
var2 = ...
< use the variables >
Advance to next row in table
Loop
Close

- - - - - - - - - - - - - - - - - - -

OPTIONAL READING:
Why do I care? The short story is that I am trying to map data from an
unnormalized Access table (i.e., one that was imported from an Excel
spreadsheet) to a normalized Access table. To do that, I want to perform an
update query (formatted in VBA) for each row of a column-mapping table
....and to start my journey, I must learn how to read my mapping table.
Perhaps I have been looking in all the wrong places, but I could not find
any sample code on the web.

MORE OPTIONAL READING:
What's the long story? It all starts with a spreadsheet that is imported
into Access. Abstracted, its format is (with column headers):

Part, Vendor1_Price, Vendor2_Price, Vendor3_Price, ...
Part1, $1000, $2000, $1500, ...
Part2, $5000, $5500, $6500, ...
Part3, $3000, $2500, $4000, ...
etc...

There are about 1,000 Parts and many Vendors that are subject to change.

I want to create a normalized table from the above data:

Part, Vendor, Price
Part1, "Vendor1", $1000
Part1, "Vendor2", $2000
Part1, "Vendor3", $1500
Part2, "Vendor1", $5000
Part2, "Vendor2", $5500
Part2, "Vendor3", $6500
Part3, "Vendor1", $3000
Part3, "Vendor2", $2500
Part3, "Vendor3", $4000
etc...

I could create a table of Vendors and generate a query that would give me
all combinations of Parts and Vendors. Unfortunately, how would I set
Price?

I already have a Vendor table and a Parts table from which I create a
normalized table of all Parts/Vendor combinations (similar to above, but
with an empty Price column). From VBA, I plan to loop through the Vendors
table and dynamically create a SQL statement to join the normalized and
unnormalized tables (by Part) and to update the normalized table's Prices,
vendor by vendor. I can't do that in one query, because a query can't
dynamically reference a column with a row-dependant name (for example, the
query's Price formula would have to reference "Vendor#_Price", but the
Vendor is row-specific).

Thanks very much!

Dave

 

[Ken Snell \(MVP\)]

I wouldn't use VBA code to normalize data. Instead, use a UNION query to
normalize the data:

SELECT T1.Part, T1.Vendor1_Price
FROM TableName AS T1
UNION ALL
SELECT T2.Part, T2.Vendor2_Price
FROM TableName AS T2
UNION ALL
SELECT T3.Part, T3.Vendor3_Price
FROM TableName AS T3
UNION ALL
SELECT T4.Part, T4.Vendor4_Price
FROM TableName AS T4;

You can add more subqueries to the above if you have more than 4 prices.

Then create an append query that uses the above query (save it) as the
source table; the append query is then used to append the data to your new
table.

 

[David McCulloch]

Ken,
VBA is the only way to automate the process so a non-programmer could run
the application without frequent maintenance. Specifically, a UNION query
would not accommodate [frequent] changes (i.e., new vendors, etc.) to the
unnormalized data. I was planning to use VBA to dynamically construct a
UNION query, but to do that, VBA would need data from other tables.

Any idea how I could find sample code for reading rows from a table?

Dave

 

[Ken Snell \(MVP\)]

Looping through a recordset is done this way:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SQL statement")
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
Do While rst.EOF = False
'
' put code here to do something with record
'
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Ken,
VBA is the only way to automate the process so a non-programmer could run
the application without frequent maintenance. Specifically, a UNION query
would not accommodate [frequent] changes (i.e., new vendors, etc.) to the
unnormalized data. I was planning to use VBA to dynamically construct a
UNION query, but to do that, VBA would need data from other tables.

Any idea how I could find sample code for reading rows from a table?

Dave

I wouldn't use VBA code to normalize data. Instead, use a UNION query to
normalize the data:

SELECT T1.Part, T1.Vendor1_Price
FROM TableName AS T1
UNION ALL
SELECT T2.Part, T2.Vendor2_Price
FROM TableName AS T2
UNION ALL
SELECT T3.Part, T3.Vendor3_Price
FROM TableName AS T3
UNION ALL
SELECT T4.Part, T4.Vendor4_Price
FROM TableName AS T4;

You can add more subqueries to the above if you have more than 4 prices.

Then create an append query that uses the above query (save it) as the
source table; the append query is then used to append the data to your
new table.

 

[David McCulloch]

Ken,
Thanks very much -- that helps a lot!

Dave

Looping through a recordset is done this way:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SQL statement")
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
Do While rst.EOF = False
'
' put code here to do something with record
'
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

--

Ken Snell
<MS ACCESS MVP>

Ken,
VBA is the only way to automate the process so a non-programmer could run
the application without frequent maintenance. Specifically, a UNION
query would not accommodate [frequent] changes (i.e., new vendors, etc.)
to the unnormalized data. I was planning to use VBA to dynamically
construct a UNION query, but to do that, VBA would need data from other
tables.

Any idea how I could find sample code for reading rows from a table?

Dave

I wouldn't use VBA code to normalize data. Instead, use a UNION query to
normalize the data:

SELECT T1.Part, T1.Vendor1_Price
FROM TableName AS T1
UNION ALL
SELECT T2.Part, T2.Vendor2_Price
FROM TableName AS T2
UNION ALL
SELECT T3.Part, T3.Vendor3_Price
FROM TableName AS T3
UNION ALL
SELECT T4.Part, T4.Vendor4_Price
FROM TableName AS T4;

You can add more subqueries to the above if you have more than 4 prices.

Then create an append query that uses the above query (save it) as the
source table; the append query is then used to append the data to your
new table.
--

Ken Snell
<MS ACCESS MVP>


I am relatively new to Access VBA coding and need some help.

QUESTION:
Using Access 2003 and VBA, how can I loop through a table and bring
some of a row's columns into VB variables?

PSEUDO CODE:
Open table
Do Until EOF
var1 = ...
var2 = ...
< use the variables >
Advance to next row in table
Loop
Close

- - - - - - - - - - - - - - - - - - -

OPTIONAL READING:
Why do I care? The short story is that I am trying to map data from an
unnormalized Access table (i.e., one that was imported from an Excel
spreadsheet) to a normalized Access table. To do that, I want to
perform an update query (formatted in VBA) for each row of a
column-mapping table ...and to start my journey, I must learn how to
read my mapping table. Perhaps I have been looking in all the wrong
places, but I could not find any sample code on the web.

MORE OPTIONAL READING:
What's the long story? It all starts with a spreadsheet that is
imported into Access. Abstracted, its format is (with column headers):

Part, Vendor1_Price, Vendor2_Price, Vendor3_Price, ...
Part1, $1000, $2000, $1500, ...
Part2, $5000, $5500, $6500, ...
Part3, $3000, $2500, $4000, ...
etc...

There are about 1,000 Parts and many Vendors that are subject to
change.

I want to create a normalized table from the above data:

Part, Vendor, Price
Part1, "Vendor1", $1000
Part1, "Vendor2", $2000
Part1, "Vendor3", $1500
Part2, "Vendor1", $5000
Part2, "Vendor2", $5500
Part2, "Vendor3", $6500
Part3, "Vendor1", $3000
Part3, "Vendor2", $2500
Part3, "Vendor3", $4000
etc...

I could create a table of Vendors and generate a query that would give
me all combinations of Parts and Vendors. Unfortunately, how would I
set Price?

I already have a Vendor table and a Parts table from which I create a
normalized table of all Parts/Vendor combinations (similar to above,
but with an empty Price column). From VBA, I plan to loop through the
Vendors table and dynamically create a SQL statement to join the
normalized and unnormalized tables (by Part) and to update the
normalized table's Prices, vendor by vendor. I can't do that in one
query, because a query can't dynamically reference a column with a
row-dependant name (for example, the query's Price formula would have
to reference "Vendor#_Price", but the Vendor is row-specific).

Thanks very much!

Dave