Excel 2010 VBA to retrieve value from Access 2010

[grego9]

I am writing some Excel VBA to retrive a value from an Access Database called SSI20031.mdb.
In a nutshell I want the code to look at the value in Sheet1 cell F55 of the excel spreadsheet and then find the record that has this value in the "ID" field of query called "Code Generator" of the database. After finding the appropriate record I then want the code to store the value in the "Expr1" field of the database for the selected record and pass this into field G55 of the Excel Sheet1.

I have written the following code:

Sub ReturnCode()


Dim cnnz As ADODB.Connection
Dim rsz As ADODB.Recordset
Dim sSQLz As String, strConnz
Dim Numbeb As Variant



'Q:\IT\Database Masters\Guarantees2.mdb
'

strConnz = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=Q:\IT\Database Masters\SSI20031.mdb;Persist Security Info=False"
Set cnnz = New ADODB.Connection
cnnz.Open strConnz

Set Numbeb = Worksheets("Sheet1").Range("F55").Value

'sSQLz = "SELECT [Code Generator].* FROM [Code Generator] WHERE [Code Generator].ID='" & Range("F55").Value & "';"
sSQLz = "SELECT [Code Generator].* FROM [Code Generator] WHERE [Code Generator].ID=Numbeb;"
Set rsz = New ADODB.Recordset
rsz.Open sSQLz, cnnz, adOpenStatic, adLockOptimistic
If Not IsNull(rsz.Fields("ID").Value) Then
Range("G55") = rsz.Fields("Expr1").Value
End If

rsz.Close
Set rsz = Nothing

cnnz.Close
Set cnnz = Nothing

End Sub


I keep getting a type mismatch message on line
"Set Numbeb = Worksheets("Sheet1").Range("F55").Value"

Field F55 only carries numbers e.g. 1680 - so to me I am not sure why this is an issue. The ID field in the database is using autonumber - so that should be a number also. Any ideas on what I need to do to fix this?

thanks

 

[pr2-eugin]

Just use

Numbeb = Worksheet("Sheet1").Range("F55").Value

Set Keyword is used only for Objects..

 

[namliam]

You use SET only for objects not variables, remove the SET from the line and you should be good.

*** Waves fist @ pr2 ***

 

[grego9]

Thanks - I've removed the SET part and I now get "No Value Given for One or More Paramaters" on line "rsz.Open sSQLz, cnnz, adOpenStatic, adLockOptimistic"
There is definitely a value in F55 of the Spreadsheet - Confused! Any help greatly appreciated!

 

[pr2-eugin]

Where is the ReturnCode run from? Excel? Check out : http://www.connectionstrings.com/excel/

 

[namliam]

Because.....
sSQLz = "SELECT [Code Generator].* FROM [Code Generator] WHERE [Code Generator].ID=Numbeb;"
You are hardcoding the variable instead of replacing it in.... while you obviously know how to do it....
'sSQLz = "SELECT [Code Generator].* FROM [Code Generator] WHERE [Code Generator].ID='" & Range("F55").Value & "';"

 

[grego9]

Thanks for your help on this.

the Return code code is run from excel yes.

Namliam - I copied the code from another process that I created and that works fine
If I replace the
sSQLz = "SELECT [Code Generator].* FROM [Code Generator] WHERE [Code Generator].ID=Numbeb;"
with

sSQLz = "SELECT [Code Generator].* FROM [Code Generator] WHERE [Code Generator].ID='" & Range("F55").Value & "';"
I get the data type mismatch again! Confused!

Thanks for sticking with me on this!

 

[pr2-eugin]

The ID is number.. So remove the single quotes..

sSQLz = "SELECT [Code Generator].* FROM [Code Generator] WHERE [Code Generator].ID = " & Range("F55").Value

 

[namliam]

Your id is a.... *guessing here* number, so why are you trying to search for a text by using the ' around your field?
For number fields

sSQLz = "SELECT [Code Generator].* FROM [Code Generator] WHERE [Code Generator].ID=" & Numbeb &";"

Text:

sSQLz = "SELECT [Code Generator].* FROM [Code Generator] WHERE [Code Generator].ID=""" & Numbeb &""";"

Or as you have it

sSQLz = "SELECT [Code Generator].* FROM [Code Generator] WHERE [Code Generator].ID='" & Numbeb &"';"

Both work...

and for dates:

sSQLz = "SELECT [Code Generator].* FROM [Code Generator] WHERE [Code Generator].ID=#" & Numbeb &"#;"

Remember the date contained in Numbeb MUST be in US format, MM/DD/YYYY

Edit 1: please use the [cade] and [/cade] tags to make the code look "pretty" (replace a by o offcourse)

Edit 2: *SIGH* Pr2

 

[grego9]

Thank you so much! Problem solved! I could have spent hours trying to spot that!

 

[pr2-eugin]

Edit 2: *SIGH* Pr2

Ha Ha Ha !

Thank you so much! Problem solved! I could have spent hours trying to spot that!

You're welcome ! Good Luck !