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!