Effectively Filtering Records with Access Front-End

K

Kenneth Courville

My client has a table with over contact 60000 records. We recently upgraded
to a SQL Server 2000 back-end with MS Access 2002 front-end.

When opening the contacts form with all of these records, I can watch the
MSACCESS process climb to 100+ Mb of RAM. Since their PC's only have 128 Mb
of RAM, this is a problem. The PC's involved tend to run slow and/or crash.

I created a separate form to allow them to specify criteria that will be
used by the contact form. This way, only the records they need to work with
at the time are cached from the server, and not near as much RAM is needed.

My first method of doing this was by creating a string using VBA in the
search form (called Contacts-Find). Upon clicking a button, code is
executed to build the filter, the actual Contacts form is opened, and the
form's server filter item is set to this string.

The problem with this method seemed that the filter would sometimes be saved
with the form, and that same search criteria is "stuck" in there when the
next person tries to execute a search.

For my next method, I tried creating a stored procecure, which follows
below. In addition, the Input Parameters option for the Contact form is set
to (not complete text):
"@SearchType=forms![contacts-find]!SearchType,@ContactID=forms![contacts-fin
d]!ContactID,@StoreNum=forms![contacts-find]!StoreNum,@CompanyName=forms![co
ntacts-find]!CompanyName,@FirstName=forms![contacts-find]!FirstName,@LastNam
e=forms![contacts-find]!LastName,@Address=forms![contacts-find]!Address,@Cit
y=forms![contacts-find]!City..."

Basically, the parameter @SearchType is used to determine whether to perform
and "OR" query or an "AND" query. The problem now is that we cannot
effectively perform an "AND" query. This occurs because the client may not
necessary provide data for all of the input parameters below, which results
in search for records that have to have blank or 0 in the field.

My question is... is there a better way to do this, or is there a way to fix
my methods listed above?


ALTER PROCEDURE dbo.spFilterContactsTest
(@SearchType char(3),
@ContactID int,
@StoreNum float,
@CompanyName nvarchar(50),
@FirstName nvarchar(20),
@LastName nvarchar(25),
@Address nvarchar(40),
@City nvarchar(25),
@StateOrProvince nvarchar(2),
@PostalCode nvarchar(10),
@EmailName nvarchar(50),
@WorkPhone nvarchar(14),
@FaxNumber nvarchar(14),
@KeyAcctMgr nvarchar(25),
@StatusTypeID int)
AS

IF @CompanyName='' SET @CompanyName='%'
IF @FirstName ='' SET @FirstName ='%'
IF @LastName ='' SET @LastName ='%'
IF @Address ='' SET @Address ='%'
IF @City ='' SET @City ='%'
IF @StateOrProvince ='' SET @StateOrProvince ='%'
IF @PostalCode ='' SET @PostalCode ='%'
IF @EmailName ='' SET @EmailName ='%'
IF @WorkPhone ='' SET @WorkPhone ='%'
IF @FaxNumber ='' SET @FaxNumber ='%'
IF @WorkPhone ='' SET @WorkPhone ='%'
IF @KeyAcctMgr='' SET @KeyAcctMgr='%'

IF @SearchType='OR' -- perform an "OR" search
BEGIN
SELECT ContactID, StoreNum, CompanyName, BuildingName, FirstName, LastName,
Address, City, StateOrProvince, PostalCode, EmailName, WorkPhone,
WorkExtension, FaxNumber, MobilePhone, ReferredBy, Notes, GEOMarket, Area,
District, CVSDistrict, ContactTypeID, StatusTypeID,
NumberOfEmployees, FluClinicMailing, KeyAcctMgr, AcctMaintRep,
SecondaryContact, SecondaryContactTitle, SecondaryPhone, SecondaryEmail,
ClinicCompetition, Flag, Title
FROM dbo.Contacts
WHERE (ContactID = @ContactID) OR
(StoreNum = @StoreNum) OR
(CompanyName LIKE @CompanyName) OR
(FirstName LIKE @FirstName) OR
(LastName LIKE @LastName) OR
(Address LIKE @Address) OR
(City LIKE @City) OR
(StateOrProvince LIKE @StateOrProvince) OR
(PostalCode LIKE @PostalCode) OR
(EmailName LIKE @EmailName) OR
(WorkPhone LIKE @WorkPhone) OR
(FaxNumber LIKE @FaxNumber) OR
(KeyAcctMgr LIKE @KeyAcctMgr) OR
(StatusTypeID = @StatusTypeID)
END

ELSE -- Perform an "AND" search
BEGIN
SELECT ContactID, StoreNum, CompanyName, BuildingName, FirstName, LastName,
Address, City, StateOrProvince, PostalCode, EmailName, WorkPhone, WorkExtens
ion, FaxNumber, MobilePhone, ReferredBy, Notes, GEOMarket, Area, District,
CVSDistrict, ContactTypeID, StatusTypeID,
NumberOfEmployees, FluClinicMailing, KeyAcctMgr, AcctMaintRep,
SecondaryContact, SecondaryContactTitle, SecondaryPhone, SecondaryEmail,
ClinicCompetition, Flag, Title
FROM dbo.Contacts
WHERE (ContactID = @ContactID) AND
(StoreNum = @StoreNum) AND
(CompanyName LIKE @CompanyName) AND
(FirstName LIKE @FirstName) AND
(LastName LIKE @LastName) AND
(Address LIKE @Address) AND
(City LIKE @City) AND
(StateOrProvince LIKE @StateOrProvince) AND
(PostalCode LIKE @PostalCode) AND
(EmailName LIKE @EmailName) AND
(WorkPhone LIKE @WorkPhone) AND
(FaxNumber LIKE @FaxNumber) AND
(KeyAcctMgr LIKE @KeyAcctMgr) AND
(StatusTypeID = @StatusTypeID)
END
 
A

Andy Williams

The server filter getting 'stuck' could be avoided by
giving users an ADE file. This way no modifications can
be made to your forms.

I'll let someone else address the stored procedure topic.

-----Original Message-----
My client has a table with over contact 60000 records. We recently upgraded
to a SQL Server 2000 back-end with MS Access 2002 front- end.

When opening the contacts form with all of these records, I can watch the
MSACCESS process climb to 100+ Mb of RAM. Since their PC's only have 128 Mb
of RAM, this is a problem. The PC's involved tend to run slow and/or crash.

I created a separate form to allow them to specify criteria that will be
used by the contact form. This way, only the records they need to work with
at the time are cached from the server, and not near as much RAM is needed.

My first method of doing this was by creating a string using VBA in the
search form (called Contacts-Find). Upon clicking a button, code is
executed to build the filter, the actual Contacts form is opened, and the
form's server filter item is set to this string.

The problem with this method seemed that the filter would sometimes be saved
with the form, and that same search criteria is "stuck" in there when the
next person tries to execute a search.

For my next method, I tried creating a stored procecure, which follows
below. In addition, the Input Parameters option for the Contact form is set
to (not complete text):
"@SearchType=forms![contacts-find]! SearchType,@ContactID=forms![contacts-fin
d]!ContactID,@StoreNum=forms![contacts-find]!
Click to expand...
StoreNum,@CompanyName=forms![co
ntacts-find]!CompanyName,@FirstName=forms![contacts-find]! FirstName,@LastNam
e=forms![contacts-find]!LastName,@Address=forms![contacts-
Click to expand...
find]!Address,@Cit
y=forms![contacts-find]!City..."

Basically, the parameter @SearchType is used to determine whether to perform
and "OR" query or an "AND" query. The problem now is that we cannot
effectively perform an "AND" query. This occurs because the client may not
necessary provide data for all of the input parameters below, which results
in search for records that have to have blank or 0 in the field.

My question is... is there a better way to do this, or is there a way to fix
my methods listed above?


ALTER PROCEDURE dbo.spFilterContactsTest
(@SearchType char(3),
@ContactID int,
@StoreNum float,
@CompanyName nvarchar(50),
@FirstName nvarchar(20),
@LastName nvarchar(25),
@Address nvarchar(40),
@City nvarchar(25),
@StateOrProvince nvarchar(2),
@PostalCode nvarchar(10),
@EmailName nvarchar(50),
@WorkPhone nvarchar(14),
@FaxNumber nvarchar(14),
@KeyAcctMgr nvarchar(25),
@StatusTypeID int)
AS

IF @CompanyName='' SET @CompanyName='%'
IF @FirstName ='' SET @FirstName ='%'
IF @LastName ='' SET @LastName ='%'
IF @Address ='' SET @Address ='%'
IF @City ='' SET @City ='%'
IF @StateOrProvince ='' SET @StateOrProvince ='%'
IF @PostalCode ='' SET @PostalCode ='%'
IF @EmailName ='' SET @EmailName ='%'
IF @WorkPhone ='' SET @WorkPhone ='%'
IF @FaxNumber ='' SET @FaxNumber ='%'
IF @WorkPhone ='' SET @WorkPhone ='%'
IF @KeyAcctMgr='' SET @KeyAcctMgr='%'

IF @SearchType='OR' -- perform an "OR" search
BEGIN
SELECT ContactID, StoreNum, CompanyName, BuildingName, FirstName, LastName,
Address, City, StateOrProvince, PostalCode, EmailName, WorkPhone,
WorkExtension, FaxNumber, MobilePhone, ReferredBy, Notes, GEOMarket, Area,
District, CVSDistrict, ContactTypeID, StatusTypeID,
NumberOfEmployees, FluClinicMailing, KeyAcctMgr, AcctMaintRep,
SecondaryContact, SecondaryContactTitle, SecondaryPhone, SecondaryEmail,
ClinicCompetition, Flag, Title
FROM dbo.Contacts
WHERE (ContactID = @ContactID) OR
(StoreNum = @StoreNum) OR
(CompanyName LIKE @CompanyName) OR
(FirstName LIKE @FirstName) OR
(LastName LIKE @LastName) OR
(Address LIKE @Address) OR
(City LIKE @City) OR
(StateOrProvince LIKE @StateOrProvince) OR
(PostalCode LIKE @PostalCode) OR
(EmailName LIKE @EmailName) OR
(WorkPhone LIKE @WorkPhone) OR
(FaxNumber LIKE @FaxNumber) OR
(KeyAcctMgr LIKE @KeyAcctMgr) OR
(StatusTypeID = @StatusTypeID)
END

ELSE -- Perform an "AND" search
BEGIN
SELECT ContactID, StoreNum, CompanyName, BuildingName, FirstName, LastName,
Address, City, StateOrProvince, PostalCode, EmailName, WorkPhone, WorkExtens
ion, FaxNumber, MobilePhone, ReferredBy, Notes, GEOMarket, Area, District,
CVSDistrict, ContactTypeID, StatusTypeID,
NumberOfEmployees, FluClinicMailing, KeyAcctMgr, AcctMaintRep,
SecondaryContact, SecondaryContactTitle, SecondaryPhone, SecondaryEmail,
ClinicCompetition, Flag, Title
FROM dbo.Contacts
WHERE (ContactID = @ContactID) AND
(StoreNum = @StoreNum) AND
(CompanyName LIKE @CompanyName) AND
(FirstName LIKE @FirstName) AND
(LastName LIKE @LastName) AND
(Address LIKE @Address) AND
(City LIKE @City) AND
(StateOrProvince LIKE @StateOrProvince) AND
(PostalCode LIKE @PostalCode) AND
(EmailName LIKE @EmailName) AND
(WorkPhone LIKE @WorkPhone) AND
(FaxNumber LIKE @FaxNumber) AND
(KeyAcctMgr LIKE @KeyAcctMgr) AND
(StatusTypeID = @StatusTypeID)
END


.
Click to expand...
 
J

J. Clay

Question -- Is this form in a datasheet view or is it a single record view?




Kenneth Courville said:
My client has a table with over contact 60000 records. We recently upgraded
to a SQL Server 2000 back-end with MS Access 2002 front-end.

When opening the contacts form with all of these records, I can watch the
MSACCESS process climb to 100+ Mb of RAM. Since their PC's only have 128 Mb
of RAM, this is a problem. The PC's involved tend to run slow and/or crash.

I created a separate form to allow them to specify criteria that will be
used by the contact form. This way, only the records they need to work with
at the time are cached from the server, and not near as much RAM is needed.

My first method of doing this was by creating a string using VBA in the
search form (called Contacts-Find). Upon clicking a button, code is
executed to build the filter, the actual Contacts form is opened, and the
form's server filter item is set to this string.

The problem with this method seemed that the filter would sometimes be saved
with the form, and that same search criteria is "stuck" in there when the
next person tries to execute a search.

For my next method, I tried creating a stored procecure, which follows
below. In addition, the Input Parameters option for the Contact form is set
to (not complete text):
"@SearchType=forms![contacts-find]!SearchType,@ContactID=forms![contacts-find]!ContactID,@StoreNum=forms![contacts-find]!StoreNum,@CompanyName=forms![contacts-find]!CompanyName,@FirstName=forms![contacts-find]!FirstName,@LastName=forms![contacts-find]!LastName,@Address=forms![contacts-find]!Address,@Cit
y=forms![contacts-find]!City..."

Basically, the parameter @SearchType is used to determine whether to perform
and "OR" query or an "AND" query. The problem now is that we cannot
effectively perform an "AND" query. This occurs because the client may not
necessary provide data for all of the input parameters below, which results
in search for records that have to have blank or 0 in the field.

My question is... is there a better way to do this, or is there a way to fix
my methods listed above?


ALTER PROCEDURE dbo.spFilterContactsTest
(@SearchType char(3),
@ContactID int,
@StoreNum float,
@CompanyName nvarchar(50),
@FirstName nvarchar(20),
@LastName nvarchar(25),
@Address nvarchar(40),
@City nvarchar(25),
@StateOrProvince nvarchar(2),
@PostalCode nvarchar(10),
@EmailName nvarchar(50),
@WorkPhone nvarchar(14),
@FaxNumber nvarchar(14),
@KeyAcctMgr nvarchar(25),
@StatusTypeID int)
AS

IF @CompanyName='' SET @CompanyName='%'
IF @FirstName ='' SET @FirstName ='%'
IF @LastName ='' SET @LastName ='%'
IF @Address ='' SET @Address ='%'
IF @City ='' SET @City ='%'
IF @StateOrProvince ='' SET @StateOrProvince ='%'
IF @PostalCode ='' SET @PostalCode ='%'
IF @EmailName ='' SET @EmailName ='%'
IF @WorkPhone ='' SET @WorkPhone ='%'
IF @FaxNumber ='' SET @FaxNumber ='%'
IF @WorkPhone ='' SET @WorkPhone ='%'
IF @KeyAcctMgr='' SET @KeyAcctMgr='%'

IF @SearchType='OR' -- perform an "OR" search
BEGIN
SELECT ContactID, StoreNum, CompanyName, BuildingName, FirstName, LastName,
Address, City, StateOrProvince, PostalCode, EmailName, WorkPhone,
WorkExtension, FaxNumber, MobilePhone, ReferredBy, Notes, GEOMarket, Area,
District, CVSDistrict, ContactTypeID, StatusTypeID,
NumberOfEmployees, FluClinicMailing, KeyAcctMgr, AcctMaintRep,
SecondaryContact, SecondaryContactTitle, SecondaryPhone, SecondaryEmail,
ClinicCompetition, Flag, Title
FROM dbo.Contacts
WHERE (ContactID = @ContactID) OR
(StoreNum = @StoreNum) OR
(CompanyName LIKE @CompanyName) OR
(FirstName LIKE @FirstName) OR
(LastName LIKE @LastName) OR
(Address LIKE @Address) OR
(City LIKE @City) OR
(StateOrProvince LIKE @StateOrProvince) OR
(PostalCode LIKE @PostalCode) OR
(EmailName LIKE @EmailName) OR
(WorkPhone LIKE @WorkPhone) OR
(FaxNumber LIKE @FaxNumber) OR
(KeyAcctMgr LIKE @KeyAcctMgr) OR
(StatusTypeID = @StatusTypeID)
END

ELSE -- Perform an "AND" search
BEGIN
SELECT ContactID, StoreNum, CompanyName, BuildingName, FirstName, LastName,
Address, City, StateOrProvince, PostalCode, EmailName, WorkPhone, WorkExtens
ion, FaxNumber, MobilePhone, ReferredBy, Notes, GEOMarket, Area, District,
CVSDistrict, ContactTypeID, StatusTypeID,
NumberOfEmployees, FluClinicMailing, KeyAcctMgr, AcctMaintRep,
SecondaryContact, SecondaryContactTitle, SecondaryPhone, SecondaryEmail,
ClinicCompetition, Flag, Title
FROM dbo.Contacts
WHERE (ContactID = @ContactID) AND
(StoreNum = @StoreNum) AND
(CompanyName LIKE @CompanyName) AND
(FirstName LIKE @FirstName) AND
(LastName LIKE @LastName) AND
(Address LIKE @Address) AND
(City LIKE @City) AND
(StateOrProvince LIKE @StateOrProvince) AND
(PostalCode LIKE @PostalCode) AND
(EmailName LIKE @EmailName) AND
(WorkPhone LIKE @WorkPhone) AND
(FaxNumber LIKE @FaxNumber) AND
(KeyAcctMgr LIKE @KeyAcctMgr) AND
(StatusTypeID = @StatusTypeID)
END
Click to expand...
 
K

Kenneth Courville

It is in single record view.


J. Clay said:
Question -- Is this form in a datasheet view or is it a single record view?




Kenneth Courville said:
My client has a table with over contact 60000 records. We recently upgraded
to a SQL Server 2000 back-end with MS Access 2002 front-end.

When opening the contacts form with all of these records, I can watch the
MSACCESS process climb to 100+ Mb of RAM. Since their PC's only have
Click to expand...
128
Mb
of RAM, this is a problem. The PC's involved tend to run slow and/or crash.

I created a separate form to allow them to specify criteria that will be
used by the contact form. This way, only the records they need to work with
at the time are cached from the server, and not near as much RAM is needed.

My first method of doing this was by creating a string using VBA in the
search form (called Contacts-Find). Upon clicking a button, code is
executed to build the filter, the actual Contacts form is opened, and the
form's server filter item is set to this string.

The problem with this method seemed that the filter would sometimes be saved
with the form, and that same search criteria is "stuck" in there when the
next person tries to execute a search.

For my next method, I tried creating a stored procecure, which follows
below. In addition, the Input Parameters option for the Contact form is set
to (not complete text):
Click to expand...
"@SearchType=forms![contacts-find]!SearchType,@ContactID=forms![contacts-find]!ContactID,@StoreNum=forms![contacts-find]!StoreNum,@CompanyName=forms![contacts-find]!CompanyName,@FirstName=forms![contacts-find]!FirstName,@LastName=forms![contacts-find]!LastName,@Address=forms![contacts-find]!Address,@Cit
y=forms![contacts-find]!City..."

Basically, the parameter @SearchType is used to determine whether to perform
and "OR" query or an "AND" query. The problem now is that we cannot
effectively perform an "AND" query. This occurs because the client may not
necessary provide data for all of the input parameters below, which results
in search for records that have to have blank or 0 in the field.

My question is... is there a better way to do this, or is there a way to fix
my methods listed above?


ALTER PROCEDURE dbo.spFilterContactsTest
(@SearchType char(3),
@ContactID int,
@StoreNum float,
@CompanyName nvarchar(50),
@FirstName nvarchar(20),
@LastName nvarchar(25),
@Address nvarchar(40),
@City nvarchar(25),
@StateOrProvince nvarchar(2),
@PostalCode nvarchar(10),
@EmailName nvarchar(50),
@WorkPhone nvarchar(14),
@FaxNumber nvarchar(14),
@KeyAcctMgr nvarchar(25),
@StatusTypeID int)
AS

IF @CompanyName='' SET @CompanyName='%'
IF @FirstName ='' SET @FirstName ='%'
IF @LastName ='' SET @LastName ='%'
IF @Address ='' SET @Address ='%'
IF @City ='' SET @City ='%'
IF @StateOrProvince ='' SET @StateOrProvince ='%'
IF @PostalCode ='' SET @PostalCode ='%'
IF @EmailName ='' SET @EmailName ='%'
IF @WorkPhone ='' SET @WorkPhone ='%'
IF @FaxNumber ='' SET @FaxNumber ='%'
IF @WorkPhone ='' SET @WorkPhone ='%'
IF @KeyAcctMgr='' SET @KeyAcctMgr='%'

IF @SearchType='OR' -- perform an "OR" search
BEGIN
SELECT ContactID, StoreNum, CompanyName, BuildingName, FirstName, LastName,
Address, City, StateOrProvince, PostalCode, EmailName, WorkPhone,
WorkExtension, FaxNumber, MobilePhone, ReferredBy, Notes, GEOMarket, Area,
District, CVSDistrict, ContactTypeID, StatusTypeID,
NumberOfEmployees, FluClinicMailing, KeyAcctMgr, AcctMaintRep,
SecondaryContact, SecondaryContactTitle, SecondaryPhone, SecondaryEmail,
ClinicCompetition, Flag, Title
FROM dbo.Contacts
WHERE (ContactID = @ContactID) OR
(StoreNum = @StoreNum) OR
(CompanyName LIKE @CompanyName) OR
(FirstName LIKE @FirstName) OR
(LastName LIKE @LastName) OR
(Address LIKE @Address) OR
(City LIKE @City) OR
(StateOrProvince LIKE @StateOrProvince) OR
(PostalCode LIKE @PostalCode) OR
(EmailName LIKE @EmailName) OR
(WorkPhone LIKE @WorkPhone) OR
(FaxNumber LIKE @FaxNumber) OR
(KeyAcctMgr LIKE @KeyAcctMgr) OR
(StatusTypeID = @StatusTypeID)
END

ELSE -- Perform an "AND" search
BEGIN
SELECT ContactID, StoreNum, CompanyName, BuildingName, FirstName, LastName,
Address, City, StateOrProvince, PostalCode, EmailName, WorkPhone, WorkExtens
ion, FaxNumber, MobilePhone, ReferredBy, Notes, GEOMarket, Area, District,
CVSDistrict, ContactTypeID, StatusTypeID,
NumberOfEmployees, FluClinicMailing, KeyAcctMgr, AcctMaintRep,
SecondaryContact, SecondaryContactTitle, SecondaryPhone, SecondaryEmail,
ClinicCompetition, Flag, Title
FROM dbo.Contacts
WHERE (ContactID = @ContactID) AND
(StoreNum = @StoreNum) AND
(CompanyName LIKE @CompanyName) AND
(FirstName LIKE @FirstName) AND
(LastName LIKE @LastName) AND
(Address LIKE @Address) AND
(City LIKE @City) AND
(StateOrProvince LIKE @StateOrProvince) AND
(PostalCode LIKE @PostalCode) AND
(EmailName LIKE @EmailName) AND
(WorkPhone LIKE @WorkPhone) AND
(FaxNumber LIKE @FaxNumber) AND
(KeyAcctMgr LIKE @KeyAcctMgr) AND
(StatusTypeID = @StatusTypeID)
END
Click to expand...
Click to expand...
 
K

Kenneth Courville

I had seen something about that in the Access help files. Do I simply need
to change the file extension from MDB to MDE to implement this?

Andy Williams said:
The server filter getting 'stuck' could be avoided by
giving users an ADE file. This way no modifications can
be made to your forms.

I'll let someone else address the stored procedure topic.

-----Original Message-----
My client has a table with over contact 60000 records. We recently upgraded
to a SQL Server 2000 back-end with MS Access 2002 front- end.

When opening the contacts form with all of these records, I can watch the
MSACCESS process climb to 100+ Mb of RAM. Since their PC's only have 128 Mb
of RAM, this is a problem. The PC's involved tend to run slow and/or crash.

I created a separate form to allow them to specify criteria that will be
used by the contact form. This way, only the records they need to work with
at the time are cached from the server, and not near as much RAM is needed.

My first method of doing this was by creating a string using VBA in the
search form (called Contacts-Find). Upon clicking a button, code is
executed to build the filter, the actual Contacts form is opened, and the
form's server filter item is set to this string.

The problem with this method seemed that the filter would sometimes be saved
with the form, and that same search criteria is "stuck" in there when the
next person tries to execute a search.

For my next method, I tried creating a stored procecure, which follows
below. In addition, the Input Parameters option for the Contact form is set
to (not complete text):
"@SearchType=forms![contacts-find]! SearchType,@ContactID=forms![contacts-fin
d]!ContactID,@StoreNum=forms![contacts-find]!
Click to expand...
StoreNum,@CompanyName=forms![co
ntacts-find]!CompanyName,@FirstName=forms![contacts-find]! FirstName,@LastNam
e=forms![contacts-find]!LastName,@Address=forms![contacts-
Click to expand...
find]!Address,@Cit
y=forms![contacts-find]!City..."

Basically, the parameter @SearchType is used to determine whether to perform
and "OR" query or an "AND" query. The problem now is that we cannot
effectively perform an "AND" query. This occurs because the client may not
necessary provide data for all of the input parameters below, which results
in search for records that have to have blank or 0 in the field.

My question is... is there a better way to do this, or is there a way to fix
my methods listed above?


ALTER PROCEDURE dbo.spFilterContactsTest
(@SearchType char(3),
@ContactID int,
@StoreNum float,
@CompanyName nvarchar(50),
@FirstName nvarchar(20),
@LastName nvarchar(25),
@Address nvarchar(40),
@City nvarchar(25),
@StateOrProvince nvarchar(2),
@PostalCode nvarchar(10),
@EmailName nvarchar(50),
@WorkPhone nvarchar(14),
@FaxNumber nvarchar(14),
@KeyAcctMgr nvarchar(25),
@StatusTypeID int)
AS

IF @CompanyName='' SET @CompanyName='%'
IF @FirstName ='' SET @FirstName ='%'
IF @LastName ='' SET @LastName ='%'
IF @Address ='' SET @Address ='%'
IF @City ='' SET @City ='%'
IF @StateOrProvince ='' SET @StateOrProvince ='%'
IF @PostalCode ='' SET @PostalCode ='%'
IF @EmailName ='' SET @EmailName ='%'
IF @WorkPhone ='' SET @WorkPhone ='%'
IF @FaxNumber ='' SET @FaxNumber ='%'
IF @WorkPhone ='' SET @WorkPhone ='%'
IF @KeyAcctMgr='' SET @KeyAcctMgr='%'

IF @SearchType='OR' -- perform an "OR" search
BEGIN
SELECT ContactID, StoreNum, CompanyName, BuildingName, FirstName, LastName,
Address, City, StateOrProvince, PostalCode, EmailName, WorkPhone,
WorkExtension, FaxNumber, MobilePhone, ReferredBy, Notes, GEOMarket, Area,
District, CVSDistrict, ContactTypeID, StatusTypeID,
NumberOfEmployees, FluClinicMailing, KeyAcctMgr, AcctMaintRep,
SecondaryContact, SecondaryContactTitle, SecondaryPhone, SecondaryEmail,
ClinicCompetition, Flag, Title
FROM dbo.Contacts
WHERE (ContactID = @ContactID) OR
(StoreNum = @StoreNum) OR
(CompanyName LIKE @CompanyName) OR
(FirstName LIKE @FirstName) OR
(LastName LIKE @LastName) OR
(Address LIKE @Address) OR
(City LIKE @City) OR
(StateOrProvince LIKE @StateOrProvince) OR
(PostalCode LIKE @PostalCode) OR
(EmailName LIKE @EmailName) OR
(WorkPhone LIKE @WorkPhone) OR
(FaxNumber LIKE @FaxNumber) OR
(KeyAcctMgr LIKE @KeyAcctMgr) OR
(StatusTypeID = @StatusTypeID)
END

ELSE -- Perform an "AND" search
BEGIN
SELECT ContactID, StoreNum, CompanyName, BuildingName, FirstName, LastName,
Address, City, StateOrProvince, PostalCode, EmailName, WorkPhone, WorkExtens
ion, FaxNumber, MobilePhone, ReferredBy, Notes, GEOMarket, Area, District,
CVSDistrict, ContactTypeID, StatusTypeID,
NumberOfEmployees, FluClinicMailing, KeyAcctMgr, AcctMaintRep,
SecondaryContact, SecondaryContactTitle, SecondaryPhone, SecondaryEmail,
ClinicCompetition, Flag, Title
FROM dbo.Contacts
WHERE (ContactID = @ContactID) AND
(StoreNum = @StoreNum) AND
(CompanyName LIKE @CompanyName) AND
(FirstName LIKE @FirstName) AND
(LastName LIKE @LastName) AND
(Address LIKE @Address) AND
(City LIKE @City) AND
(StateOrProvince LIKE @StateOrProvince) AND
(PostalCode LIKE @PostalCode) AND
(EmailName LIKE @EmailName) AND
(WorkPhone LIKE @WorkPhone) AND
(FaxNumber LIKE @FaxNumber) AND
(KeyAcctMgr LIKE @KeyAcctMgr) AND
(StatusTypeID = @StatusTypeID)
END


.
Click to expand...
Click to expand...
 
K

Kenneth Courville

It makes sense and sounds like good practice for navigating SQL Server
records vai Access. However, something else I didn't previously mention is
that my client frequently will perform filters on many fields. You can see
the required fields in the input parameters below. This is the reason for
my concern for performing "AND" and "OR" queries.

In one scenario, a user will want to see only his/her pending customers.
This query will require two fields to be inputted for filtering... the
customer status and account manager.

The other scenario is that a user is looking for a contact and must use
possibly one to several of the fields to retrieve a recordset that they can
look through. With this scenario in mind, I do not want to build a case
structure and query for each possible use of the provided fields.

I think what would help me is if there is a way that in the stored procedure
you could dynamically build a SQL string for just the fields that are
desired to be filtered, and then use the string to select and return the
recordset. Do you know of a way to accomplish this?


J. Clay said:
This is good....Your stored procedure should return ONLY 1 record. This is
the beauty of Client Server applications. The server does more work and
returns ONLY the data you need, thus reducing client requirements and
network traffic.

Now, onto how to make it work.

Although others do, I don't use the built in record navigation in Access.
Instead I have a store procedure that has all of the required parameters
along with 2 additional. The first is the current record ID and the second
is the Function (F,P,N,L,S) for First, Previous, Next, Last, Specific.
Quick note: It is important that you have some type of key field in the
table that you can sort on. You can even change the sort by adding a
different parameter.

In the SP, if the function is either F or L, I can just select the Min or
Max of the Sort ID as the record to ruturn and use that in the Where clause
of my final Select in the SP. If it is P or N, use WHILE Loop the check for
the existance of the next or previous record ID while moving up or down
through the records using a Select Count WHERE ID = record to check for.
This way you can quickly cycle up or down through a couple of tries to get
to your next record. The S is for going to a Specific record that is passed
in the Current rec ID parameter. Since I know where I am going, I don't
care where I am coming from.

To pass the parameters, I just set the inputparameters in code. When you
you do this, it will automatically requery the recordset and pull your new
record.

The added benefit to this method is that it completely bypasses the mouse
wheel which can be an extreme pain in many instances. In most of the forms
I program this way, I have an Edit, Save and Undo button on the form that
allows me to lock the form so a user has to conciously click the edit button
to change any of the data. Also, when they click on edit, I shut the
navigation button off, so they have to either click the save or undo buttons
in order to get out of the record. This ensures that code is run when you
want it to run everytime.

Hope this makes sense and helps.

J. Clay


Kenneth Courville said:
It is in single record view.


watch
the have
128
Click to expand...
will
be and
the when
the
Click to expand...
form
is
Click to expand...
"@SearchType=forms![contacts-find]!SearchType,@ContactID=forms![contacts-find]!ContactID,@StoreNum=forms![contacts-find]!StoreNum,@CompanyName=forms![contacts-find]!CompanyName,@FirstName=forms![contacts-find]!FirstName,@LastName=forms![contacts-find]!LastName,@Address=forms![contacts-find]!Address,@Cit
y=forms![contacts-find]!City..."

Basically, the parameter @SearchType is used to determine whether to
perform
and "OR" query or an "AND" query. The problem now is that we cannot
effectively perform an "AND" query. This occurs because the client may
not
necessary provide data for all of the input parameters below, which
results
in search for records that have to have blank or 0 in the field.

My question is... is there a better way to do this, or is there a
Click to expand...
Click to expand...
way
to
Click to expand...
Click to expand...
 
K

Kenneth Courville

I see the option, but it is greyed out.

Don't you have to have some other Office license to compile an Access
project?

J. Clay said:
No, you actually have to compile it to an ADE. This is under
Tools->Database Utilites->Make ADE


Kenneth Courville said:
I had seen something about that in the Access help files. Do I simply need
to change the file extension from MDB to MDE to implement this?

Andy Williams said:
The server filter getting 'stuck' could be avoided by
giving users an ADE file. This way no modifications can
be made to your forms.

I'll let someone else address the stored procedure topic.


-----Original Message-----
My client has a table with over contact 60000 records.
We recently upgraded
to a SQL Server 2000 back-end with MS Access 2002 front-
end.

When opening the contacts form with all of these records,
I can watch the
MSACCESS process climb to 100+ Mb of RAM. Since their
PC's only have 128 Mb
of RAM, this is a problem. The PC's involved tend to run
slow and/or crash.

I created a separate form to allow them to specify
criteria that will be
used by the contact form. This way, only the records
they need to work with
at the time are cached from the server, and not near as
much RAM is needed.

My first method of doing this was by creating a string
using VBA in the
search form (called Contacts-Find). Upon clicking a
button, code is
executed to build the filter, the actual Contacts form is
opened, and the
form's server filter item is set to this string.

The problem with this method seemed that the filter would
sometimes be saved
with the form, and that same search criteria is "stuck"
in there when the
next person tries to execute a search.

For my next method, I tried creating a stored procecure,
which follows
below. In addition, the Input Parameters option for the
Contact form is set
to (not complete text):
"@SearchType=forms![contacts-find]!
SearchType,@ContactID=forms![contacts-fin
d]!ContactID,@StoreNum=forms![contacts-find]!
StoreNum,@CompanyName=forms![co
ntacts-find]!CompanyName,@FirstName=forms![contacts-find]!
FirstName,@LastNam
e=forms![contacts-find]!LastName,@Address=forms![contacts-
find]!Address,@Cit
y=forms![contacts-find]!City..."

Basically, the parameter @SearchType is used to determine
whether to perform
and "OR" query or an "AND" query. The problem now is
that we cannot
effectively perform an "AND" query. This occurs because
the client may not
necessary provide data for all of the input parameters
below, which results
in search for records that have to have blank or 0 in the
field.

My question is... is there a better way to do this, or is
there a way to fix
my methods listed above?


ALTER PROCEDURE dbo.spFilterContactsTest
(@SearchType char(3),
@ContactID int,
@StoreNum float,
@CompanyName nvarchar(50),
@FirstName nvarchar(20),
@LastName nvarchar(25),
@Address nvarchar(40),
@City nvarchar(25),
@StateOrProvince nvarchar(2),
@PostalCode nvarchar(10),
@EmailName nvarchar(50),
@WorkPhone nvarchar(14),
@FaxNumber nvarchar(14),
@KeyAcctMgr nvarchar(25),
@StatusTypeID int)
AS

IF @CompanyName='' SET @CompanyName='%'
IF @FirstName ='' SET @FirstName ='%'
IF @LastName ='' SET @LastName ='%'
IF @Address ='' SET @Address ='%'
IF @City ='' SET @City ='%'
IF @StateOrProvince ='' SET @StateOrProvince ='%'
IF @PostalCode ='' SET @PostalCode ='%'
IF @EmailName ='' SET @EmailName ='%'
IF @WorkPhone ='' SET @WorkPhone ='%'
IF @FaxNumber ='' SET @FaxNumber ='%'
IF @WorkPhone ='' SET @WorkPhone ='%'
IF @KeyAcctMgr='' SET @KeyAcctMgr='%'

IF @SearchType='OR' -- perform an "OR" search
BEGIN
SELECT ContactID, StoreNum, CompanyName, BuildingName,
FirstName, LastName,
Address, City, StateOrProvince, PostalCode, EmailName,
WorkPhone,
WorkExtension, FaxNumber, MobilePhone, ReferredBy, Notes,
GEOMarket, Area,
District, CVSDistrict, ContactTypeID, StatusTypeID,
NumberOfEmployees, FluClinicMailing, KeyAcctMgr,
AcctMaintRep,
SecondaryContact, SecondaryContactTitle, SecondaryPhone,
SecondaryEmail,
ClinicCompetition, Flag, Title
FROM dbo.Contacts
WHERE (ContactID = @ContactID) OR
(StoreNum = @StoreNum) OR
(CompanyName LIKE @CompanyName) OR
(FirstName LIKE @FirstName) OR
(LastName LIKE @LastName) OR
(Address LIKE @Address) OR
(City LIKE @City) OR
(StateOrProvince LIKE @StateOrProvince) OR
(PostalCode LIKE @PostalCode) OR
(EmailName LIKE @EmailName) OR
(WorkPhone LIKE @WorkPhone) OR
(FaxNumber LIKE @FaxNumber) OR
(KeyAcctMgr LIKE @KeyAcctMgr) OR
(StatusTypeID = @StatusTypeID)
END

ELSE -- Perform an "AND" search
BEGIN
SELECT ContactID, StoreNum, CompanyName, BuildingName,
FirstName, LastName,
Address, City, StateOrProvince, PostalCode, EmailName,
WorkPhone, WorkExtens
ion, FaxNumber, MobilePhone, ReferredBy, Notes,
GEOMarket, Area, District,
CVSDistrict, ContactTypeID, StatusTypeID,
NumberOfEmployees, FluClinicMailing, KeyAcctMgr,
AcctMaintRep,
SecondaryContact, SecondaryContactTitle, SecondaryPhone,
SecondaryEmail,
ClinicCompetition, Flag, Title
FROM dbo.Contacts
WHERE (ContactID = @ContactID) AND
(StoreNum = @StoreNum) AND
(CompanyName LIKE @CompanyName) AND
(FirstName LIKE @FirstName) AND
(LastName LIKE @LastName) AND
(Address LIKE @Address) AND
(City LIKE @City) AND
(StateOrProvince LIKE @StateOrProvince) AND
(PostalCode LIKE @PostalCode) AND
(EmailName LIKE @EmailName) AND
(WorkPhone LIKE @WorkPhone) AND
(FaxNumber LIKE @FaxNumber) AND
(KeyAcctMgr LIKE @KeyAcctMgr) AND
(StatusTypeID = @StatusTypeID)
END


.
Click to expand...
Click to expand...
Click to expand...
 
J

J. Clay

In answer to your other post about ADEs. I believe that you have to convert
the file to a 2002 structure in order to create an ADE. Look on your
database window. It will say either Access 2000 file format or Access 2002
file format. If it is in 2000 format, you need to convert it under
tool->database utilities->convert database.

See below for the some info re this post.


Kenneth Courville said:
It makes sense and sounds like good practice for navigating SQL Server
records vai Access. However, something else I didn't previously mention is
that my client frequently will perform filters on many fields. You can see
the required fields in the input parameters below. This is the reason for
my concern for performing "AND" and "OR" queries.
Click to expand...

********
I have set up a very similar scenario that seems to work very well. If the
users are looking through a specific group of fields, you can create a view
of those fields only to limit the data thus increasing efficiency and create
a form, similar to your main one only with the specific seachable fields and
set it to automatically go into Server Filter by Form. This will give you
"AND" and "OR" functionality right on a usable form. When they select the
filter button in a tool bar (I set up a custom tool bar just for filter by
form), the user can cycle through the records that the server returned. On
the form there should be a "Select Record" button which will change the
input parameters on the main form and close the search form. The nice thing
about this type of search is that you can use any valid SQL query tools
(i.e. Like '%joe%').
********
In one scenario, a user will want to see only his/her pending customers.
This query will require two fields to be inputted for filtering... the
customer status and account manager.

The other scenario is that a user is looking for a contact and must use
possibly one to several of the fields to retrieve a recordset that they can
look through. With this scenario in mind, I do not want to build a case
structure and query for each possible use of the provided fields.

I think what would help me is if there is a way that in the stored procedure
you could dynamically build a SQL string for just the fields that are
desired to be filtered, and then use the string to select and return the
recordset. Do you know of a way to accomplish this?
Click to expand...
*********
Yes, although I think the solution above is much better for your needs. You
can build dynamic SQL w/in a stored procedure. I have never tried to build
a Dynamic SQL form record source and am afraid it may time out on you. I
don't recommend it for the weak of heart if it is a large SQL Statement
because it can be a royal pain to trouble shoot, but I have a couple of big
ones that are working great. Also, note that with dynamic SQL procedures,
they probably won't process as fast due to the fact that you are not
utilizing the pre-compiled state of a normal stored procedure.

To accomplish this, set up variables in your SP and build your SQL
Statement. There is an issue in that the variables need to be a max of I
think 5000 characters (or maybe less??) so if it is a big statement you need
to break it up into several variables. At the end of the Stored Procedure
use the T-SQL EXEC command:
DECLARE @strSql VARCHAR(5000)
SET @strSql = 'SELECT * FROM Table1 WHERE ' + @SearchFieldVar + ' = ''' +
@SearchValue + ''''
EXEC (@strSql)

HTH
J. Clay

J. Clay said:
This is good....Your stored procedure should return ONLY 1 record. This is
the beauty of Client Server applications. The server does more work and
returns ONLY the data you need, thus reducing client requirements and
network traffic.

Now, onto how to make it work.

Although others do, I don't use the built in record navigation in Access.
Instead I have a store procedure that has all of the required parameters
along with 2 additional. The first is the current record ID and the second
is the Function (F,P,N,L,S) for First, Previous, Next, Last, Specific.
Quick note: It is important that you have some type of key field in the
table that you can sort on. You can even change the sort by adding a
different parameter.

In the SP, if the function is either F or L, I can just select the Min or
Max of the Sort ID as the record to ruturn and use that in the Where clause
of my final Select in the SP. If it is P or N, use WHILE Loop the check for
the existance of the next or previous record ID while moving up or down
through the records using a Select Count WHERE ID = record to check for.
This way you can quickly cycle up or down through a couple of tries to get
to your next record. The S is for going to a Specific record that is passed
in the Current rec ID parameter. Since I know where I am going, I don't
care where I am coming from.

To pass the parameters, I just set the inputparameters in code. When you
you do this, it will automatically requery the recordset and pull your new
record.

The added benefit to this method is that it completely bypasses the mouse
wheel which can be an extreme pain in many instances. In most of the forms
I program this way, I have an Edit, Save and Undo button on the form that
allows me to lock the form so a user has to conciously click the edit button
to change any of the data. Also, when they click on edit, I shut the
navigation button off, so they have to either click the save or undo buttons
in order to get out of the record. This ensures that code is run when you
want it to run everytime.

Hope this makes sense and helps.

J. Clay


will in
the
Click to expand...
sometimes
be form
Click to expand...
"@SearchType=forms![contacts-find]!SearchType,@ContactID=forms![contacts-find]!ContactID,@StoreNum=forms![contacts-find]!StoreNum,@CompanyName=forms![contacts-find]!CompanyName,@FirstName=forms![contacts-find]!FirstName,@LastName=forms![contacts-find]!LastName,@Address=forms![contacts-find]!Address,@Cit
y=forms![contacts-find]!City..."

Basically, the parameter @SearchType is used to determine whether to
perform
and "OR" query or an "AND" query. The problem now is that we cannot
effectively perform an "AND" query. This occurs because the
Click to expand...
client
may
not
necessary provide data for all of the input parameters below, which
results
in search for records that have to have blank or 0 in the field.

My question is... is there a better way to do this, or is there a
Click to expand...
way
to
fix
my methods listed above?


ALTER PROCEDURE dbo.spFilterContactsTest
(@SearchType char(3),
@ContactID int,
@StoreNum float,
@CompanyName nvarchar(50),
@FirstName nvarchar(20),
@LastName nvarchar(25),
@Address nvarchar(40),
@City nvarchar(25),
@StateOrProvince nvarchar(2),
@PostalCode nvarchar(10),
@EmailName nvarchar(50),
@WorkPhone nvarchar(14),
@FaxNumber nvarchar(14),
@KeyAcctMgr nvarchar(25),
@StatusTypeID int)
AS

IF @CompanyName='' SET @CompanyName='%'
IF @FirstName ='' SET @FirstName ='%'
IF @LastName ='' SET @LastName ='%'
IF @Address ='' SET @Address ='%'
IF @City ='' SET @City ='%'
IF @StateOrProvince ='' SET @StateOrProvince ='%'
IF @PostalCode ='' SET @PostalCode ='%'
IF @EmailName ='' SET @EmailName ='%'
IF @WorkPhone ='' SET @WorkPhone ='%'
IF @FaxNumber ='' SET @FaxNumber ='%'
IF @WorkPhone ='' SET @WorkPhone ='%'
IF @KeyAcctMgr='' SET @KeyAcctMgr='%'

IF @SearchType='OR' -- perform an "OR" search
BEGIN
SELECT ContactID, StoreNum, CompanyName, BuildingName, FirstName,
LastName,
Address, City, StateOrProvince, PostalCode, EmailName, WorkPhone,
WorkExtension, FaxNumber, MobilePhone, ReferredBy, Notes, GEOMarket,
Area,
District, CVSDistrict, ContactTypeID, StatusTypeID,
NumberOfEmployees, FluClinicMailing, KeyAcctMgr, AcctMaintRep,
SecondaryContact, SecondaryContactTitle, SecondaryPhone, SecondaryEmail,
ClinicCompetition, Flag, Title
FROM dbo.Contacts
WHERE (ContactID = @ContactID) OR
(StoreNum = @StoreNum) OR
(CompanyName LIKE @CompanyName) OR
(FirstName LIKE @FirstName) OR
(LastName LIKE @LastName) OR
(Address LIKE @Address) OR
(City LIKE @City) OR
(StateOrProvince LIKE @StateOrProvince) OR
(PostalCode LIKE @PostalCode) OR
(EmailName LIKE @EmailName) OR
(WorkPhone LIKE @WorkPhone) OR
(FaxNumber LIKE @FaxNumber) OR
(KeyAcctMgr LIKE @KeyAcctMgr) OR
(StatusTypeID = @StatusTypeID)
END

ELSE -- Perform an "AND" search
BEGIN
SELECT ContactID, StoreNum, CompanyName, BuildingName, FirstName,
LastName,
Address, City, StateOrProvince, PostalCode, EmailName, WorkPhone,
WorkExtens
ion, FaxNumber, MobilePhone, ReferredBy, Notes, GEOMarket, Area,
District,
CVSDistrict, ContactTypeID, StatusTypeID,
NumberOfEmployees, FluClinicMailing, KeyAcctMgr, AcctMaintRep,
SecondaryContact, SecondaryContactTitle, SecondaryPhone, SecondaryEmail,
ClinicCompetition, Flag, Title
FROM dbo.Contacts
WHERE (ContactID = @ContactID) AND
(StoreNum = @StoreNum) AND
(CompanyName LIKE @CompanyName) AND
(FirstName LIKE @FirstName) AND
(LastName LIKE @LastName) AND
(Address LIKE @Address) AND
(City LIKE @City) AND
(StateOrProvince LIKE @StateOrProvince) AND
(PostalCode LIKE @PostalCode) AND
(EmailName LIKE @EmailName) AND
(WorkPhone LIKE @WorkPhone) AND
(FaxNumber LIKE @FaxNumber) AND
(KeyAcctMgr LIKE @KeyAcctMgr) AND
(StatusTypeID = @StatusTypeID)
END
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

INSERT INTO statement 5
Clickable Hyperlinks in Database 12
sql union self-join syntax 27
Importing Contacts From MS Outlook 1
Trying to combine multiple records into one record in a Query 1
problem with arrangement of fields in address block 2
Trying to combine multiple records into one record in a Query 1
web form getting data to SQL Backend 8

Top