Easiest way to create a large Access Table

[BobC]

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.

 

[Allen Browne]

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.

 

[John W. Vinson]

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]

 

[BobC]

Thank You!

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]

 

[Jeff Boyce]

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]

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

 

[BobC]

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

 

[John W. Vinson]

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()

 

[Jeff Boyce]

Thanks for the clarification...

Regards

Jeff Boyce
Microsoft Office/Access MVP