Help with Access Database Query!!

[Guest]

The code in example #1 works fine, but I am trying to figure out what is
wrong with the code in example #2. I get an error when I try to open the
recordset. I am trying to pass a value from a combo box to the query in
example #2 so the user can select different values. I can't get it to work,
I am looking for help.

example #1

Dim sSql as String
Dim i as Integer
Dim cn as ADODB.Connection, rs as ADODB.Recordset, r AS Long, c ASLong

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.Oledb.4.0; " & _
"Data Source=C:\DoctorBlades.mdb;"

Set rs = New ADODB.Recordset
rs.Open "tblDoctorBladesQuery",cn,adOpenDynamic,adLockOptimistic


example #2

Dim sSql as String
Dim i as Integer
Dim cn as ADODB.Connection, rs as ADODB.Recordset, r AS Long, c ASLong

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.Oledb.4.0; " & _
"Data Source=C:\DoctorBlades.mdb;"

Set rs = New ADODB.Recordset
rs.Open "Select * FROM tblDoctorBladesQuery WHERE
tblDoctorBladesQuery.MachineLocation =
cboMachineLocation.Value",cn,adOpenDynamic,adLockOptimistic,adCmdText

 

[Guest]

tblDoctorBladesQuery.MachineLocation = " &
cboMachineLocation.Value ,cn,adOpenDynamic,adLockOptimistic,adCmdText

Try this. Your cbo value was just a string ("cboMachineLocation.Value"), not
the value form the cbo...

HTH

 

[Guest]

Jim,
I made the change below, I still get the error when the rs.open is executed.
The error I get is -2147217900. I don't know if that error code helps or
not. My code reads as follows:
rs.Open "Select *FROM tblDoctorBladesQuery WHERE
tblDoctorBladesQuery.MachineLocation = " & cboMachineLocation.Value , cn,
adOpenDynamic, adLockOptimistic, adCmdText.

Your help with this is greatly appreciated.

 

[Tim Williams]

What's the text of the error message?

 

[Guest]

Sorry Try this...

rs.Open "Select *FROM tblDoctorBladesQuery WHERE
tblDoctorBladesQuery.MachineLocation = """ & cboMachineLocation.Value""" ,
cn,
adOpenDynamic, adLockOptimistic, adCmdText.

Needs the Quotes...

 

[Guest]

rs.Open "Select *FROM tblDoctorBladesQuery WHERE
tblDoctorBladesQuery.MachineLocation = """ & cboMachineLocation.Value"""" ,
cn,
adOpenDynamic, adLockOptimistic, adCmdText

I just can't get the quotes correct today... The value returnved by
cboMachineLocation.Value must be in Quotes in the select statement... I think
the quotes line up now...

 

[Guest]

Still not working, but different symptoms. If I use the three quotes, I get
a syntax error, but two quotes seems to be ok with syntax. Now when the line
executes I get the following error:

3709 - The connection cannot be used to perform this operation. It is
either closed or invalid in this context.

Again, the code in sample 1 works, but bring back everything. I never
imagined it would be so hard to get something out of Access through Excel!

 

[Dick Kusleika]

Vince

I'm convinced that it's an SQL syntax problem. Use the sSQL variable to
create the statement

sSQL = "Create statement here"
Debug.Pring sSQL
rs.Open ssql, cn, etc...

Look at the SQL that prints in the Immediate Window and you may see what's
wrong. If not, create a new worksheet in your workbook and insert an
external data table (Data - External Data) that executes the query that
you're trying to do through ADO. Hard code the value that would normally
come from the combobox. When you get that QueryTable set up and working, go
back to the Immediate Window and type

?Sheet1.QueryTables(1).CommandText

Where Sheet1 is the sheet you created. Compare the SQL you get from that to
what you're passing to the ADODB.Recordset and you should see the problem.