Help with Access Database Query!!

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

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

"Vince" wrote:

>
> 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
>
>

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.

"Jim Thomlinson" wrote:

> 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
>
> "Vince" wrote:
>
> >
> > 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
> >
> >

What's the text of the error message?

--
Tim Williams
Palo Alto, CA


"Vince" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> "Jim Thomlinson" wrote:
>
> > 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
> >
> > "Vince" wrote:
> >
> > >
> > > 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
> > >
> > >

Sorry Try this...

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

Needs the Quotes...

"Vince" wrote:

> 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.
>
> "Jim Thomlinson" wrote:
>
> > 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
> >
> > "Vince" wrote:
> >
> > >
> > > 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
> > >
> > >

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...

"Jim Thomlinson" wrote:

> Sorry Try this...
>
> rs.Open "Select *FROM tblDoctorBladesQuery WHERE
> tblDoctorBladesQuery.MachineLocation = """ & cboMachineLocation.Value""" ,
> cn,
> adOpenDynamic, adLockOptimistic, adCmdText.
>
> Needs the Quotes...
>
> "Vince" wrote:
>
> > 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.
> >
> > "Jim Thomlinson" wrote:
> >
> > > 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
> > >
> > > "Vince" wrote:
> > >
> > > >
> > > > 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
> > > >
> > > >

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!

"Jim Thomlinson" wrote:

> 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...
>
> "Jim Thomlinson" wrote:
>
> > Sorry Try this...
> >
> > rs.Open "Select *FROM tblDoctorBladesQuery WHERE
> > tblDoctorBladesQuery.MachineLocation = """ & cboMachineLocation.Value""" ,
> > cn,
> > adOpenDynamic, adLockOptimistic, adCmdText.
> >
> > Needs the Quotes...
> >
> > "Vince" wrote:
> >
> > > 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.
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > 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
> > > >
> > > > "Vince" wrote:
> > > >
> > > > >
> > > > > 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
> > > > >
> > > > >

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.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Vince wrote:
> 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!
>
> "Jim Thomlinson" wrote:
>
>> 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...
>>
>> "Jim Thomlinson" wrote:
>>
>>> Sorry Try this...
>>>
>>> rs.Open "Select *FROM tblDoctorBladesQuery WHERE
>>> tblDoctorBladesQuery.MachineLocation = """ &
>>> cboMachineLocation.Value""" , cn,
>>> adOpenDynamic, adLockOptimistic, adCmdText.
>>>
>>> Needs the Quotes...
>>>
>>> "Vince" wrote:
>>>
>>>> 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.
>>>>
>>>> "Jim Thomlinson" wrote:
>>>>
>>>>> 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
>>>>>
>>>>> "Vince" wrote:
>>>>>
>>>>>>
>>>>>> 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