Still need help with Access DB Query!!

[Guest]

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!

 

[Tom Ogilvy]

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

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!

cboMachineLocation.Value",cn,adOpenDynamic,adLockOptimistic,adCmdText

 

[Guest]

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.

 

[Jamie Collins]

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.

--