Append rows from Excel

[herbwarri0r]

Hi all,

Really sorry if this has been aswered already. I have tried searching this forum and Google but haven't found anything thus far. Possibly because there are better ways of doing this.

I have an Excel workbook with 9 fields. The entered data begins on row 7, I want some code that when a command button is clicked it appends the data to an exisiting Access table.

This is how far I have got, I was going to add a loop when I can get the first row to append but I realise I have a syntax error with the SQL at the mo.

Sub Transfer()
Dim db As DAO.Database
Dim qry As DAO.Querydef
Dim sqltxt As String
Dim Row As Integer

Set db = OpenDatabase("H:\Finance\Billing\BAL\Loader.mdb")

Dim AccNum As String
Dim AdjType As String
Dim CPS As String
Dim AdjDesc As String
Dim Amnt As String
Dim cn As String
Dim dispute As String
Dim C2 As String
Dim Auth As String

Row = 7

AccNum = Cells(Row, 1).Value
AdjType = Cells(Row, 2).Value
CPS = Cells(Row, 3).Value
AdjDesc = Cells(Row, 4).Value
Amnt = Cells(Row, 5).Value
cn = Cells(Row, 6).Value
dispute = Cells(Row, 7).Value
C2 = Cells(Row, 8).Value
Auth = Cells(Row, 9).Value

db.OpenRecordset(INSERT INTO BAL VALUES, AccNum, AdjType, CPS, AdjDesc, Amnt, cn, dispute, cn, Auth)

Set db = Nothing
End Sub

Or would it be better to copy the current region from the worksheet to a temporary sheet and then use Docmd.transferspreadsheet?

 

[ecawilkinson]

change the db.openrecordset to:

db.execute "INSERT INTO BAL (explicitly name fields here) VALUES (AccNum, AdjType, CPS, AdjDesc, Amnt, cn, dispute, cn, auth)"

and loop though the rows - is one solution

HTH,
Chris

 

[herbwarri0r]

Still need some help I'm afriad

I was getting a syntax error, till I realised I had my variables within quotes. Now the error I'm getting is:

Error 3078 : The Microsoft Jet database engine cannot find the input table or query "INSERT INTO BAL...

Just to be clear, this code is running from Excel. I have run compact and repair and tried making it a secured database.