Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 23rd, 2003, 12:10 PM
Registered User
Join Date: Jul 2003
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default ADO Dynamic Query - Please help


I've been experiencing some problems executing a query in access using ADO.
Here is the static part of the query:

' sPharmSrchSql = "SELECT t_Pharm.[Pharmacist_Name],
t_Pharm.[Pharmacy_Location] , "
    'sPharmSrchSql = sPharmSrchSql & "t_Pharm.[Pharmacy_Phone],
t_Pharm.[Sales_Area] , "
    'sPharmSrchSql = sPharmSrchSql & "t_Pharm.[Shop_Windows],
    'sPharmSrchSql = sPharmSrchSql & "t_Pharm.[UMSATZ_VJ] ,
t_Pharm.[Cust_Main_Id] " & vbCrLf
    'sPharmSrchSql = sPharmSrchSql & "FROM t_LocatorIJ, t_SiteVisit,
t_Pharm, Assign_Keys " & vbCrLf
    'sPharmSrchSql = sPharmSrchSql & "WHERE "
    'sPharmSrchSql = sPharmSrchSql & "t_LocatorIJ.[Cust_Main_Id] =
t_SiteVisit.[Cust_Main_Id] " & vbCrLf
    'sPharmSrchSql = sPharmSrchSql & "AND t_SiteVisit.[Cust_Main_Id] =
t_Pharm.[CUST_MAIN_ID] " & vbCrLf
    'sPharmSrchSql = AND t_Pharm.[CUST_MAIN_ID] = Assign_Keys.[CUST_MAIN_ID]
" & vbCrLf

The static part returns all the records as you would expect.

My problem is that whenever I add some criteria to reduce the list(depending
on what the user enters), e.g., t_Pharm.[Pharmacist_Name] Like 'dr.*', I get
an empty resultset. I know that there are records that match the criteria.

Another thing I did was I copied and pasted the query into acess (include
the criteria I previously mentioned). The query run ok, which means that the
problem is probably with ADO.

I open my query withthe followign command: rstSrch.Open sPharmSrchSql, con,
adOpenForwardOnly, adLockOptimistic

Any help would be very much appreciated.


Old July 23rd, 2003, 06:50 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts

Just having a quick look at that query, apart from the fact that each line has been commented out, which I assume is just so that your procedure skipped it - the last line is missing a bit.

You've created this big string, then the last line screws it up - since you've missed a "
so it should be
sPharmSrchSql = sPharmSrchSql & "AND t_Pharm.[CUST_MAIN_ID] = Assign_Keys.[CUST_MAIN_ID]"
Also - you don't really need to have vbCrLf all the time. Whether it's a new line or not makes no difference.

Now that that's dealt with - how are you putting in the criteria?
You said the bit you've posted works fine, so maybe you could post the bit that doesn't work.


There are 10 kinds of people in the world - those who understand binary - and those with friends

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using ADO to Query Excel alex_w Excel VBA 1 October 19th, 2006 04:42 AM
Dynamic generation of ADO recordset Pravin kumar Classic ASP Professional 3 March 12th, 2006 07:50 PM
Using ADO to Query Excel alex_w Access VBA 1 May 18th, 2005 11:59 AM
VB6 ADO Dynamic Data Binding Question pad Pro VB Databases 1 January 28th, 2005 10:43 PM
Delphi ADO Query acabrera Oracle 2 February 2nd, 2004 12:49 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.