input info from excel to a database

hi
this is my code for pulling the info from the excel spreadsheet
is seem to work so happy days

Code:

---


Dim xl As New Excel.Application 
 
    Dim xlsheet As Excel.Worksheet 
 
    Dim xlwbook As Excel.Workbook 
 
 
 
  ' Const SHEET_NAME As String = "CreditRatings" 
 
    Dim i As Integer 
 
    Dim j As Integer 
 
    Dim sBuffer As String 
 
    Dim LastRowColB As Integer 
 
    Dim fn As Integer 
 
 
    Set xlwbook = xl.Workbooks.Open("c:\CreditRatingsReport.xls") 
 
  Set xlsheet = xlwbook.Sheets.Item("CreditRatings") 
 
 
 
    LastRowColB = xl.Range("B65536").End(xlUp).row 
 
    fn = FreeFile 
 
    'Open sOUTPUT_PATH & "\Ivory_SecuritiesIssued" & ".csv" For Append As #fn 
 
    For i = 2 To LastRowColB 
 
        sBuffer = "" 
 
        For j = 1 To 24 
 
 
 
        If IsDate(xlsheet.Cells(i, j)) Then 
 
            sBuffer = sBuffer & Format(xlsheet.Cells(i, j)) & "|" 
 
        Else 
 
            sBuffer = sBuffer & Trim(xlsheet.Cells(i, j)) & "|" 
 
        End If 
 
             
 
        Next j 
 
      ' MsgBox sBuffer 
 
    Next i

---

now its the next part i cant get i need to put that into the table. but i have to stick to the structure in here so can any one help please.
i need to get to to put the info into the table buy doing it something like this

Code:

---

DBCALL "SELECT", "ACRT", Cmd$ -- calls the table in the database 
 
   
 Cmd$ = "INSERT INTO ACRT (CNO, MoodysRate, SNPRate, FITCHRate, MoodysWatch, snPWatch, FITCHWatch, MoodysIRB) Values ) 
     
     
   
     
    DBCALL "add", "ACRT", Cmd$ adds the info into tthe table 
         
    If result <> dbSUCCESS And result <> 100 Then 
                MsgBox "Data Base Unavailable or Unexpected error in Data Base or batch running." 
                Message 43 
                result = 1 
                 
    End If

---

its the middle part i can figure out the insert into table part anyone have any ideas

really stuck on this can anyone help

Well for one thing this statement is missing a lot of required info

Code:

---

Cmd$ = "INSERT INTO ACRT (CNO, MoodysRate, SNPRate, FITCHRate, MoodysWatch, snPWatch, FITCHWatch, MoodysIRB) Values )

---

It should be

Code:

---

Cmd$ = "Insert Into TableName ( Your field names as above) Values ( The actual values for each field listed in the first part)

---

I have no idea what this line is actually doing

Code:

---

DBCALL "add", "ACRT", Cmd$ adds the info into tthe table

---

And here you are checking result but you have not assigned result=anything so this is not goin to work either unless of course result is defined as a public variable and is assigned in the DBCall routine

Code:

---

If result <> dbSUCCESS And result <> 100 Then 
                MsgBox "Data Base Unavailable or Unexpected error in Data Base or batch running." 
                Message 43 
                result = 1 
                 
    End If

---

hi the part im missing alot is really the part im stuck on.
for this part

Code:

---

It should be

Cmd$ = "Insert Into TableName ( Your field names as above) Values ( The actual values for each field listed in the

---

i want the values to be entered automatically. its a big long spreadsheet and this will be run on a daily basic and the spreadsheet values will change
is an array the best way to get this done or is there a better way

The values would be variables that you would read from the spread sheet then they would be inserted into the database. This would be done in a loop so it calls the insert for each new record. Keep in mind Insert is only for adding new records it is not for updating existing records.

Of course if Your goal is to have lots of data entered into a database where people are entering new records all the time the best way would be to drop Excel from the equation and create a vb program for data entry/edited that talks directly to the database.

thanks will give it a go.
info will be deleted from table each time we do an add.