Saving an Excel Spreadsheet to an Access Table

[AllusiveKitten]

Hi, I really hope you can help... I am currently trying to write a code that will take an excel spreadsheet & save it to an access Table.

I come up with an error "Operation is not allowed when the object is closed." after the insert statement has run.

Sub AccessLoading()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

    DBFullName = "G:\Agent Support Centre\Envelopes\MASTER DATA\PROCESS MANAGER\BACK\Process Manager 2006_be.mdb"
    
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
        DBFullName & ";"

Dim updatesql As String
updatesql = "INSERT INTO Tbl_PartnerNETPayments ( Plant ) VALUES ('" & Range("A1").Value & "');"
rs.Open updatesql, cn, adOpenDynamic, adLockOptimistic

rs.AddNew
rs.Update

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub

I am really sorry if this sounds really vague, but I am only new to programming.

Thank you for your assistance

AK

[Killer42]

I think the AddNew is a mistake. Isn't running the INSERT query in SQL the equivalent of doing an AddNew in code? As such, I wouldn't expect it to actually open a query which could be used to do things like AddNew or Update.

[AllusiveKitten]

Thank you for your help, I have it working now

Sub AccessLoading()
Dim cn As ADODB.Connectio n, rs As ADODB.Recordset , intColIndex As Integer
Set cn = New ADODB.Connectio n
Set rs = New ADODB.Recordset

DBFullName = "G:\Agent Support Centre\Envelope s\MASTER DATA\PROCESS MANAGER\BACK\Pr ocess Manager 2006_be.mdb"

cn.Open "Provider=Micro soft.Jet.OLEDB. 4.0; Data Source=" & _
DBFullName & ";"

Dim updatesql As String
updatesql = "INSERT INTO Tbl_PartnerNETP ayments ( Plant, [Date], DktNumber, AccNumber, AccName, [$Value], PmentType, CCType, CCNumber, CCXpDate, SalePayment, DuplicatePment ) VALUES ('" & Range("A1").Val ue & "','" & Range("B1").Val ue & "','" & Range("C1").Val ue & "','" & Range("D1").Val ue & "','" & Range("E1").Val ue & "','" & CCur(Range("F1" ).Value) & "','" & Range("G1").Val ue & "','" & Range("H1").Val ue & "','" & Range("I1").Val ue & "','" & Range("J1").Val ue & "','" & Range("K1").Val ue & "','" & Range("L1").Val ue & "');"
rs.Open updatesql, cn, adOpenDynamic, adLockOptimisti c

Set rs = Nothing
Set cn = Nothing

End Sub

[Killer42]

Glad to help!

And I've learned a bit in the process. It's a win-win! :)