Solved: Inserting data from Excel into Access

[Agent mMm]

For reasons too numerous to go into here, I am building an Excel front-end that feeds into an Access back-end database. National network with many front-end data collection points to feed into a single database file.

I have no problem with the connection and can succesfully insert a new record if I directly inserting values e.g.

objCommand.CommandText = "INSERT INTO tblHoldingArea" & "([Asset name])" & "VALUES ('Dave');"

However, what I need is for the VALUES to be taken from a specific range of cells on the worksheet. I tried the following line, which does pick up the data from the specified range, however a syntax error is thrown up.

objCommnad.CommandText = "INSERT INTO tblHoldingArea" & "([Asset name])" & VALUES ("& arange &");"

The syntax error is:

Syntax error (missing operator) in query expression 'Test Data'.

'Test Data' being the contents of the cell referenced by arange.

Grateful for thoughts on this. Many thanks.

[Bob Phillips]

Difficult to be sure as you don't give enough detail, but maybe



objCommnad.CommandText = "INSERT INTO tblHoldingArea" & "([Asset name])" & VALUES (" & Range("arange").Value & ");"

[Agent mMm]

Hmm...thanks. Tried your code, but now I get the following critical error msg:

Method 'Range' of object '_Global' failed

when I run the procedure.

You mentioned a lack of detail, xld - what else would you like to know?

[Bob Phillips]

I was really referring to what that variable arange is, how it is setup.

[Agent mMm]

Aah...righty ho.

DIM arange as Range

Set arange = Worksheets("Inventory").Range("C8")

Once I manage to get the test cell transferred (C8) I'll be increaseing the range to include all cells required.

[Bob Phillips]

Then this should (?) work



objCommnad.CommandText = "INSERT INTO tblHoldingArea ([Asset name]) VALUES (" & arange.Value & ");"

[Agent mMm]

Nope - back to the syntax error. How frustrating is this?

[Bob Phillips]

Is it possible for you to post the workbook and the database so I can see exactly what is happening?

[slamet Harto]

how about :
objCommnad.CommandText = "INSERT INTO tblHoldingArea" & "([Asset name])" & VALUES ('"& arange &");"

just look at a piece of my code:
msql = "insert into tbreason(RegNo,CompName,CallStatus,CallReason,CycleID,ApptDate,Remarks,User ID,TrackingDate,t_date,DateWon)" & _
"values('" & Trim(TxtRegNo.Text) & "','" & Trim(TxtCompName.Text) & "','" & Trim(CboCall.Text) & "','" & Trim(CboReason.Text) & "'" & _
",'" & Trim(CboCycle.Text) & "','" & TxtApptdate.Text & "','" & Trim(TxtRemarks.Text) & "','" & Sheets("EntryMenu").Range("F1").Value & "','" & Now() & "','" & Format(Date, "yyyymmdd") & "','" & TglWonOrNot & "')"
Cn.Execute (msql)

[Bob Phillips]

Good thinking slamet, you just missed a quote

objCommnad.CommandText = "INSERT INTO tblHoldingArea ([Asset name]) VALUES ('" & arange.Value & "');"

[Agent mMm]

That worked a treat - the misisng quotes! DOH! MAny thanks for your assistance.