Easiest way to create a large Access Table

I need to create a large Access lookup table.
The table will be roughly 2500 records consisting of 12 fields.
The entries into the table can be created with simple equations and a
few nested loops. I was thinking about using VB6; but wanted to get some
opinions.

There's an example at the end of this page of how to OpenRecordset and loop
through to create 1000 records:
http://allenbrowne.com/ser-39.html

Between the AddNew and Update, specify your fields - one per line - and
assign whatever values you want. You may be able to pull some data from
existing tables, or you could use a combination of Chr() with Rnd() to
values.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BobC" wrote in message
...
I need to create a large Access lookup table.
The table will be roughly 2500 records consisting of 12 fields.
The entries into the table can be created with simple equations and a few
nested loops. I was thinking about using VB6; but wanted to get some
opinions.

On Thu, 07 Feb 2008 22:28:40 -0500, BobC wrote:

I need to create a large Access lookup table.
The table will be roughly 2500 records consisting of 12 fields.
The entries into the table can be created with simple equations and a
few nested loops. I was thinking about using VB6; but wanted to get some
opinions.

Well, VBA and VB6 are syntactically very similar. You don't say anything about
the nature of the calculations, but it would be straightforward to create a
table; open a Recordset based on it; and loop through the creation sequence
using the AddNew method to create a record, and then set the values.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("YourLookupTable", dbOpenDynaset)
For ... your loop expression
rs.AddNew
rs!ThisField = thisvalue
rs!ThatField = thatvalue
...
rs.Update ' write out the record
Loop
rs.Close
Set rs = Nothing

John W. Vinson [MVP]

Thank You!

John W. Vinson wrote:
On Thu, 07 Feb 2008 22:28:40 -0500, BobC wrote:

I need to create a large Access lookup table.
The table will be roughly 2500 records consisting of 12 fields.
The entries into the table can be created with simple equations and a
few nested loops. I was thinking about using VB6; but wanted to get some
opinions.

Well, VBA and VB6 are syntactically very similar. You don't say anything about
the nature of the calculations, but it would be straightforward to create a
table; open a Recordset based on it; and loop through the creation sequence
using the AddNew method to create a record, and then set the values.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("YourLookupTable", dbOpenDynaset)
For ... your loop expression
rs.AddNew
rs!ThisField = thisvalue
rs!ThatField = thatvalue
...
rs.Update ' write out the record
Loop
rs.Close
Set rs = Nothing

John W. Vinson [MVP]

Bob

If "...the entries ... can be created with simple equations...", why do you
need to store the calculated values?

You've explained "how" you want to do something, but not why it makes more
sense to have a "large Access lookup table" than to use a query to generate
the calculated value(s) you need "on the fly".

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"BobC" wrote in message
...
I need to create a large Access lookup table.
The table will be roughly 2500 records consisting of 12 fields.
The entries into the table can be created with simple equations and a few
nested loops. I was thinking about using VB6; but wanted to get some
opinions.

I'm having a syntax??? issue with:
Set db = Data.mdb
in the test code below:

Sub AddRecords()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim I As Integer
Set db = Data.mdb
Set rs = db.OpenRecordset("tblNewReten", dbOpenDynaset)
For I = 1 To 10
rs.AddNew
rs!HA# = 100 + I
rs!POLYR = 2007 + I
rs!PROPRET = 100000
rs!LIABRET = 100000
rs!LIABAGG = 150000

rs.Update ' write out the record
Next

John W. Vinson wrote:
On Thu, 07 Feb 2008 22:28:40 -0500, BobC wrote:

I need to create a large Access lookup table.
The table will be roughly 2500 records consisting of 12 fields.
The entries into the table can be created with simple equations and a
few nested loops. I was thinking about using VB6; but wanted to get some
opinions.

Well, VBA and VB6 are syntactically very similar. You don't say anything about
the nature of the calculations, but it would be straightforward to create a
table; open a Recordset based on it; and loop through the creation sequence
using the AddNew method to create a record, and then set the values.

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("YourLookupTable", dbOpenDynaset)
For ... your loop expression
rs.AddNew
rs!ThisField = thisvalue
rs!ThatField = thatvalue
...
rs.Update ' write out the record
Loop
rs.Close
Set rs = Nothing

John W. Vinson [MVP]

It is a 'lookup' table containing constants that is changed by the user;
I am just setting up the basic table.

Jeff Boyce wrote:
Bob

If "...the entries ... can be created with simple equations...", why do you
need to store the calculated values?

You've explained "how" you want to do something, but not why it makes more
sense to have a "large Access lookup table" than to use a query to generate
the calculated value(s) you need "on the fly".

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"BobC" wrote in message
...
I need to create a large Access lookup table.
The table will be roughly 2500 records consisting of 12 fields.
The entries into the table can be created with simple equations and a few
nested loops. I was thinking about using VB6; but wanted to get some
opinions.

On Sun, 10 Feb 2008 23:36:46 -0500, BobC wrote:

I'm having a syntax??? issue with:
Set db = Data.mdb
in the test code below:

Sub AddRecords()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim I As Integer
Set db = Data.mdb

Well, I have no idea where you got that syntax but obviously it doesn't work.

If this code exists in the database named Data.mdb, just use

Set db = CurrentDb()
--
John W. Vinson [MVP]

Thanks for the clarification...

Regards

Jeff Boyce
Microsoft Office/Access MVP


"BobC" wrote in message
...
It is a 'lookup' table containing constants that is changed by the user; I
am just setting up the basic table.

Jeff Boyce wrote:
Bob

If "...the entries ... can be created with simple equations...", why do
you need to store the calculated values?

You've explained "how" you want to do something, but not why it makes
more sense to have a "large Access lookup table" than to use a query to
generate the calculated value(s) you need "on the fly".

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"BobC" wrote in message
...
I need to create a large Access lookup table.
The table will be roughly 2500 records consisting of 12 fields.
The entries into the table can be created with simple equations and a
few nested loops. I was thinking about using VB6; but wanted to get some
opinions.