| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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,adLockOpti mistic 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,adLockO ptimistic,adCmdText |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Access Database Query!!
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,adLockOpti mistic 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,adLockO ptimistic,adCmdText |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Access Database Query!!
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,adLockOpti mistic 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,adLockO ptimistic,adCmdText |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Access Database Query!!
What's the text of the error message?
-- Tim Williams Palo Alto, CA "Vince" wrote in message ... 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,adLockOpti mistic 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,adLockO ptimistic,adCmdText |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Access Database Query!!
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,adLockOpti mistic 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,adLockO ptimistic,adCmdText |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Access Database Query!!
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,adLockOpti mistic 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,adLockO ptimistic,adCmdText |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Access Database Query!!
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,adLockOpti mistic 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,adLockO ptimistic,adCmdText |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with Access Database Query!!
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. -- Dick Kusleika Excel MVP Daily Dose of Excel www.dicks-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,adLockOpti mistic 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,adLockO ptimistic,adCmdText |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel query to access database | Excel Discussion (Misc queries) | |||
| Query of an Access database won't let me get more than 95 fields. | Excel Discussion (Misc queries) | |||
| Database query to import from Access to Excel | Excel Discussion (Misc queries) | |||
| How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
| How to do an Access Database Query in Excel | Excel Programming | |||
Linear Mode
