Trying to export data from Excel 2010 doc to Access 2010 database (rs.Open failure)


(sorry for bad english)
I get an error code that says (translated from swedish) "Wrong SQL expression, use Delete, Select, Insert...." Error Code -2147217900 (80040e14)

rs.Open i marked

The thing i am trying to doo i to export data from cells in a excel 2010 doc to an access 2010 database. Everything i on my own computer (not trying to do something to a server (yet))

Here´s the code that i wrote so far.

This is an experiment for a small project att work were i want to send data from several users via an excel doc to a database without anyone opening the databse. This is beacuse it's not unusual for idiots to erase the wrong data or add in wrong ways etc.

Here´s the code that i wrote so far.

Sub DataFranProjektVy()

Dim i As Integer

'Dim cn As Connection
Dim cn As Connection

    Set cn = New Connection
    cn.Provider = " Provider=Microsoft.ACE.OLEDB.12.0 "
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Arvid\Desktop\Maskinbutiken\Maskinorder.accdb;Persist Security Info=False;"

Dim rs As Recordset
    Set rs = New Recordset

Dim Tabell As String
Tabell = "Order"

    rs.Open "Tabell", cn, adOpenDynamic, adLockOptimistic
    rs.AddNew 'Ny post skapas i Databasen
    rs.Fields("Affärsnummer") = Range("B2").Value
    rs.Fields("Datum") = Range("B3").Value
    rs.Fields("Modell") = Range("D2").Value
    rs.Fields("Lev från fabrik") = Range("D3").Value
    rs.Fields("Tillbehör 1") = Range("B5").Value
    rs.Fields("Projektnr 1") = Range("B6").Value
    rs.Fields("Rekv Tillbehör 1") = Range("B7").Value
    rs.Fields("Rekv Verkstad") = Range("F2").Value
    rs.Fields("Kommentar") = Range("B9").Value
    Set rs = Nothing
    Set cn = Nothing
    Range("B2").Value = "Skriv värden i respektive ruta!"

End Sub

1 answer

Have a look at: