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