Split data help

Hi, Usually i used to transfer data from Excell multible column to access db. Now i have data in one column seperated by , as mention below. Please what should i change in code below to transfer data from excel to access db? or please advice me how to do it? i appreciate your help in advance.
Excel sheet data:
In column A,

Code:

---

B,Brss,4645656,basmoh,6565,failed

---

Code

Code:

---

'
'
If rs1.Fields(1) <> vbNullString Then
        strSQLSELECT = "SELECT COUNT(*) AS recCount FROM " & strTable1 & " WHERE "
        strSQLSELECT = strSQLSELECT & "colname = '" & rs1.Fields(0) & "' AND "
        strSQLSELECT = strSQLSELECT & "colnumber = '" & rs1.Fields(0) & "' AND "
        strSQLSELECT = strSQLSELECT & "colICCID = '" & rs1.Fields(0) & "' AND "
        strSQLSELECT = strSQLSELECT & "colstatus ='" & Trim$(rs1.Fields(0)) & "',") & "'"
        rs2.Open strSQLSELECT, cnn, adOpenStatic, adLockOptimistic
        If rs2![recCount] = 0 Then
            strSQL = " (colname,colnumber,colICCID,colstatus) VALUES("
            strSQL = strSQL & "'" & rs1.Fields(1) & "',"
            strSQL = strSQL & "'" & rs1.Fields(2) & "',"
            strSQL = strSQL & "'" & rs1.Fields(3) & "',"
            strSQL = strSQL & "'" & Trim$(rs1.Fields(4)) & "')"
            cnn.Execute strSQl1 & strTable1 & strSQL
            cnn.Execute strSQl1 & strTable2 & strSQL
        End If
'
'

---

Here is AN approach...I put your excel field into a textbox for clarification. Instead of putting the individual values as I did into an array of textboxes, insert them into your database instead.

Code:

---

Dim mySplitArray() As String
Dim myText As String
myText = Text1.Text & ","  'use your excel cell value instead of my text1.text (add a comma at the end)
mySplitArray = Split(myText, ",") 'splits your value
Dim i As Integer
For i = 0 To UBound(mySplitArray()) - 1
    Text2(i).Text = mySplitArray(i)  'instead of putting the array into textboxes, do your insert cmd here
Next i

---

Hello,

This feels more like an Office question, rather than specifically a VB6 one.

As such, I am going to move it to the Office Development Forum.

Let me know if you have any questions.

Gary

Dear Sam, Thank you for your reply, i really enjoy apply your code i did make array and work such fine. I did not figure out how to add it to my project. I attach the source code with excel data and Access DB, I would be appreciate if you can look at it and help me. ofcource if you have time for me bro. Also, Thank you gep. Thank you again

Looking forward for your advice Sam? Thank you very much.

Any advice please? Thank you very much.

Try this:

Code:

---

Private Sub TransferExcelFile2Database_Click()
'On Error GoTo errortrap
Dim strTable1 As String
Dim strTable2 As String
Dim strSQL As String
Dim strSQl1 As String
Dim strSQLSELECT As String
strTable1 = "Table3"
strTable2 = "Table4"
strSQl1 = "INSERT INTO "
Dim strDealer As String
Dim strDate As String
Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset
Dim rs2 As ADODB.Recordset
Set rs2 = New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim mySplitArray() As String
Dim myText As String
Dim i As Integer
Set cnn = New ADODB.Connection
cnn.CursorLocation = adUseClient
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.path & "\sample.mdb;"
strSQL = "SELECT * FROM [Report_DealerInfo$]"
rs1.Open strSQL, con, adOpenStatic, adLockOptimistic
Do
    If rs1.Fields(0) <> vbNullString Then
        mySplitArray = Split(rs1.Fields(0), ",")
        strSQLSELECT = "SELECT COUNT(*) AS recCount FROM " & strTable1 & " WHERE "
        strSQLSELECT = strSQLSELECT & "AREA = '" & mySplitArray(0) & "' AND "
        strSQLSELECT = strSQLSELECT & "DEPT_CODE = '" & mySplitArray(1) & "' AND "
        strSQLSELECT = strSQLSELECT & "DEALERNAME = '" & mySplitArray(2) & "' AND "
        strSQLSELECT = strSQLSELECT & "DEALER_MSISDN = '" & mySplitArray(3) & "' AND "
        strSQLSELECT = strSQLSELECT & "SUB_MSISDN = '" & mySplitArray(4) & "' AND "
        strSQLSELECT = strSQLSELECT & "STATUS = '" & mySplitArray(5) & "' AND "
        strSQLSELECT = strSQLSELECT & "ACTIVATION_ON ='" & Trim$(mySplitArray(6)) & "'"
        rs2.Open strSQLSELECT, cnn, adOpenStatic, adLockOptimistic
        If rs2![recCount] = 0 Then
            strSQL = " (AREA,DEPT_CODE,DEALERNAME,DEALER_MSISDN,SUB_MSISDN,STATUS,ACTIVATION_ON) VALUES("
            strSQL = strSQL & "'" & mySplitArray(0) & "',"
            strSQL = strSQL & "'" & mySplitArray(1) & "',"
            strSQL = strSQL & "'" & mySplitArray(2) & "',"
            strSQL = strSQL & "'" & mySplitArray(3) & "',"
            strSQL = strSQL & "'" & mySplitArray(4) & "',"
            strSQL = strSQL & "'" & mySplitArray(5) & "',"
            strSQL = strSQL & "'" & Trim$(mySplitArray(6)) & "')"
            cnn.Execute strSQl1 & strTable1 & strSQL
            cnn.Execute strSQl1 & strTable2 & strSQL
        End If
        rs2.Close
    End If
    rs1.MoveNext
Loop Until rs1.EOF
rs1.Close
Set rs1 = Nothing
con.Close
Set con = Nothing
cnn.Close
Set cnn = Nothing
MsgBox "Data transfered"
  'TransferExcelFile2Database.Enabled = False
'errortrap:    MsgBox Err.Description, vbExclamation, "error"
End Sub

---