Hi xCav8r, this is beginner and thanks for your info. & help. however, i find it ADO seems a bit more difficult to understand than DAO and can I use DAO sample to ask your help again.....

Below got the coding sample for my headache.....what i want to do in details are :-

1. under Access Form, click a Product_Search button and run the VBA when "on_click"
2. then, the VBA will first clear the records in my current database (ABC.mdb)'s table "ABC_level_1" and open a recordset.
3. then, it also open the other Excel file (Excel.xls) in the same path as the ABC.mdb and located the WorkSheet(1)
4. Then it start to retrieve data from the Excel cells value to access under some criterias ( lines start with "i=4" )

However, the VBA got error and stop at the line "Set xlApp = ......" and i also find some error at the line "Set mySht = .....(either options also got errors)", so I cannot achieve the above step 3. ie. I cannot locate the Excel.xls to access the Worksheet(1) cell values.

I checked the line "myDb.Execute "DELETE * FROM " & myTblName" is working ok as the table was being empty and those data retrieve lines after "i=4" should be ok as i tried run them inside Excel macro. (although the VBA still not yet reach this lines)


Would you help to study what should be the correct coding to open the Excel file and locating the required worksheets or some other coding not correct ??
Many many thanks....!!!



Private Sub Product_Search_Click()
    Dim xlApp As Excel.Application
    Dim filename_excel As String
    Dim myDb As DAO.Database
    Dim myRst As DAO.Recordset
    Dim myFileName As String
    Dim myTblName As String
    Dim myKey As String
    Dim mySht As Worksheet
    Dim i As Long
    Dim j As Long
    myFileName = "ABC.mdb"
    myTblName = "ABC_level_1"
    Set myDb = CurrentDb
    myDb.Execute "DELETE * FROM " & myTblName
    Set myRst = myDb.OpenRecordset(myTblName)
    filename_excel = "Excel.xls"
    Set xlApp = GetObject(ThisWorkbook.Path & "\" & filename_excel, "Excel.Application")
    ' Set mySht = xlApp.Workbooks.OpenDatabase(filename_excel).Worksheets(1)
    Set mySht = xlApp.Worksheets(1)
    i = 4
    With myRst
        .Index = "PrimaryKey"
        For i = 4 To mySht.Range("A100").Row
        If Trim(mySht.Cells(i, 23).Text) = "142" Then Exit For
        If mySht.Cells(i, 4 + 19).Value <> "" Then
            .AddNew
            myRst(1).Value = mySht.Cells(2, 10).Value
            myRst(2).Value = mySht.Cells(2, 30).Value
            For j = 4 To .Fields.Count - 1
                myRst(j - 1).Value = mySht.Cells(i, j + 19).Value
            Next
            If mySht.Cells(i, .Fields.Count + 19).Value = "" Then
               myRst(.Fields.Count - 1).Value = "N"
            Else
               myRst(.Fields.Count - 1).Value = "Y"
           End If
          .Update
       Else
       End If
    Next
    .Close
    End With
    myDb.Close
    Set myRst = Nothing
    Set myDb = Nothing
End Sub