Hi Guys,

I just started using AccessProject/MSDE/SQL to aim my application for huge database and process. Initially this what I did to my
main data entry form with 4,000 records at present and it may grow 100X:

1) I created one Stored proc:

Alter Procedure sprContactsRS
	@intContactsID int
AS
SELECT * FROM m01TblContacts
WHERE m01ContactsID= @intContactsID

Before my m01FrmContacts open it will pass first to my search form "m01FrmSearch" where all the contacts are listed in List Box.
Once I select one Contact on the list, the m01ContactsID  will automatically assigned in one hidden txtContactsID as my SP
parameter. Double-click is to open my m01FrmContacts.

2) Then, I assigned the above SP "sprContactsRS" as the default RecordSource of my m01FrmContacts.

3) Then, I assigned this parameter to my m01FrmContacts Input Parameter Properties.
	@intContactsID int=Forms![m01FrmSearch]![txtContactsID]

The above is working fine, my question is, am I doing the right approach? What I mean is, is the above taking only one record from
the server and send to my application not like mdb backend, all records will pass through the NT cable and the filtering is occurred
in the application cause a hell of delay? My main concern is, I want my form to open like a blink of an eye.

Any tips is highly appreciated...

TIA,
George

Generally yes you are on the right path.  I have a question about this
though

"my search form "m01FrmSearch" where all the contacts are listed in List
Box."

are the contacts the 4,000 records expected to grow 100X?  If so then you
will have performance problems (and usability problems) at this point.

But the rest of it good, right on target :) 



I generally take a very similar approach, but instead of a selection list I
give the users a search string entry, then I format a SQL query using that
to find my records.  Often this is more than one record, so I also include a
list screen

[search criteria entry] -> [search results list] -> [record detail edit]

to do this you could modify your stored proc to take as param a string, then
execute a query using that string in a LIKE expression...


Alter Procedure sprContactsRS
	@strContactsname varchar
AS

declare @Search as varchar

set @Search = '%' & @strContactsname & '&'

SELECT * FROM m01TblContacts
WHERE Name LIKE @Search





-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Tuesday, November 19, 2002 12:58 AM
To: Access
Subject: [access] Right Approach


Hi Guys,

I just started using AccessProject/MSDE/SQL to aim my application for huge
database and process. Initially this what I did to my
main data entry form with 4,000 records at present and it may grow 100X:

1) I created one Stored proc:

Alter Procedure sprContactsRS
	@intContactsID int
AS
SELECT * FROM m01TblContacts
WHERE m01ContactsID= @intContactsID

Before my m01FrmContacts open it will pass first to my search form
"m01FrmSearch" where all the contacts are listed in List Box.
Once I select one Contact on the list, the m01ContactsID  will automatically
assigned in one hidden txtContactsID as my SP
parameter. Double-click is to open my m01FrmContacts.

2) Then, I assigned the above SP "sprContactsRS" as the default RecordSource
of my m01FrmContacts.

3) Then, I assigned this parameter to my m01FrmContacts Input Parameter
Properties.
	@intContactsID int=Forms![m01FrmSearch]![txtContactsID]

The above is working fine, my question is, am I doing the right approach?
What I mean is, is the above taking only one record from
the server and send to my application not like mdb backend, all records will
pass through the NT cable and the filtering is occurred
in the application cause a hell of delay? My main concern is, I want my form
to open like a blink of an eye.

Any tips is highly appreciated...

TIA,
George

Thanks Dave,
I set my ListBox RowSource empty as default to avoid slow of opening my search form. Then my search approach is, first I have an
option group where the user ask in which field you want to search. For example the user choose search by First Name to search
George, then the next step is to type the search string on the txtSearchBox. Every character typed the list will requery till the
search reach the exact value.
E.g.
SearchBox: G
--------------
Garry
Gerry
George
G-Z

SearchBox: GE
--------------
Gerry
George
G-Z

SearchBox: GEO
-------------
George
G-Z

I done the same approach like yours but in mdb, I assigned the parameter in 5 fields in my query but its very slow because the
search is reading on the entire table. Is your approach can handle more than 2 fields for search e.g. FirstName & LastName?

Thanks again for comment and tips...

Cheers,
George


-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Tuesday, November 19, 2002 7:52 PM
To: Access
Subject: [access] RE: Right Approach


Generally yes you are on the right path.  I have a question about this
though

"my search form "m01FrmSearch" where all the contacts are listed in List
Box."

are the contacts the 4,000 records expected to grow 100X?  If so then you
will have performance problems (and usability problems) at this point.

But the rest of it good, right on target :)



I generally take a very similar approach, but instead of a selection list I
give the users a search string entry, then I format a SQL query using that
to find my records.  Often this is more than one record, so I also include a
list screen

[search criteria entry] -> [search results list] -> [record detail edit]

to do this you could modify your stored proc to take as param a string, then
execute a query using that string in a LIKE expression...


Alter Procedure sprContactsRS
	@strContactsname varchar
AS

declare @Search as varchar

set @Search = '%' & @strContactsname & '&'

SELECT * FROM m01TblContacts
WHERE Name LIKE @Search





-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Tuesday, November 19, 2002 12:58 AM
To: Access
Subject: [access] Right Approach


Hi Guys,

I just started using AccessProject/MSDE/SQL to aim my application for huge
database and process. Initially this what I did to my
main data entry form with 4,000 records at present and it may grow 100X:

1) I created one Stored proc:

Alter Procedure sprContactsRS
	@intContactsID int
AS
SELECT * FROM m01TblContacts
WHERE m01ContactsID= @intContactsID

Before my m01FrmContacts open it will pass first to my search form
"m01FrmSearch" where all the contacts are listed in List Box.
Once I select one Contact on the list, the m01ContactsID  will automatically
assigned in one hidden txtContactsID as my SP
parameter. Double-click is to open my m01FrmContacts.

2) Then, I assigned the above SP "sprContactsRS" as the default RecordSource
of my m01FrmContacts.

3) Then, I assigned this parameter to my m01FrmContacts Input Parameter
Properties.
	@intContactsID int=Forms![m01FrmSearch]![txtContactsID]

The above is working fine, my question is, am I doing the right approach?
What I mean is, is the above taking only one record from
the server and send to my application not like mdb backend, all records will
pass through the NT cable and the filtering is occurred
in the application cause a hell of delay? My main concern is, I want my form
to open like a blink of an eye.

Any tips is highly appreciated...

TIA,
George

here is the core piece of code from my "customer search" dialog. This is VB6
but should be compatible with Access VB.  This is called after the user has
completed a form with multiple fields on it, and those fields are populated
into an object oSearchCriteria

Public Function GenerateCustomerQuery() As String

Dim strSql As String
Dim strWhere As String

    strSql = "SELECT * FROM view_SelectCustomer WHERE CustomerId IS NOT NULL
"
    strWhere = ""
    
    If oSearchCriteria.bAll = False Then
        If oSearchCriteria.sName <> "" Then
            strWhere = strWhere & " AND CustomerName like '%" &
RTrim(oSearchCriteria.sName) & "%' "
        End If
        If oSearchCriteria.sAddr <> "" Then
            strWhere = strWhere & " AND (StreetAddress1 like '%" &
RTrim(oSearchCriteria.sAddr) & _
                        "%' OR StreetAddress2 like '%" &
RTrim(oSearchCriteria.sAddr) & "%') "
        End If
        If oSearchCriteria.sCity <> "" Then
            strWhere = strWhere & " AND AddressCity like '%" &
RTrim(oSearchCriteria.sCity) & "%' "
        End If
        If oSearchCriteria.sState <> "" Then
            strWhere = strWhere & " AND (StateName like '%" &
RTrim(oSearchCriteria.sState) & _
                        "%' OR StateAbbreviation like '%" &
RTrim(oSearchCriteria.sState) & "%') "
        End If
        If oSearchCriteria.sZip <> "" Then
            strWhere = strWhere & " AND AddressZipCode like '%" &
RTrim(oSearchCriteria.sZip) & "%' "
        End If
        If oSearchCriteria.sPhone <> "" Then
            strWhere = strWhere & " AND Phone like '%" &
RTrim(oSearchCriteria.sPhone) & "%' "
        End If
        If oSearchCriteria.sEML <> "" Then
            strWhere = strWhere & " AND EmailAddr like '%" &
RTrim(oSearchCriteria.sEML) & "%' "
        End If
        If oSearchCriteria.sAcct <> "" Then
            strWhere = strWhere & " AND AccountNumber = '" &
oSearchCriteria.sAcct & "'"
        End If

    End If
    
    GenerateCustomerQuery = strSql & strWhere


the SQL query generated here is executed against the database, and the
results are formatted into a list box...

-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, November 20, 2002 2:50 AM
To: Access
Subject: [access] RE: Right Approach


Thanks Dave,
I set my ListBox RowSource empty as default to avoid slow of opening my
search form. Then my search approach is, first I have an
option group where the user ask in which field you want to search. For
example the user choose search by First Name to search
George, then the next step is to type the search string on the txtSearchBox.
Every character typed the list will requery till the
search reach the exact value.
E.g.
SearchBox: G
--------------
Garry
Gerry
George
G-Z

SearchBox: GE
--------------
Gerry
George
G-Z

SearchBox: GEO
-------------
George
G-Z

I done the same approach like yours but in mdb, I assigned the parameter in
5 fields in my query but its very slow because the
search is reading on the entire table. Is your approach can handle more than
2 fields for search e.g. FirstName & LastName?

Thanks again for comment and tips...

Cheers,
George


-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Tuesday, November 19, 2002 7:52 PM
To: Access
Subject: [access] RE: Right Approach


Generally yes you are on the right path.  I have a question about this
though

"my search form "m01FrmSearch" where all the contacts are listed in List
Box."

are the contacts the 4,000 records expected to grow 100X?  If so then you
will have performance problems (and usability problems) at this point.

But the rest of it good, right on target :)



I generally take a very similar approach, but instead of a selection list I
give the users a search string entry, then I format a SQL query using that
to find my records.  Often this is more than one record, so I also include a
list screen

[search criteria entry] -> [search results list] -> [record detail edit]

to do this you could modify your stored proc to take as param a string, then
execute a query using that string in a LIKE expression...


Alter Procedure sprContactsRS
	@strContactsname varchar
AS

declare @Search as varchar

set @Search = '%' & @strContactsname & '&'

SELECT * FROM m01TblContacts
WHERE Name LIKE @Search





-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Tuesday, November 19, 2002 12:58 AM
To: Access
Subject: [access] Right Approach


Hi Guys,

I just started using AccessProject/MSDE/SQL to aim my application for huge
database and process. Initially this what I did to my
main data entry form with 4,000 records at present and it may grow 100X:

1) I created one Stored proc:

Alter Procedure sprContactsRS
	@intContactsID int
AS
SELECT * FROM m01TblContacts
WHERE m01ContactsID= @intContactsID

Before my m01FrmContacts open it will pass first to my search form
"m01FrmSearch" where all the contacts are listed in List Box.
Once I select one Contact on the list, the m01ContactsID  will automatically
assigned in one hidden txtContactsID as my SP
parameter. Double-click is to open my m01FrmContacts.

2) Then, I assigned the above SP "sprContactsRS" as the default RecordSource
of my m01FrmContacts.

3) Then, I assigned this parameter to my m01FrmContacts Input Parameter
Properties.
	@intContactsID int=Forms![m01FrmSearch]![txtContactsID]

The above is working fine, my question is, am I doing the right approach?
What I mean is, is the above taking only one record from
the server and send to my application not like mdb backend, all records will
pass through the NT cable and the filtering is occurred
in the application cause a hell of delay? My main concern is, I want my form
to open like a blink of an eye.

Any tips is highly appreciated...

TIA,
George

Thanks Dave I will try it...

Cheers,
George


-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, November 20, 2002 7:26 PM
To: Access
Subject: [access] RE: Right Approach


here is the core piece of code from my "customer search" dialog. This is VB6
but should be compatible with Access VB.  This is called after the user has
completed a form with multiple fields on it, and those fields are populated
into an object oSearchCriteria

Public Function GenerateCustomerQuery() As String

Dim strSql As String
Dim strWhere As String

    strSql = "SELECT * FROM view_SelectCustomer WHERE CustomerId IS NOT NULL
"
    strWhere = ""
    
    If oSearchCriteria.bAll = False Then
        If oSearchCriteria.sName <> "" Then
            strWhere = strWhere & " AND CustomerName like '%" &
RTrim(oSearchCriteria.sName) & "%' "
        End If
        If oSearchCriteria.sAddr <> "" Then
            strWhere = strWhere & " AND (StreetAddress1 like '%" &
RTrim(oSearchCriteria.sAddr) & _
                        "%' OR StreetAddress2 like '%" &
RTrim(oSearchCriteria.sAddr) & "%') "
        End If
        If oSearchCriteria.sCity <> "" Then
            strWhere = strWhere & " AND AddressCity like '%" &
RTrim(oSearchCriteria.sCity) & "%' "
        End If
        If oSearchCriteria.sState <> "" Then
            strWhere = strWhere & " AND (StateName like '%" &
RTrim(oSearchCriteria.sState) & _
                        "%' OR StateAbbreviation like '%" &
RTrim(oSearchCriteria.sState) & "%') "
        End If
        If oSearchCriteria.sZip <> "" Then
            strWhere = strWhere & " AND AddressZipCode like '%" &
RTrim(oSearchCriteria.sZip) & "%' "
        End If
        If oSearchCriteria.sPhone <> "" Then
            strWhere = strWhere & " AND Phone like '%" &
RTrim(oSearchCriteria.sPhone) & "%' "
        End If
        If oSearchCriteria.sEML <> "" Then
            strWhere = strWhere & " AND EmailAddr like '%" &
RTrim(oSearchCriteria.sEML) & "%' "
        End If
        If oSearchCriteria.sAcct <> "" Then
            strWhere = strWhere & " AND AccountNumber = '" &
oSearchCriteria.sAcct & "'"
        End If

    End If
    
    GenerateCustomerQuery = strSql & strWhere


the SQL query generated here is executed against the database, and the
results are formatted into a list box...

-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, November 20, 2002 2:50 AM
To: Access
Subject: [access] RE: Right Approach


Thanks Dave,
I set my ListBox RowSource empty as default to avoid slow of opening my
search form. Then my search approach is, first I have an
option group where the user ask in which field you want to search. For
example the user choose search by First Name to search
George, then the next step is to type the search string on the txtSearchBox.
Every character typed the list will requery till the
search reach the exact value.
E.g.
SearchBox: G
--------------
Garry
Gerry
George
G-Z

SearchBox: GE
--------------
Gerry
George
G-Z

SearchBox: GEO
-------------
George
G-Z

I done the same approach like yours but in mdb, I assigned the parameter in
5 fields in my query but its very slow because the
search is reading on the entire table. Is your approach can handle more than
2 fields for search e.g. FirstName & LastName?

Thanks again for comment and tips...

Cheers,
George


-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Tuesday, November 19, 2002 7:52 PM
To: Access
Subject: [access] RE: Right Approach


Generally yes you are on the right path.  I have a question about this
though

"my search form "m01FrmSearch" where all the contacts are listed in List
Box."

are the contacts the 4,000 records expected to grow 100X?  If so then you
will have performance problems (and usability problems) at this point.

But the rest of it good, right on target :)



I generally take a very similar approach, but instead of a selection list I
give the users a search string entry, then I format a SQL query using that
to find my records.  Often this is more than one record, so I also include a
list screen

[search criteria entry] -> [search results list] -> [record detail edit]

to do this you could modify your stored proc to take as param a string, then
execute a query using that string in a LIKE expression...


Alter Procedure sprContactsRS
	@strContactsname varchar
AS

declare @Search as varchar

set @Search = '%' & @strContactsname & '&'

SELECT * FROM m01TblContacts
WHERE Name LIKE @Search





-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Tuesday, November 19, 2002 12:58 AM
To: Access
Subject: [access] Right Approach


Hi Guys,

I just started using AccessProject/MSDE/SQL to aim my application for huge
database and process. Initially this what I did to my
main data entry form with 4,000 records at present and it may grow 100X:

1) I created one Stored proc:

Alter Procedure sprContactsRS
	@intContactsID int
AS
SELECT * FROM m01TblContacts
WHERE m01ContactsID= @intContactsID

Before my m01FrmContacts open it will pass first to my search form
"m01FrmSearch" where all the contacts are listed in List Box.
Once I select one Contact on the list, the m01ContactsID  will automatically
assigned in one hidden txtContactsID as my SP
parameter. Double-click is to open my m01FrmContacts.

2) Then, I assigned the above SP "sprContactsRS" as the default RecordSource
of my m01FrmContacts.

3) Then, I assigned this parameter to my m01FrmContacts Input Parameter
Properties.
	@intContactsID int=Forms![m01FrmSearch]![txtContactsID]

The above is working fine, my question is, am I doing the right approach?
What I mean is, is the above taking only one record from
the server and send to my application not like mdb backend, all records will
pass through the NT cable and the filtering is occurred
in the application cause a hell of delay? My main concern is, I want my form
to open like a blink of an eye.

Any tips is highly appreciated...

TIA,
George