Need help importing excel file to Access db table

[anoble1]

I have been reading some articles on the site and I can't really find out how to make them work with what I have. I need some help.

Here is what I have -
It keeps failing at the Records.EDIT
What am I missing?

Dim appExcel As Object
    Dim workBook As Object
    Dim workSheet As Object
    Dim i As Integer
    Dim FdrID As Integer
    Dim dbs_curr As Database
    Dim records As Recordset
    Dim sqlStatement As String
    Dim Message As String
    Set dbs_curr = CurrentDb
    Dim returnValue As Integer


Select Case returnValue
    Case vbCancel:
    Exit Function
    Case vbNo
    Application.FollowHyperlink "http://powerdelivery.southernco.com/apc/distribution/eng-svcs/SGIG/SGIG.html", , True, True
    Exit Function
    Case vbYes
    
    End Select
        
    
    
    DoCmd.OpenForm "frmRunningQuery"
    Forms!frmRunningQuery.Repaint

'Delete all existing CSS informatin
    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE tblTest SET tblTest.feederID = Null, tblTest.Description = Null, tblTest.CSSCustomers = Null, tblTest.CSSRevenue = Null, tblTest.ADDSCircuitMiles = Null, tblTest.SAIFI = Null, tblTest.SAIDI = Null, tblTest.MAIFI = Null, tblTest.OpCenter = Null, tblTest.Region = Null, tblTest.SubstationUnit = Null, tblTest.NextDate = Null, tblTest.CompleteDate = Null, tblTest.StartDate = Null, tblTest.ReviewDate = Null, tblTest.ReviewedDate = Null, tblTest.TotalPoles = Null;"
    DoCmd.SetWarnings True
    
    
    ' Open an existing spreadsheet
    Set appExcel = GetObject("C:\CSS.xls")
    
   
    Set workSheet = appExcel.Worksheets(1)

sqlStatement = "SELECT tblTest.* FROM tblTest"
            Set records = dbs_curr.OpenRecordset(sqlStatement, dbOpenDynaset, dbSeeChanges, dbOptimistic)

records.Edit
                records!feederID = workSheet.Cells.Range("B" & i & ":B" & i).Value
                records!Description = workSheet.Cells.Range("C" & i & ":C" & i).Value
                records!CSSCustomers = workSheet.Cells.Range("D" & i & ":D" & i).Value
                records!CSSRevenue = workSheet.Cells.Range("E" & i & ":E" & i).Value
                records!ADDSCircuitMiles = workSheet.Cells.Range("F" & i & ":F" & i).Value
                records!SAIFI = workSheet.Cells.Range("G" & i & ":G" & i).Value
                records!SAIDI = workSheet.Cells.Range("H" & i & ":H" & i).Value
                records!MAIFI = workSheet.Cells.Range("I" & i & ":I" & i).Value
                records!OpCenter = workSheet.Cells.Range("K" & i & ":K" & i).Value
                records!Region = workSheet.Cells.Range("M" & i & ":M" & i).Value
                records!SubstationUnit = workSheet.Cells.Range("P" & i & ":P" & i).Value
                records!NextDate = workSheet.Cells.Range("R" & i & ":R" & i).Value
                records!CompleteDate = workSheet.Cells.Range("S" & i & ":S" & i).Value
                records!StartDate = workSheet.Cells.Range("T" & i & ":T" & i).Value
                records!ReviewDate = workSheet.Cells.Range("U" & i & ":U" & i).Value
                records!ReviewedDate = workSheet.Cells.Range("V" & i & ":V" & i).Value
                records!TotalPoles = workSheet.Cells.Range("W" & i & ":W" & i).Value
                records.Update

' Release objects
    Set workSheet = Nothing
    Set workBook = Nothing
    Set appExcel = Nothing
    
    DoCmd.Close acForm, "frmRunningQuery"
    MsgBox "Done"
    
End Function

[Seth Schrock]

What error are you getting (number and exact message)?

[anoble1]

Run-time error '3021':
No Current record


it does that at the

records.Edit

Doesn't make it anywhere

[jimatqsi]

Your table must be empty, the recordset returned by the Select is returning an empty recordset. You have to check for that before doing the .Edit. Change records.Edit to the following:

if not records.eof then
    records.Edit
else
    records.AddNew
end if

Jim

[jimatqsi]

Also when you are releasing objects you should have:

records.close
set records = nothing

[zmbd]

Line 31, that's one way...

'Replace line 31
zjsql = "DELETE * " _
& "FROM tbl_nametoclear;"
db.Execute zjsql, dbFailOnError

Also, it is, IMHO, better to build your string first, then use it in the commands. This way, if something wierd happens, or doesn't happen, all one need do is place a debug.pring strSQL after the variable to see what is actually being resolved in the string. However, when building the string within the command, as you have in your code, there is no easy way to determine what is happening with it.

Line 44 WILL throw this error because in Line 31 you just cleared this table; therefore, there is nothing to edit. You will need to add a new record as jimatqsi indicated.

Next what is up with all of these "i" in your ranges such as:
workSheet.Cells .Range("B" & i & ":B" & i).Value
You are not in a loop nor have you defined the value for "i" that I can find so i=0; therefore, the above string resolves to:workSheet.Cells .Range("B0:B0") .Value

You really need to look at the transferworkshe et method. Pull your worksheet into a temporary table and then parse that table as needed to validate datatypes and entries.
Here are three threads that have information and links covering different uses for this method:
>>updating-access-using-excel-spreadsheets-used-distribute-access-info
>> how-export-data-form-datasheet-excel-via-code
>>import-excel-cells-table-but-field-names-different-both

finally as jimatqsi pointed out, you need to close the record set first before setting it to nothing.

You also need to release the Execl objects.