how to dump array data into DB ?

[Piz Bruin]

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.

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

[JHausmann]

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

[ttingen]

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

[Piz Bruin]

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