| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still need help with Access DB Query!!
This is continued from yesterday. I am still at the point of getting the
3709 error. Thought it would be easier to just copy and paste what had been done so far. Still needing help with this one. History is below: 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 brings back everything from the DB. 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 AS Long 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
|
|||
|
|||
|
Still need help with Access DB Query!!
rs.Open "Select *FROM tblDoctorBladesQuery WHERE
tblDoctorBladesQuery.MachineLocation = """ & cboMachineLocation.Value"""" , should be rs.Open "Select * FROM tblDoctorBladesQuery WHERE tblDoctorBladesQuery.MachineLocation = """ & cboMachineLocation.Value & """" , I would think. -- Regards, Tom Ogilvy "Vince" wrote in message ... This is continued from yesterday. I am still at the point of getting the 3709 error. Thought it would be easier to just copy and paste what had been done so far. Still needing help with this one. History is below: 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 brings back everything from the DB. 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 AS Long 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
|
|||
|
|||
|
Still need help with Access DB Query!!
Thanks Tom,
Access queries seem to be extremely finicky with the syntax. The second "&" and the added quotes took care of it. Thanks so much. "Tom Ogilvy" wrote: rs.Open "Select *FROM tblDoctorBladesQuery WHERE tblDoctorBladesQuery.MachineLocation = """ & cboMachineLocation.Value"""" , should be rs.Open "Select * FROM tblDoctorBladesQuery WHERE tblDoctorBladesQuery.MachineLocation = """ & cboMachineLocation.Value & """" , I would think. -- Regards, Tom Ogilvy "Vince" wrote in message ... This is continued from yesterday. I am still at the point of getting the 3709 error. Thought it would be easier to just copy and paste what had been done so far. Still needing help with this one. History is below: 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 brings back everything from the DB. 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 AS Long 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
|
|||
|
|||
|
Still need help with Access DB Query!!
Vince wrote: Access queries seem to be extremely finicky with the syntax. All SQL parsers are <g. You may or may not prefer this approach: "SELECT * FROM tblDoctorBladesQuery" & _ " WHERE tblDoctorBladesQuery.MachineLocation" & _ " = " & Chr$(34) & cboMachineLocation.Value & Chr$(34) Jamie. -- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Query from Access into Excel cause Access to go to read only | Excel Discussion (Misc queries) | |||
| Can I use MS Query in Excel like an Append Query in Access | Excel Discussion (Misc queries) | |||
| Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
| How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) | |||
| Access query vs. Excel query | Excel Programming | |||
Hybrid Mode
