Exporting Large Array to Access (2003)

[ExcelMonkey]

I have a routine in ExcelVBA which creates a 2D array which is quite large.
It generates 1,000,000 million random numbers over a period of years (10). I
want to export each years worth of data upon generating the last random
number in each year.

I want to export to an Access File. I am new to access and have never
really used it before. So when finished, the Access File will have the full
2D array in it expressing my million data points over 10 years. Assuming it
will have to happen as follows:

1) Create Access File
2) Generate a years worth of random numbers in Excel/VBA
3) Export years worth of random numbers from Array in Excel/VBA
4) Come back to Excel and generate next years of random numbers
5) When finished, save Access file

How do I do this?

My Excel/VBA code looks as follows:

Sub PopulateArrayAndExporttoAccess()
Dim CurrentYear As Double
Dim CurrentNumber As Double
Dim BigArray As Variant
Dim LastNum As Double
Dim LastYear As Double

Application.ScreenUpdating = False

ReDim BigArray(1 To 1000000)

LastNum = UBound(BigArray)
LastYear = 10

For CurrentYear = 1 To LastYear
For CurrentNumber = 1 To LastNum
BigArray(CurrentNumber) = Rnd()
Application.StatusBar = "Creating random numbers: " &
Round((CurrentNumber / (LastNum * LastYear)) * 100, 2) & "%"

Next
'Export the full Array to Access
'Put export code here......
Application.StatusBar = "Exporting to Access."
Next

Application.StatusBar = ""

End Sub

Thanks

EM

 

[merjet]

http://www.erlandsendata.no/english/index.php?t=envbadac

Try the 2nd or 3rd link under "Document".

You will need to have created a Table in Access, which is easy.

Hth,
Merjet

 

[ExcelMonkey]

Actually I started off with the Erlandsen site. The code kept failing on the
"tablename" snippet. I didn't know how to create a table in Access. I am
assuming that what I want is to create everything in the code I have. That
is, the Access file will not exist when I initiate my Excel/VBA code. Is
there a way to create this table in code?

Thanks

EM

 

[merjet]

I have not tested it, but you can try here:
http://www.bygsoftware.com/Excel/SQL/UsingSql.html

Hth,
Merjet

 

[merjet]

Here is a DAO example:


Private Sub CreateTable()
Dim db As DAO.Database
Dim td As TableDef

Set db = DBEngine.Workspaces(0).OpenDatabase("c:\Temp\MyDb1.mdb")
Set td = pDB.CreateTableDef("Table1")
With td
'Add fields to database
.Fields.Append .CreateField("ID", dbInteger)
.Fields.Append .CreateField("Name", dbText)
.Fields.Append .CreateField("Points", dbDouble)
db.TableDefs.Append td
End With
Set td = Nothing
Set db = Nothing
End Sub

Hth,
Merjet