exporting from excel to Access

[Guest]

Hello,

I am trying to export values from cells in excel to updates fields in
selected records in access. The code I have so far is this.

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strConnect As String
Dim strSQL As String
Dim strJobNo As String

strJobNo = Cells(2, 2)
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\db\temp_db.mdb;"
strSQL = "SELECT tblWorkInProgress.wpJobNumber, tblWorkInProgress.wpCost
FROM tblWorkInProgress WHERE (((tblWorkInProgress.wpJobNumber) Like
'strJobNo')); "

Set cnn = New ADODB.Connection
cnn.Open strConnect
Set rs = New ADODB.Recordset
rs.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
rs!wpCost = Cells(2, 3)
rs.Update
rs.Close
cnn.Close

This does not work, it does not like the variable strJobNo in the select
statement. If you replace the strJobNo with an actual job number eg 1223 the
select statment works and it will update wpCost with the value at Cells(2,3).
What do I need to change to enable this Select statement to used the
variable strJobNo.

Thanks In advance

Matt

 

[Brendan Reynolds]

You need to get the contents of the variable, rather than its name, into the
SQL string ...

.... WHERE (((tblWorkInProgress.wpJobNumber) = " & strJobNo & "));"

If wpJobNumber is a text rather than a numeric field, you'll need quotes
around the value ...

.... WHERE (((tblWorkInProgress.wpJobNumber) = '" & strJobNo & "'));"

That's a single quote followed by a double quote after the first ampersand,
and a double quote followed by a single quote after the second ampersand.

 

[Guest]

Thanks thats done the trick

You need to get the contents of the variable, rather than its name, into the
SQL string ...

.... WHERE (((tblWorkInProgress.wpJobNumber) = " & strJobNo & "));"

If wpJobNumber is a text rather than a numeric field, you'll need quotes
around the value ...

.... WHERE (((tblWorkInProgress.wpJobNumber) = '" & strJobNo & "'));"

That's a single quote followed by a double quote after the first ampersand,
and a double quote followed by a single quote after the second ampersand.