how to dump array data into DB ?

Hi,
I have a VB app that reads data out of Excel spreadsheets
and loads needed data into arrays.
How can I dump the contents of the array into a local
Access DB table? There are 25 data items in each array
to be loaded into a table column.

The app loops through over 1000 Excel files and loads the array
with new data each time so I need the Access table
to be appened.

Code:

---


Option Explicit

 Private Type myPDBType
    col1 As String
    col2 As String
    col3 As String
    col4 As String
    col5 As String
    col6 As String
    col7 As String
 End Type

 Dim myPDBArray(1 To 25) As myPDBType


 Private Sub parse_pdb_file(strFile As String)
    Dim myExeclApp As New Excel.Application
    Dim myWorkbook As Excel.Workbook
    Dim myWorkSheet As Excel.Worksheet

    Dim strHeader As String
    Dim i As Integer

    Set myWorkbook = myExeclApp.Workbooks.Open(strFile)
    Set myWorkSheet = myWorkbook.Worksheets(1)

    strHeader = myWorkSheet.Cells(3, 1)

    For i = 6 To 30
      myPDBArray(i - 5).col1 = strHeader
      myPDBArray(i - 5).col2 = myWorkSheet.Cells(i, 1)
      myPDBArray(i - 5).col3 = myWorkSheet.Cells(i, 2)
      myPDBArray(i - 5).col4 = myWorkSheet.Cells(i, 3)
      myPDBArray(i - 5).col5 = myWorkSheet.Cells(i, 4)
      myPDBArray(i - 5).col6 = myWorkSheet.Cells(i, 5)
      myPDBArray(i - 5).col7 = myWorkSheet.Cells(i, 6)
    Next i

    myWorkbook.Close
    Set myWorkSheet = Nothing
    Set myWorkbook = Nothing
    Set myExeclApp = Nothing

    MsgBox "done"


 End Sub

 Private Sub cmdGo_Click()
    Dim strInFile As String
    Dim strPath As String

    strPath = "c:\Test\Dan\"

    strInFile = Dir$(strPath & "*.pdb")

    'loop thorugh all pdb files
    Do While strInFile <> ""
      parse_pdb_file (strPath & strInFile)

      strInFile = Dir
    Loop

 End Sub

---

Any help would be greatly appreciated.
I am new to VB, in fact this is my first project so go easy on me!
Having lots of fun learning VB,
- Dan

As long as the fields are defined the same (looks like char fields) in your access database, you can issue an SQL insert as you write the stuff into your array.

Public gdbDatabase as DAO.Database

'you'll need to open the access database somewhere...

dim sSQL as string

'insert the following in your for i=6 to 30 loop

sSQL= "insert into dbTable values('" & strHeader & "','" & myWorkSheet.Cells(i, 1) & "','" & myWorkSheet.Cells(i, 2) & "','" & myWorkSheet.Cells(i, 3) & "','" & myWorkSheet.Cells(i, 4) & "','" & myWorkSheet.Cells(i, 5) & "','" & myWorkSheet.Cells(i, 6)& "')"
gdbDatabase.Execute sSQL, dbFailOnError

Place the following code in a procedure to fill a db table with the info that is in the array.

Dim db As Database
Dim rs As Recordset
Dim i As Integer

'DB_PATH constant would need
'to be declared
Set db = OpenDatabase(DB_PATH)

'TABLE_NAME constant for the table name
'to dump the info into
Set rs = db.OpenRecordset(TABLE_NAME)

With rs
For i = 1 To 25
.AddNew
.Fields!COL_1 = myPDBArray(i).col1
.Fields!COL_2 = myPDBArray(i).col2
'AND SO FORTH
.Update
Next i
End With

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

Thanks guys, I will give these a try.
Much appreciated!