VB Programming in Access

[Guest]

I have a VB Module that creates an array of numbers and I would like to know
how to dump them into (populate) a table in Access. And also create a new
table from the VB Module, hopefully in the background. Any one who has any
examples of code I would appreciate suggestions.

 

[Brendan Reynolds]

Public Sub ArrayToTable()

'for testing only, delete table if exists
On Error Resume Next
CurrentProject.Connection.Execute "DROP TABLE ArrayTest"
On Error GoTo 0

'create table
CurrentProject.Connection.Execute "CREATE TABLE ArrayTest " & _
"(NumberField int PRIMARY KEY)"

'we don't need the array, we could assign the numbers directly
'to the table, this is for demonstration purposes only
Dim lngLoop As Long
Dim alngNumbers(9) As Long
For lngLoop = 0 To 9
alngNumbers(lngLoop) = lngLoop
Next lngLoop

For lngLoop = LBound(alngNumbers) To UBound(alngNumbers)
CurrentProject.Connection.Execute "INSERT INTO ArrayTest " & _
"(NumberField) VALUES (" & alngNumbers(lngLoop) & ")"
Next lngLoop

End Sub