VBA Syntax issue (I hope)

[Dave]

Access 2003
Excel 2003
Trying to pull in specific field values from Excel into a table in Access.

Copied code form another project we got working before but am clearly
missing something and I believe it is a simple syntax error

to try to keep the amount of code I bore you all with here are the important
lines (I hope)

cmd.CommandText = "Insert into tblCapabilities (EmployeeID) Values
(@EmployeeID)"

cmd.Parameters.Append cmd.CreateParameter("@EmployeeID", adVarChar,
adParamInput, 7)

sht.Cells.Range("A2").Activate
cmd.Parameters("@EmployeeID").Value = xl.ActiveCell

cmd.Execute


Problem is I am not writing to the table
I have cut down my attempt to 1 field until I find the problem
If I step thru it does grab the correct value from Excel
But never writes to the Table.

The field in question is a Number field in both Excel and the Access Table
and is not a primary key (nor does the table currently have a primary key)

I think I have an error in my INSERT line

Any help here will be appreciated.

Thanks in advance

dave

 

[Danny J. Lesandrini]

Dave:

I dopn't know how the line, cmd.Execute, could possibly work based on
what you show below. I think this group needs to be a little "bored" with
the code that sets up the cmd object.

It rather suggests, though, that you're using an ADODB Command object.
Is that because the table is in SQL Server? If not, why not just use DAO
and do an INSERT like this ...


sSQL = "INSERT INTO tblMyTable (EmployeeID) VALUES (" & xl.ActiveCell & ")"
CurrentDb.Execute sSQL

 

[Dave]

Danny thanks for the reply.

The table is in Access 2003 NOT SQL Server
I will try your code - although our code is straight from another access
2003 "working" DB - pruned for the differences.
Somebody here says "there is always more then one way to skin a cat" so I
only want to get it working, do not care if it is the same as the other DB
we got this from.

If that does not work I will post it all (the code)

Thanks very much

Dave

 

[Dave]

Danny - I tried your code.
Works and actually seems to simplify the process.

Here is what I adapted from your post

sht.Cells.Range("A2").Activate
sSQL = "INSERT INTO tblCapabilities
(EmployeeID,Q1Self,Q1Super,Q1SelfComment) VALUES (" & xl.ActiveCell & "," &
xl.ActiveCell.Offset(0, 2) & "," & xl.ActiveCell.Offset(0, 3) & "," &
xl.ActiveCell.Offset(0, 4) & ")"
CurrentDb.Execute sSQL

HOWEVER - it only works with numbers
how can I adapt to also work with text and dates and Memo fields.

Thanks much dave

 

[Danny J. Lesandrini]

Ahh, yes, you need to delimit strings with a single quote, and dates with pound sign.

ssql = "INSERT INTO tblTest(myID, myText, myDate) " & _
"VALUES(" & lngID & ",'" & Replace(strMyText, "'","''") & "',#" & dteMyDate & "#)"

This is difficult to read because single and double quotes munge together, but two things
to notice here:

1) The final output will have single quotes surrounding the text value, like this ...

VALUES (1243, 'this where my text goes', #1/1/2009#)

2) Any embedded single quotes in the string must be doubled, so they don't screw up
the INSERT statement, like this ...

VALUES (1243, 'you shouldn''t have to worry about single quotes', #1/1/2009#)

The Replace call above replaces the single quote of _shouldn't_ with two single quotes.
Play around with that and you'll see what I mean. Print out your SQL Statement to the
debug window and test it in a SQL Window. That will reveal any issues.

 

[Dave]

OK - I got it to work but I do not think I did exactly what you told me.
for the VALUE of the 4th field (the problem text field)
I added single quoits
(EmployeeID,Q1Self,Q1Super,Q1SelfComment) VALUES (" & xl.ActiveCell & "," &
xl.ActiveCell.Offset(0, 2) & "," & xl.ActiveCell.Offset(0, 3) & ",'" &
xl.ActiveCell.Offset(0, 4) & "')"

This gave me the desired INSERT

However you mention embedded single quotes.

These will be in a text field in EXCEL. How do I "CODE" for those if I don't
know when or if they will happen?

ALSO

How do I " Print out your SQL Statement to the

debug window and test it in a SQL Window. "?

Thanks much

dave

 

[Danny J. Lesandrini]

Dave:

The embedded quote thing may never come up, but I wanted to warn you. When I
create a sql insert statement like this, I always use the Replace() method to double
the embedded single quotes. Like this ...

Replace(strMyText, "'","''")

As for sending the sql statement to the debug window, you do that by including this
line in the code ...

Debug.Print sSQL

It shows up in the Immediate window. (Press Ctl+G to see the immediate window)

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

 

[Dave]

Thank you very much. I learned much today

dave

Dave:

The embedded quote thing may never come up, but I wanted to warn you.
When I
create a sql insert statement like this, I always use the Replace() method
to double
the embedded single quotes. Like this ...

Replace(strMyText, "'","''")

As for sending the sql statement to the debug window, you do that by
including this
line in the code ...

Debug.Print sSQL

It shows up in the Immediate window. (Press Ctl+G to see the immediate
window)