Hi Guys,

We have a prospective client now having the above number of records as their contacts. When I tried to create a temp table having
the above records to test on our standard Membership database which we are trying to sell to them, my Members Information form is
opening after 30 seconds which is not acceptable. I set up my Records Source as mentioned below:

tblMembers - My main table
qryMembers1(Source tblMembers) - To filter the members only having fldStatus="Active"
qryMembers2 (Source qryMembers1)- I set the below criteria to a couple of fields (10)for my search box on my form (AfterUpdate 
Me.Requery)
Like "*" &[Forms]![frmMembers]![txtSearchBox]& "*"

Basically the qryMembers2 is the source of my Members Information form. I know my setup is the reason why my form is opening very
slow, because when I point directly my record source to the tblMembers, my form is opening after 4 seconds which is quite
acceptable. The problem is I need that criteria which I defined to qryMembers1 and 2 and the data might grow and grow. So if the
records will double then that 4 sec. will be 8 sec. which is not acceptable now.

Only my objective is to open my Membership form in normal speed having the above numbers of records plus the criteria which I
defined from my queries.

I tried to upsize to SQL using the wizard and it is more slow.......


Any idea or suggestion is highly appreciated...

P.S.
My Members Information form having 20-30 fields and 4 subforms.
My Front/Back end is all in access2k under win2K.


Cheers,

George

George

The problem is the wildcard search. Any kind of wildcard search is slow, 
a 'find my text anywhere in this field' search is incredibly slow!

Because you are telling the database that the start of the field can be 
any sequence of characters, any indexes you've set up are useless - so the 
database has to hit every single one of those 500,000 records to find the 
matches.

To add to the delay, once it has a record, it has to loop through the text 
trying to match the characters you've supplied it with.

So, if you're searching for a four character string, and the average 
length of the data in the search field is eight characters, the database 
has to do TWO MILLION comparisons!!!! Just to return the few records 
you're interested in...

Additionally, you should think seriously about unbinding your forms from 
the datasource. This will seriously speed up the initial opening of the 
form, as it will not have to wait for a recordset to load.

Brian

> Hi Guys,

We have a prospective client now having the above number of records as 
their contacts. When I tried to create a temp table having
the above records to test on our standard Membership database which we are 
trying to sell to them, my Members Information form is
opening after 30 seconds which is not acceptable. I set up my Records 
Source as mentioned below:

tblMembers - My main table
qryMembers1(Source tblMembers) - To filter the members only having 
fldStatus="Active"
qryMembers2 (Source qryMembers1)- I set the below criteria to a couple of 
fields (10)for my search box on my form (AfterUpdate 
Me.Requery)
Like "*" &[Forms]![frmMembers]![txtSearchBox]& "*"

Basically the qryMembers2 is the source of my Members Information form. I 
know my setup is the reason why my form is opening very
slow, because when I point directly my record source to the tblMembers, my 
form is opening after 4 seconds which is quite
acceptable. The problem is I need that criteria which I defined to 
qryMembers1 and 2 and the data might grow and grow. So if the
records will double then that 4 sec. will be 8 sec. which is not 
acceptable now.

Only my objective is to open my Membership form in normal speed having the 
above numbers of records plus the criteria which I
defined from my queries.

I tried to upsize to SQL using the wizard and it is more slow.......


Any idea or suggestion is highly appreciated...

P.S.
My Members Information form having 20-30 fields and 4 subforms.
My Front/Back end is all in access2k under win2K.


Cheers,

George

Thanks for the tips man.

Cheers,
George


-----Original Message-----
From: braxis@b... [mailto:braxis@b...]
Sent: Saturday, July 27, 2002 4:15 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


George

The problem is the wildcard search. Any kind of wildcard search is slow, 
a 'find my text anywhere in this field' search is incredibly slow!

Because you are telling the database that the start of the field can be 
any sequence of characters, any indexes you've set up are useless - so the 
database has to hit every single one of those 500,000 records to find the 
matches.

To add to the delay, once it has a record, it has to loop through the text 
trying to match the characters you've supplied it with.

So, if you're searching for a four character string, and the average 
length of the data in the search field is eight characters, the database 
has to do TWO MILLION comparisons!!!! Just to return the few records 
you're interested in...

Additionally, you should think seriously about unbinding your forms from 
the datasource. This will seriously speed up the initial opening of the 
form, as it will not have to wait for a recordset to load.

Brian

> Hi Guys,

We have a prospective client now having the above number of records as 
their contacts. When I tried to create a temp table having
the above records to test on our standard Membership database which we are 
trying to sell to them, my Members Information form is
opening after 30 seconds which is not acceptable. I set up my Records 
Source as mentioned below:

tblMembers - My main table
qryMembers1(Source tblMembers) - To filter the members only having 
fldStatus="Active"
qryMembers2 (Source qryMembers1)- I set the below criteria to a couple of 
fields (10)for my search box on my form (AfterUpdate 
Me.Requery)
Like "*" &[Forms]![frmMembers]![txtSearchBox]& "*"

Basically the qryMembers2 is the source of my Members Information form. I 
know my setup is the reason why my form is opening very
slow, because when I point directly my record source to the tblMembers, my 
form is opening after 4 seconds which is quite
acceptable. The problem is I need that criteria which I defined to 
qryMembers1 and 2 and the data might grow and grow. So if the
records will double then that 4 sec. will be 8 sec. which is not 
acceptable now.

Only my objective is to open my Membership form in normal speed having the 
above numbers of records plus the criteria which I
defined from my queries.

I tried to upsize to SQL using the wizard and it is more slow.......


Any idea or suggestion is highly appreciated...

P.S.
My Members Information form having 20-30 fields and 4 subforms.
My Front/Back end is all in access2k under win2K.


Cheers,

George

Hi Guys,
Now I managed to open my form fast when I  pointed directly my record source to the table instead of passing through a couple of
criteria using query. The problem now I'm facing is once I preview my report, it's opening very slow (1-2 minutes) which is not
acceptable.

One thing I notice, if any object or process passes through a query with some defined criteria, the process will be slow, I'm sure
the reason is because of the no. of records. Is there any way to increase the speed of all my process having the above records or
MsAccess cannot handle this kind of situation.

Please advice...


Cheers,
George


-----Original Message-----
From: braxis@b... [mailto:braxis@b...]
Sent: Saturday, July 27, 2002 4:15 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


George

The problem is the wildcard search. Any kind of wildcard search is slow,
a 'find my text anywhere in this field' search is incredibly slow!

Because you are telling the database that the start of the field can be
any sequence of characters, any indexes you've set up are useless - so the
database has to hit every single one of those 500,000 records to find the
matches.

To add to the delay, once it has a record, it has to loop through the text
trying to match the characters you've supplied it with.

So, if you're searching for a four character string, and the average
length of the data in the search field is eight characters, the database
has to do TWO MILLION comparisons!!!! Just to return the few records
you're interested in...

Additionally, you should think seriously about unbinding your forms from
the datasource. This will seriously speed up the initial opening of the
form, as it will not have to wait for a recordset to load.

Brian

> Hi Guys,

We have a prospective client now having the above number of records as
their contacts. When I tried to create a temp table having
the above records to test on our standard Membership database which we are
trying to sell to them, my Members Information form is
opening after 30 seconds which is not acceptable. I set up my Records
Source as mentioned below:

tblMembers - My main table
qryMembers1(Source tblMembers) - To filter the members only having
fldStatus="Active"
qryMembers2 (Source qryMembers1)- I set the below criteria to a couple of
fields (10)for my search box on my form (AfterUpdate 
Me.Requery)
Like "*" &[Forms]![frmMembers]![txtSearchBox]& "*"

Basically the qryMembers2 is the source of my Members Information form. I
know my setup is the reason why my form is opening very
slow, because when I point directly my record source to the tblMembers, my
form is opening after 4 seconds which is quite
acceptable. The problem is I need that criteria which I defined to
qryMembers1 and 2 and the data might grow and grow. So if the
records will double then that 4 sec. will be 8 sec. which is not
acceptable now.

Only my objective is to open my Membership form in normal speed having the
above numbers of records plus the criteria which I
defined from my queries.

I tried to upsize to SQL using the wizard and it is more slow.......


Any idea or suggestion is highly appreciated...

P.S.
My Members Information form having 20-30 fields and 4 subforms.
My Front/Back end is all in access2k under win2K.


Cheers,

George

Just an observation from my experience with Access 2000. When it comes to 
forms, it is just basically slower than it's predicessor (Access97) and if 
you add a variable of Accessing the data from a Server you slow down a lot 
more. My moving the data into SQL has increased the speed of my 
application immensely but I am not dealing with that number of records. 
However, if you move it into SQL and then create some basic queries as 
Views that simplify the subsequent queries in Access then you may increase 
your speed also. Try creating a view in SQL for the qryMembers1 and 
linking it to your Access front end. Remember, a query within a query 
takes twice the time to run because the impedded query has to run first 
before the other query can even start to run. SQL has a faster engine for 
processing queries.

You may also try using the filtering options rather than actually creating 
and running the query. I have also found this to increase speed 
(especially in reports) than the query itself (don't ask me why).

Anyway,  good luck.

Amy

> Hi Guys,

We have a prospective client now having the above number of records as 
their contacts. When I tried to create a temp table having
the above records to test on our standard Membership database which we are 
trying to sell to them, my Members Information form is
opening after 30 seconds which is not acceptable. I set up my Records 
Source as mentioned below:

tblMembers - My main table
qryMembers1(Source tblMembers) - To filter the members only having 
fldStatus="Active"
qryMembers2 (Source qryMembers1)- I set the below criteria to a couple of 
fields (10)for my search box on my form (AfterUpdate 
Me.Requery)
Like "*" &[Forms]![frmMembers]![txtSearchBox]& "*"

Basically the qryMembers2 is the source of my Members Information form. I 
know my setup is the reason why my form is opening very
slow, because when I point directly my record source to the tblMembers, my 
form is opening after 4 seconds which is quite
acceptable. The problem is I need that criteria which I defined to 
qryMembers1 and 2 and the data might grow and grow. So if the
records will double then that 4 sec. will be 8 sec. which is not 
acceptable now.

Only my objective is to open my Membership form in normal speed having the 
above numbers of records plus the criteria which I
defined from my queries.

I tried to upsize to SQL using the wizard and it is more slow.......


Any idea or suggestion is highly appreciated...

P.S.
My Members Information form having 20-30 fields and 4 subforms.
My Front/Back end is all in access2k under win2K.


Cheers,

George

Hi Amy,

Thanks for the tips, actually I'm not so familiar using SQL as my back-end. I remember I upsized my tables to SQL using the Upsizing
Wizards and it doesn't makes any difference and it seems its getting more slower.

What is the best way to use the SQL as a back-end?


Cheers,
George


-----Original Message-----
From: Amy Wyatt [mailto:amyw@c...]
Sent: Monday, July 29, 2002 3:56 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Just an observation from my experience with Access 2000. When it comes to
forms, it is just basically slower than it's predicessor (Access97) and if
you add a variable of Accessing the data from a Server you slow down a lot
more. My moving the data into SQL has increased the speed of my
application immensely but I am not dealing with that number of records.
However, if you move it into SQL and then create some basic queries as
Views that simplify the subsequent queries in Access then you may increase
your speed also. Try creating a view in SQL for the qryMembers1 and
linking it to your Access front end. Remember, a query within a query
takes twice the time to run because the impedded query has to run first
before the other query can even start to run. SQL has a faster engine for
processing queries.

You may also try using the filtering options rather than actually creating
and running the query. I have also found this to increase speed
(especially in reports) than the query itself (don't ask me why).

Anyway,  good luck.

Amy

> Hi Guys,

We have a prospective client now having the above number of records as
their contacts. When I tried to create a temp table having
the above records to test on our standard Membership database which we are
trying to sell to them, my Members Information form is
opening after 30 seconds which is not acceptable. I set up my Records
Source as mentioned below:

tblMembers - My main table
qryMembers1(Source tblMembers) - To filter the members only having
fldStatus="Active"
qryMembers2 (Source qryMembers1)- I set the below criteria to a couple of
fields (10)for my search box on my form (AfterUpdate 
Me.Requery)
Like "*" &[Forms]![frmMembers]![txtSearchBox]& "*"

Basically the qryMembers2 is the source of my Members Information form. I
know my setup is the reason why my form is opening very
slow, because when I point directly my record source to the tblMembers, my
form is opening after 4 seconds which is quite
acceptable. The problem is I need that criteria which I defined to
qryMembers1 and 2 and the data might grow and grow. So if the
records will double then that 4 sec. will be 8 sec. which is not
acceptable now.

Only my objective is to open my Membership form in normal speed having the
above numbers of records plus the criteria which I
defined from my queries.

I tried to upsize to SQL using the wizard and it is more slow.......


Any idea or suggestion is highly appreciated...

P.S.
My Members Information form having 20-30 fields and 4 subforms.
My Front/Back end is all in access2k under win2K.


Cheers,

George

Buy a book!

Whole careers are based on doing this correctly.

But here are some rules of thumb:

->make the back-end server do all the work.  This means move all of your
access queries to views and then treat them like linked tables in access. So
you probably have to re-work just a little any objects the use them.

->do all CRUD using stored procedures.  That is, instead of having the
statement 

adoConn.exec ("INSERT INTO tbl_X (x1,x2) VALUES ('X1','X2')")

you should have

adoConn.Exec ("exec sp_tblX_insert ('x1','x2')")

where sp_tblX_insert is a stored procedure that does the actual insert.
This looks trivial for this example but it will make your life SO MUCH
EASIER in the long run.

-> Control your indexing.  Look at all the forms and reports in your
application and determine what fields they use to query the db.  Index only
those fields.  Remove any extra indices.

...I could list 100 more...

David

PS - for you youngsters out there CRUD is Create Read Update Delete   ; )





-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Monday, July 29, 2002 9:56 AM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Hi Amy,

Thanks for the tips, actually I'm not so familiar using SQL as my back-end.
I remember I upsized my tables to SQL using the Upsizing
Wizards and it doesn't makes any difference and it seems its getting more
slower.

What is the best way to use the SQL as a back-end?


Cheers,
George


-----Original Message-----
From: Amy Wyatt [mailto:amyw@c...]
Sent: Monday, July 29, 2002 3:56 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Just an observation from my experience with Access 2000. When it comes to
forms, it is just basically slower than it's predicessor (Access97) and if
you add a variable of Accessing the data from a Server you slow down a lot
more. My moving the data into SQL has increased the speed of my
application immensely but I am not dealing with that number of records.
However, if you move it into SQL and then create some basic queries as
Views that simplify the subsequent queries in Access then you may increase
your speed also. Try creating a view in SQL for the qryMembers1 and
linking it to your Access front end. Remember, a query within a query
takes twice the time to run because the impedded query has to run first
before the other query can even start to run. SQL has a faster engine for
processing queries.

You may also try using the filtering options rather than actually creating
and running the query. I have also found this to increase speed
(especially in reports) than the query itself (don't ask me why).

Anyway,  good luck.

Amy

> Hi Guys,

We have a prospective client now having the above number of records as
their contacts. When I tried to create a temp table having
the above records to test on our standard Membership database which we are
trying to sell to them, my Members Information form is
opening after 30 seconds which is not acceptable. I set up my Records
Source as mentioned below:

tblMembers - My main table
qryMembers1(Source tblMembers) - To filter the members only having
fldStatus="Active"
qryMembers2 (Source qryMembers1)- I set the below criteria to a couple of
fields (10)for my search box on my form (AfterUpdate 
Me.Requery)
Like "*" &[Forms]![frmMembers]![txtSearchBox]& "*"

Basically the qryMembers2 is the source of my Members Information form. I
know my setup is the reason why my form is opening very
slow, because when I point directly my record source to the tblMembers, my
form is opening after 4 seconds which is quite
acceptable. The problem is I need that criteria which I defined to
qryMembers1 and 2 and the data might grow and grow. So if the
records will double then that 4 sec. will be 8 sec. which is not
acceptable now.

Only my objective is to open my Membership form in normal speed having the
above numbers of records plus the criteria which I
defined from my queries.

I tried to upsize to SQL using the wizard and it is more slow.......


Any idea or suggestion is highly appreciated...

P.S.
My Members Information form having 20-30 fields and 4 subforms.
My Front/Back end is all in access2k under win2K.


Cheers,

George

David,

This tips is very good, but what do you mean by "move all of your
access queries to views and then treat them like linked tables in access"? Can you elaborate this furthermore?


Many thanks,
George


-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, July 29, 2002 7:22 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Buy a book!

Whole careers are based on doing this correctly.

But here are some rules of thumb:

->make the back-end server do all the work.  This means move all of your
access queries to views and then treat them like linked tables in access. So
you probably have to re-work just a little any objects the use them.

->do all CRUD using stored procedures.  That is, instead of having the
statement 

adoConn.exec ("INSERT INTO tbl_X (x1,x2) VALUES ('X1','X2')")

you should have

adoConn.Exec ("exec sp_tblX_insert ('x1','x2')")

where sp_tblX_insert is a stored procedure that does the actual insert.
This looks trivial for this example but it will make your life SO MUCH
EASIER in the long run.

-> Control your indexing.  Look at all the forms and reports in your
application and determine what fields they use to query the db.  Index only
those fields.  Remove any extra indices.

...I could list 100 more...

David

PS - for you youngsters out there CRUD is Create Read Update Delete   ; )





-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Monday, July 29, 2002 9:56 AM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Hi Amy,

Thanks for the tips, actually I'm not so familiar using SQL as my back-end.
I remember I upsized my tables to SQL using the Upsizing
Wizards and it doesn't makes any difference and it seems its getting more
slower.

What is the best way to use the SQL as a back-end?


Cheers,
George


-----Original Message-----
From: Amy Wyatt [mailto:amyw@c...]
Sent: Monday, July 29, 2002 3:56 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Just an observation from my experience with Access 2000. When it comes to
forms, it is just basically slower than it's predicessor (Access97) and if
you add a variable of Accessing the data from a Server you slow down a lot
more. My moving the data into SQL has increased the speed of my
application immensely but I am not dealing with that number of records.
However, if you move it into SQL and then create some basic queries as
Views that simplify the subsequent queries in Access then you may increase
your speed also. Try creating a view in SQL for the qryMembers1 and
linking it to your Access front end. Remember, a query within a query
takes twice the time to run because the impedded query has to run first
before the other query can even start to run. SQL has a faster engine for
processing queries.

You may also try using the filtering options rather than actually creating
and running the query. I have also found this to increase speed
(especially in reports) than the query itself (don't ask me why).

Anyway,  good luck.

Amy

> Hi Guys,

We have a prospective client now having the above number of records as
their contacts. When I tried to create a temp table having
the above records to test on our standard Membership database which we are
trying to sell to them, my Members Information form is
opening after 30 seconds which is not acceptable. I set up my Records
Source as mentioned below:

tblMembers - My main table
qryMembers1(Source tblMembers) - To filter the members only having
fldStatus="Active"
qryMembers2 (Source qryMembers1)- I set the below criteria to a couple of
fields (10)for my search box on my form (AfterUpdate 
Me.Requery)
Like "*" &[Forms]![frmMembers]![txtSearchBox]& "*"

Basically the qryMembers2 is the source of my Members Information form. I
know my setup is the reason why my form is opening very
slow, because when I point directly my record source to the tblMembers, my
form is opening after 4 seconds which is quite
acceptable. The problem is I need that criteria which I defined to
qryMembers1 and 2 and the data might grow and grow. So if the
records will double then that 4 sec. will be 8 sec. which is not
acceptable now.

Only my objective is to open my Membership form in normal speed having the
above numbers of records plus the criteria which I
defined from my queries.

I tried to upsize to SQL using the wizard and it is more slow.......


Any idea or suggestion is highly appreciated...

P.S.
My Members Information form having 20-30 fields and 4 subforms.
My Front/Back end is all in access2k under win2K.


Cheers,

George

What David means is in SQL you can create views that are basically the 
same as a query in Access. The syntax is a little different but SQL has a 
pretty good GUI to create them that is very similar to the grid in Access 
Query designer. Once you create a view in SQL you can link it to an Access 
front-end just like you link tables. That way the SQL engine will actually 
run the query and just return the recordset to the Access front-end.

One thing, and I agree with David on this, when you use Access Projects 
there is not DAO unless you do some add-ins. You can link through ODBC and 
in my case this worked well but I am not dealing with quite as large of a 
database. If you are not too far along writing code in Access then I would 
strongly suggest moving to the Access Project venue. If do have quite a 
bit of code in a regular Access database the you could have quite a bit of 
debugging to do.

An excellent book actually comes from out Host (Wrox). SQL Server 2000 
Programming: Beginning Edition is very well done and I learned a great 
deal from it about SQL programming.

Good luck,

Amy

> David,

This tips is very good, but what do you mean by "move all of your
access queries to views and then treat them like linked tables in access"? 
Can you elaborate this furthermore?


Many thanks,
George


-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, July 29, 2002 7:22 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Buy a book!

Whole careers are based on doing this correctly.

But here are some rules of thumb:

->make the back-end server do all the work.  This means move all of your
access queries to views and then treat them like linked tables in access. 
So
you probably have to re-work just a little any objects the use them.

->do all CRUD using stored procedures.  That is, instead of having the
statement 

adoConn.exec ("INSERT INTO tbl_X (x1,x2) VALUES ('X1','X2')")

you should have

adoConn.Exec ("exec sp_tblX_insert ('x1','x2')")

where sp_tblX_insert is a stored procedure that does the actual insert.
This looks trivial for this example but it will make your life SO MUCH
EASIER in the long run.

-> Control your indexing.  Look at all the forms and reports in your
application and determine what fields they use to query the db.  Index only
those fields.  Remove any extra indices.

...I could list 100 more...

David

PS - for you youngsters out there CRUD is Create Read Update Delete   ; )





-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Monday, July 29, 2002 9:56 AM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Hi Amy,

Thanks for the tips, actually I'm not so familiar using SQL as my back-end.
I remember I upsized my tables to SQL using the Upsizing
Wizards and it doesn't makes any difference and it seems its getting more
slower.

What is the best way to use the SQL as a back-end?


Cheers,
George


-----Original Message-----
From: Amy Wyatt [mailto:amyw@c...]
Sent: Monday, July 29, 2002 3:56 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Just an observation from my experience with Access 2000. When it comes to
forms, it is just basically slower than it's predicessor (Access97) and if
you add a variable of Accessing the data from a Server you slow down a lot
more. My moving the data into SQL has increased the speed of my
application immensely but I am not dealing with that number of records.
However, if you move it into SQL and then create some basic queries as
Views that simplify the subsequent queries in Access then you may increase
your speed also. Try creating a view in SQL for the qryMembers1 and
linking it to your Access front end. Remember, a query within a query
takes twice the time to run because the impedded query has to run first
before the other query can even start to run. SQL has a faster engine for
processing queries.

You may also try using the filtering options rather than actually creating
and running the query. I have also found this to increase speed
(especially in reports) than the query itself (don't ask me why).

Anyway,  good luck.

Amy

> Hi Guys,

We have a prospective client now having the above number of records as
their contacts. When I tried to create a temp table having
the above records to test on our standard Membership database which we are
trying to sell to them, my Members Information form is
opening after 30 seconds which is not acceptable. I set up my Records
Source as mentioned below:

tblMembers - My main table
qryMembers1(Source tblMembers) - To filter the members only having
fldStatus="Active"
qryMembers2 (Source qryMembers1)- I set the below criteria to a couple of
fields (10)for my search box on my form (AfterUpdate 
Me.Requery)
Like "*" &[Forms]![frmMembers]![txtSearchBox]& "*"

Basically the qryMembers2 is the source of my Members Information form. I
know my setup is the reason why my form is opening very
slow, because when I point directly my record source to the tblMembers, my
form is opening after 4 seconds which is quite
acceptable. The problem is I need that criteria which I defined to
qryMembers1 and 2 and the data might grow and grow. So if the
records will double then that 4 sec. will be 8 sec. which is not
acceptable now.

Only my objective is to open my Membership form in normal speed having the
above numbers of records plus the criteria which I
defined from my queries.

I tried to upsize to SQL using the wizard and it is more slow.......


Any idea or suggestion is highly appreciated...

P.S.
My Members Information form having 20-30 fields and 4 subforms.
My Front/Back end is all in access2k under win2K.


Cheers,

George

Hi Amy,

Many thanks for your further explanation, I guess this is the time for me to move to Access Project.

By the way, which is more powerful Access Project or VB?

Cheers,
George



-----Original Message-----
From: Amy Wyatt [mailto:amyw@c...]
Sent: Tuesday, July 30, 2002 1:49 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


What David means is in SQL you can create views that are basically the 
same as a query in Access. The syntax is a little different but SQL has a 
pretty good GUI to create them that is very similar to the grid in Access 
Query designer. Once you create a view in SQL you can link it to an Access 
front-end just like you link tables. That way the SQL engine will actually 
run the query and just return the recordset to the Access front-end.

One thing, and I agree with David on this, when you use Access Projects 
there is not DAO unless you do some add-ins. You can link through ODBC and 
in my case this worked well but I am not dealing with quite as large of a 
database. If you are not too far along writing code in Access then I would 
strongly suggest moving to the Access Project venue. If do have quite a 
bit of code in a regular Access database the you could have quite a bit of 
debugging to do.

An excellent book actually comes from out Host (Wrox). SQL Server 2000 
Programming: Beginning Edition is very well done and I learned a great 
deal from it about SQL programming.

Good luck,

Amy

> David,

This tips is very good, but what do you mean by "move all of your
access queries to views and then treat them like linked tables in access"? 
Can you elaborate this furthermore?


Many thanks,
George


-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, July 29, 2002 7:22 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Buy a book!

Whole careers are based on doing this correctly.

But here are some rules of thumb:

->make the back-end server do all the work.  This means move all of your
access queries to views and then treat them like linked tables in access. 
So
you probably have to re-work just a little any objects the use them.

->do all CRUD using stored procedures.  That is, instead of having the
statement 

adoConn.exec ("INSERT INTO tbl_X (x1,x2) VALUES ('X1','X2')")

you should have

adoConn.Exec ("exec sp_tblX_insert ('x1','x2')")

where sp_tblX_insert is a stored procedure that does the actual insert.
This looks trivial for this example but it will make your life SO MUCH
EASIER in the long run.

-> Control your indexing.  Look at all the forms and reports in your
application and determine what fields they use to query the db.  Index only
those fields.  Remove any extra indices.

...I could list 100 more...

David

PS - for you youngsters out there CRUD is Create Read Update Delete   ; )





-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Monday, July 29, 2002 9:56 AM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Hi Amy,

Thanks for the tips, actually I'm not so familiar using SQL as my back-end.
I remember I upsized my tables to SQL using the Upsizing
Wizards and it doesn't makes any difference and it seems its getting more
slower.

What is the best way to use the SQL as a back-end?


Cheers,
George


-----Original Message-----
From: Amy Wyatt [mailto:amyw@c...]
Sent: Monday, July 29, 2002 3:56 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Just an observation from my experience with Access 2000. When it comes to
forms, it is just basically slower than it's predicessor (Access97) and if
you add a variable of Accessing the data from a Server you slow down a lot
more. My moving the data into SQL has increased the speed of my
application immensely but I am not dealing with that number of records.
However, if you move it into SQL and then create some basic queries as
Views that simplify the subsequent queries in Access then you may increase
your speed also. Try creating a view in SQL for the qryMembers1 and
linking it to your Access front end. Remember, a query within a query
takes twice the time to run because the impedded query has to run first
before the other query can even start to run. SQL has a faster engine for
processing queries.

You may also try using the filtering options rather than actually creating
and running the query. I have also found this to increase speed
(especially in reports) than the query itself (don't ask me why).

Anyway,  good luck.

Amy

> Hi Guys,

We have a prospective client now having the above number of records as
their contacts. When I tried to create a temp table having
the above records to test on our standard Membership database which we are
trying to sell to them, my Members Information form is
opening after 30 seconds which is not acceptable. I set up my Records
Source as mentioned below:

tblMembers - My main table
qryMembers1(Source tblMembers) - To filter the members only having
fldStatus="Active"
qryMembers2 (Source qryMembers1)- I set the below criteria to a couple of
fields (10)for my search box on my form (AfterUpdate 
Me.Requery)
Like "*" &[Forms]![frmMembers]![txtSearchBox]& "*"

Basically the qryMembers2 is the source of my Members Information form. I
know my setup is the reason why my form is opening very
slow, because when I point directly my record source to the tblMembers, my
form is opening after 4 seconds which is quite
acceptable. The problem is I need that criteria which I defined to
qryMembers1 and 2 and the data might grow and grow. So if the
records will double then that 4 sec. will be 8 sec. which is not
acceptable now.

Only my objective is to open my Membership form in normal speed having the
above numbers of records plus the criteria which I
defined from my queries.

I tried to upsize to SQL using the wizard and it is more slow.......


Any idea or suggestion is highly appreciated...

P.S.
My Members Information form having 20-30 fields and 4 subforms.
My Front/Back end is all in access2k under win2K.


Cheers,

George

When you write an application in Access, either as an MDB or ADP, you are
using VBA, Visual Basic for Applications.  This is a slimmed-down version of
VB.  Therefore, VB is more powerful (whatever that means:)  Some things are
easier but you have fewer options and some things are more difficult.  

You can move your data to SQL Server without converting your MDB to an ADP.
I would love to write all day about how to do that but I am very busy today
with my job (ack!). Get 2 books on it and read'em both!

David

-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Tuesday, July 30, 2002 8:09 AM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Hi Amy,

Many thanks for your further explanation, I guess this is the time for me to
move to Access Project.

By the way, which is more powerful Access Project or VB?

Cheers,
George



-----Original Message-----
From: Amy Wyatt [mailto:amyw@c...]
Sent: Tuesday, July 30, 2002 1:49 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


What David means is in SQL you can create views that are basically the 
same as a query in Access. The syntax is a little different but SQL has a 
pretty good GUI to create them that is very similar to the grid in Access 
Query designer. Once you create a view in SQL you can link it to an Access 
front-end just like you link tables. That way the SQL engine will actually 
run the query and just return the recordset to the Access front-end.

One thing, and I agree with David on this, when you use Access Projects 
there is not DAO unless you do some add-ins. You can link through ODBC and 
in my case this worked well but I am not dealing with quite as large of a 
database. If you are not too far along writing code in Access then I would 
strongly suggest moving to the Access Project venue. If do have quite a 
bit of code in a regular Access database the you could have quite a bit of 
debugging to do.

An excellent book actually comes from out Host (Wrox). SQL Server 2000 
Programming: Beginning Edition is very well done and I learned a great 
deal from it about SQL programming.

Good luck,

Amy

> David,

This tips is very good, but what do you mean by "move all of your
access queries to views and then treat them like linked tables in access"? 
Can you elaborate this furthermore?


Many thanks,
George


-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, July 29, 2002 7:22 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Buy a book!

Whole careers are based on doing this correctly.

But here are some rules of thumb:

->make the back-end server do all the work.  This means move all of your
access queries to views and then treat them like linked tables in access. 
So
you probably have to re-work just a little any objects the use them.

->do all CRUD using stored procedures.  That is, instead of having the
statement 

adoConn.exec ("INSERT INTO tbl_X (x1,x2) VALUES ('X1','X2')")

you should have

adoConn.Exec ("exec sp_tblX_insert ('x1','x2')")

where sp_tblX_insert is a stored procedure that does the actual insert.
This looks trivial for this example but it will make your life SO MUCH
EASIER in the long run.

-> Control your indexing.  Look at all the forms and reports in your
application and determine what fields they use to query the db.  Index only
those fields.  Remove any extra indices.

...I could list 100 more...

David

PS - for you youngsters out there CRUD is Create Read Update Delete   ; )





-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Monday, July 29, 2002 9:56 AM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Hi Amy,

Thanks for the tips, actually I'm not so familiar using SQL as my back-end.
I remember I upsized my tables to SQL using the Upsizing
Wizards and it doesn't makes any difference and it seems its getting more
slower.

What is the best way to use the SQL as a back-end?


Cheers,
George


-----Original Message-----
From: Amy Wyatt [mailto:amyw@c...]
Sent: Monday, July 29, 2002 3:56 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Just an observation from my experience with Access 2000. When it comes to
forms, it is just basically slower than it's predicessor (Access97) and if
you add a variable of Accessing the data from a Server you slow down a lot
more. My moving the data into SQL has increased the speed of my
application immensely but I am not dealing with that number of records.
However, if you move it into SQL and then create some basic queries as
Views that simplify the subsequent queries in Access then you may increase
your speed also. Try creating a view in SQL for the qryMembers1 and
linking it to your Access front end. Remember, a query within a query
takes twice the time to run because the impedded query has to run first
before the other query can even start to run. SQL has a faster engine for
processing queries.

You may also try using the filtering options rather than actually creating
and running the query. I have also found this to increase speed
(especially in reports) than the query itself (don't ask me why).

Anyway,  good luck.

Amy

> Hi Guys,

We have a prospective client now having the above number of records as
their contacts. When I tried to create a temp table having
the above records to test on our standard Membership database which we are
trying to sell to them, my Members Information form is
opening after 30 seconds which is not acceptable. I set up my Records
Source as mentioned below:

tblMembers - My main table
qryMembers1(Source tblMembers) - To filter the members only having
fldStatus="Active"
qryMembers2 (Source qryMembers1)- I set the below criteria to a couple of
fields (10)for my search box on my form (AfterUpdate 
Me.Requery)
Like "*" &[Forms]![frmMembers]![txtSearchBox]& "*"

Basically the qryMembers2 is the source of my Members Information form. I
know my setup is the reason why my form is opening very
slow, because when I point directly my record source to the tblMembers, my
form is opening after 4 seconds which is quite
acceptable. The problem is I need that criteria which I defined to
qryMembers1 and 2 and the data might grow and grow. So if the
records will double then that 4 sec. will be 8 sec. which is not
acceptable now.

Only my objective is to open my Membership form in normal speed having the
above numbers of records plus the criteria which I
defined from my queries.

I tried to upsize to SQL using the wizard and it is more slow.......


Any idea or suggestion is highly appreciated...

P.S.
My Members Information form having 20-30 fields and 4 subforms.
My Front/Back end is all in access2k under win2K.


Cheers,

George

I don't feel I can add much to what these more experience guys below 
have said, I have used Access for similar sizes of information to that 
which you are using.  Whilst I have been using Access 97 at work I 
understand Access 2000 (which I have at home) is not much faster.  I 
would say that if you are providing this product to a client, Access is 
not the ideal tool for it, (at least not for this number of records). 
Access has a lot of workarounds to do most things but the advice I have 
received from more experienced users are that its drivers are not 
designed to handle very large loads of information in the speed that you 
or your clients require.  

Many of my queries take several minutes to run, it is only that we use 
the Databases internally that speed has never been a major issue, I 
would never dream of supplying a client with an Access Database, as the 
guys have suggested you could use SQL Server as a back end but it would 
seem that you are ultimately moving towards a complete transition to a 
more powerful database, halfway houses are never professional solutions 
in my view. If your solutions are in Microsoft Generally speaking than 
SQL Server is good transition to make.

I would take the advice of the other users below, buy some good books (I 
normally get them from www.Amazon.co.uk were you can look at reviews). I 
have found that it is better to start making transitions early rather 
than keep using quick fixes.

Regds,
Raheel


-----Original Message-----
From: dcarnley 
Sent: 30 July 2002 15:56
To: access
Cc: dcarnley
Subject: [access] Re: 500,000 + RECORDS


When you write an application in Access, either as an MDB or ADP, you 
are
using VBA, Visual Basic for Applications.  This is a slimmed-down 
version of
VB.  Therefore, VB is more powerful (whatever that means:)  Some things 
are
easier but you have fewer options and some things are more difficult.  

You can move your data to SQL Server without converting your MDB to an 
ADP.
I would love to write all day about how to do that but I am very busy 
today
with my job (ack!). Get 2 books on it and read'em both!

David

-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Tuesday, July 30, 2002 8:09 AM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Hi Amy,

Many thanks for your further explanation, I guess this is the time for 
me to
move to Access Project.

By the way, which is more powerful Access Project or VB?

Cheers,
George



-----Original Message-----
From: Amy Wyatt [mailto:amyw@c...]
Sent: Tuesday, July 30, 2002 1:49 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


What David means is in SQL you can create views that are basically the 
same as a query in Access. The syntax is a little different but SQL has 
a 
pretty good GUI to create them that is very similar to the grid in 
Access 
Query designer. Once you create a view in SQL you can link it to an 
Access 
front-end just like you link tables. That way the SQL engine will 
actually 
run the query and just return the recordset to the Access front-end.

One thing, and I agree with David on this, when you use Access Projects 
there is not DAO unless you do some add-ins. You can link through ODBC 
and 
in my case this worked well but I am not dealing with quite as large of 
a 
database. If you are not too far along writing code in Access then I 
would 
strongly suggest moving to the Access Project venue. If do have quite a 
bit of code in a regular Access database the you could have quite a bit 
of 
debugging to do.

An excellent book actually comes from out Host (Wrox). SQL Server 2000 
Programming: Beginning Edition is very well done and I learned a great 
deal from it about SQL programming.

Good luck,

Amy

> David,

This tips is very good, but what do you mean by "move all of your
access queries to views and then treat them like linked tables in 
access"? 
Can you elaborate this furthermore?


Many thanks,
George


-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, July 29, 2002 7:22 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Buy a book!

Whole careers are based on doing this correctly.

But here are some rules of thumb:

->make the back-end server do all the work.  This means move all of your
access queries to views and then treat them like linked tables in 
access. 
So
you probably have to re-work just a little any objects the use them.

->do all CRUD using stored procedures.  That is, instead of having the
statement 

adoConn.exec ("INSERT INTO tbl_X (x1,x2) VALUES ('X1','X2')")

you should have

adoConn.Exec ("exec sp_tblX_insert ('x1','x2')")

where sp_tblX_insert is a stored procedure that does the actual insert.
This looks trivial for this example but it will make your life SO MUCH
EASIER in the long run.

-> Control your indexing.  Look at all the forms and reports in your
application and determine what fields they use to query the db.  Index 
only
those fields.  Remove any extra indices.

...I could list 100 more...

David

PS - for you youngsters out there CRUD is Create Read Update Delete   ; 
)





-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Monday, July 29, 2002 9:56 AM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Hi Amy,

Thanks for the tips, actually I'm not so familiar using SQL as my 
back-end.
I remember I upsized my tables to SQL using the Upsizing
Wizards and it doesn't makes any difference and it seems its getting 
more
slower.

What is the best way to use the SQL as a back-end?


Cheers,
George


-----Original Message-----
From: Amy Wyatt [mailto:amyw@c...]
Sent: Monday, July 29, 2002 3:56 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Just an observation from my experience with Access 2000. When it comes 
to
forms, it is just basically slower than it's predicessor (Access97) and 
if
you add a variable of Accessing the data from a Server you slow down a 
lot
more. My moving the data into SQL has increased the speed of my
application immensely but I am not dealing with that number of records.
However, if you move it into SQL and then create some basic queries as
Views that simplify the subsequent queries in Access then you may 
increase
your speed also. Try creating a view in SQL for the qryMembers1 and
linking it to your Access front end. Remember, a query within a query
takes twice the time to run because the impedded query has to run first
before the other query can even start to run. SQL has a faster engine 
for
processing queries.

You may also try using the filtering options rather than actually 
creating
and running the query. I have also found this to increase speed
(especially in reports) than the query itself (don't ask me why).

Anyway,  good luck.

Amy

> Hi Guys,

We have a prospective client now having the above number of records as
their contacts. When I tried to create a temp table having
the above records to test on our standard Membership database which we 
are
trying to sell to them, my Members Information form is
opening after 30 seconds which is not acceptable. I set up my Records
Source as mentioned below:

tblMembers - My main table
qryMembers1(Source tblMembers) - To filter the members only having
fldStatus="Active"
qryMembers2 (Source qryMembers1)- I set the below criteria to a couple 
of
fields (10)for my search box on my form (AfterUpdate 
Me.Requery)
Like "*" &[Forms]![frmMembers]![txtSearchBox]& "*"

Basically the qryMembers2 is the source of my Members Information form. 
I
know my setup is the reason why my form is opening very
slow, because when I point directly my record source to the tblMembers, 
my
form is opening after 4 seconds which is quite
acceptable. The problem is I need that criteria which I defined to
qryMembers1 and 2 and the data might grow and grow. So if the
records will double then that 4 sec. will be 8 sec. which is not
acceptable now.

Only my objective is to open my Membership form in normal speed having 
the
above numbers of records plus the criteria which I
defined from my queries.

I tried to upsize to SQL using the wizard and it is more slow.......


Any idea or suggestion is highly appreciated...

P.S.
My Members Information form having 20-30 fields and 4 subforms.
My Front/Back end is all in access2k under win2K.


Cheers,

George










Visit our website at http://www.ubswarburg.com

This message contains confidential information and is intended only 
for the individual named.  If you are not the named addressee you 
should not disseminate, distribute or copy this e-mail.  Please 
notify the sender immediately by e-mail if you have received this 
e-mail by mistake and delete this e-mail from your system.

E-mail transmission cannot be guaranteed to be secure or error-free 
as information could be intercepted, corrupted, lost, destroyed, 
arrive late or incomplete, or contain viruses.  The sender therefore 
does not accept liability for any errors or omissions in the contents 
of this message which arise as a result of e-mail transmission.  If 
verification is required please request a hard-copy version.  This 
message is provided for informational purposes and should not be 
construed as a solicitation or offer to buy or sell any securities or 
related financial instruments.

Hi George,

I had a similar problem with an Access database I developed for my company. 
It worked ok until we got it on the network at which time it bogged-down so 
much that it became useless! One problem with Access forms is that in order 
to have data available, they need to query the entire table that they are 
based on. My solution was to re-write the front end in XML/HTML. I agree 
with Raheel, Access is not built for use over a network connection! You can 
use the built-in functionality of Access 2000 to create HTML data access 
pages. These do not query the table, but create a view or "snapshot" of the 
data. They are still connected to the table, and run much faster that the 
traditional Access forms. You can recreate the functionality of your forms 
by using javascript or vbscript (vbscript cannot be viewed in some browser 
however). You can find a lot of information on-line (make google your home 
page!) and there are several books out there on scripting.

Scott


>From: Raheel.Hennan@u...
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: 500,000 + RECORDS
>Date: Tue, 30 Jul 2002 16:13:34 +0100
>
>I don't feel I can add much to what these more experience guys below
>have said, I have used Access for similar sizes of information to that
>which you are using.  Whilst I have been using Access 97 at work I
>understand Access 2000 (which I have at home) is not much faster.  I
>would say that if you are providing this product to a client, Access is
>not the ideal tool for it, (at least not for this number of records).
>Access has a lot of workarounds to do most things but the advice I have
>received from more experienced users are that its drivers are not
>designed to handle very large loads of information in the speed that you
>or your clients require.
>
>Many of my queries take several minutes to run, it is only that we use
>the Databases internally that speed has never been a major issue, I
>would never dream of supplying a client with an Access Database, as the
>guys have suggested you could use SQL Server as a back end but it would
>seem that you are ultimately moving towards a complete transition to a
>more powerful database, halfway houses are never professional solutions
>in my view. If your solutions are in Microsoft Generally speaking than
>SQL Server is good transition to make.
>
>I would take the advice of the other users below, buy some good books (I
>normally get them from www.Amazon.co.uk were you can look at reviews). I
>have found that it is better to start making transitions early rather
>than keep using quick fixes.
>
>Regds,
>Raheel
>
>
>-----Original Message-----
>From: dcarnley
>Sent: 30 July 2002 15:56
>To: access
>Cc: dcarnley
>Subject: [access] Re: 500,000 + RECORDS
>
>
>When you write an application in Access, either as an MDB or ADP, you
>are
>using VBA, Visual Basic for Applications.  This is a slimmed-down
>version of
>VB.  Therefore, VB is more powerful (whatever that means:)  Some things
>are
>easier but you have fewer options and some things are more difficult.
>
>You can move your data to SQL Server without converting your MDB to an
>ADP.
>I would love to write all day about how to do that but I am very busy
>today
>with my job (ack!). Get 2 books on it and read'em both!
>
>David
>
>-----Original Message-----
>From: George Oro [mailto:george@c...]
>Sent: Tuesday, July 30, 2002 8:09 AM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>Hi Amy,
>
>Many thanks for your further explanation, I guess this is the time for
>me to
>move to Access Project.
>
>By the way, which is more powerful Access Project or VB?
>
>Cheers,
>George
>
>
>
>-----Original Message-----
>From: Amy Wyatt [mailto:amyw@c...]
>Sent: Tuesday, July 30, 2002 1:49 PM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>What David means is in SQL you can create views that are basically the
>same as a query in Access. The syntax is a little different but SQL has
>a
>pretty good GUI to create them that is very similar to the grid in
>Access
>Query designer. Once you create a view in SQL you can link it to an
>Access
>front-end just like you link tables. That way the SQL engine will
>actually
>run the query and just return the recordset to the Access front-end.
>
>One thing, and I agree with David on this, when you use Access Projects
>there is not DAO unless you do some add-ins. You can link through ODBC
>and
>in my case this worked well but I am not dealing with quite as large of
>a
>database. If you are not too far along writing code in Access then I
>would
>strongly suggest moving to the Access Project venue. If do have quite a
>bit of code in a regular Access database the you could have quite a bit
>of
>debugging to do.
>
>An excellent book actually comes from out Host (Wrox). SQL Server 2000
>Programming: Beginning Edition is very well done and I learned a great
>deal from it about SQL programming.
>
>Good luck,
>
>Amy
>
> > David,
>
>This tips is very good, but what do you mean by "move all of your
>access queries to views and then treat them like linked tables in
>access"?
>Can you elaborate this furthermore?
>
>
>Many thanks,
>George
>
>
>-----Original Message-----
>From: Carnley, Dave [mailto:dcarnley@a...]
>Sent: Monday, July 29, 2002 7:22 PM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>Buy a book!
>
>Whole careers are based on doing this correctly.
>
>But here are some rules of thumb:
>
>->make the back-end server do all the work.  This means move all of your
>access queries to views and then treat them like linked tables in
>access.
>So
>you probably have to re-work just a little any objects the use them.
>
>->do all CRUD using stored procedures.  That is, instead of having the
>statement
>
>adoConn.exec ("INSERT INTO tbl_X (x1,x2) VALUES ('X1','X2')")
>
>you should have
>
>adoConn.Exec ("exec sp_tblX_insert ('x1','x2')")
>
>where sp_tblX_insert is a stored procedure that does the actual insert.
>This looks trivial for this example but it will make your life SO MUCH
>EASIER in the long run.
>
>-> Control your indexing.  Look at all the forms and reports in your
>application and determine what fields they use to query the db.  Index
>only
>those fields.  Remove any extra indices.
>
>...I could list 100 more...
>
>David
>
>PS - for you youngsters out there CRUD is Create Read Update Delete   ;
>)
>
>
>
>
>
>-----Original Message-----
>From: George Oro [mailto:george@c...]
>Sent: Monday, July 29, 2002 9:56 AM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>Hi Amy,
>
>Thanks for the tips, actually I'm not so familiar using SQL as my
>back-end.
>I remember I upsized my tables to SQL using the Upsizing
>Wizards and it doesn't makes any difference and it seems its getting
>more
>slower.
>
>What is the best way to use the SQL as a back-end?
>
>
>Cheers,
>George
>
>
>-----Original Message-----
>From: Amy Wyatt [mailto:amyw@c...]
>Sent: Monday, July 29, 2002 3:56 PM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>Just an observation from my experience with Access 2000. When it comes
>to
>forms, it is just basically slower than it's predicessor (Access97) and
>if
>you add a variable of Accessing the data from a Server you slow down a
>lot
>more. My moving the data into SQL has increased the speed of my
>application immensely but I am not dealing with that number of records.
>However, if you move it into SQL and then create some basic queries as
>Views that simplify the subsequent queries in Access then you may
>increase
>your speed also. Try creating a view in SQL for the qryMembers1 and
>linking it to your Access front end. Remember, a query within a query
>takes twice the time to run because the impedded query has to run first
>before the other query can even start to run. SQL has a faster engine
>for
>processing queries.
>
>You may also try using the filtering options rather than actually
>creating
>and running the query. I have also found this to increase speed
>(especially in reports) than the query itself (don't ask me why).
>
>Anyway,  good luck.
>
>Amy
>
> > Hi Guys,
>
>We have a prospective client now having the above number of records as
>their contacts. When I tried to create a temp table having
>the above records to test on our standard Membership database which we
>are
>trying to sell to them, my Members Information form is
>opening after 30 seconds which is not acceptable. I set up my Records
>Source as mentioned below:
>
>tblMembers - My main table
>qryMembers1(Source tblMembers) - To filter the members only having
>fldStatus="Active"
>qryMembers2 (Source qryMembers1)- I set the below criteria to a couple
>of
>fields (10)for my search box on my form (AfterUpdate 
>Me.Requery)
>Like "*" &[Forms]![frmMembers]![txtSearchBox]& "*"
>
>Basically the qryMembers2 is the source of my Members Information form.
>I
>know my setup is the reason why my form is opening very
>slow, because when I point directly my record source to the tblMembers,
>my
>form is opening after 4 seconds which is quite
>acceptable. The problem is I need that criteria which I defined to
>qryMembers1 and 2 and the data might grow and grow. So if the
>records will double then that 4 sec. will be 8 sec. which is not
>acceptable now.
>
>Only my objective is to open my Membership form in normal speed having
>the
>above numbers of records plus the criteria which I
>defined from my queries.
>
>I tried to upsize to SQL using the wizard and it is more slow.......
>
>
>Any idea or suggestion is highly appreciated...
>
>P.S.
>My Members Information form having 20-30 fields and 4 subforms.
>My Front/Back end is all in access2k under win2K.
>
>
>Cheers,
>
>George
>
>
>
>
>
>
>
>
>
>
>Visit our website at http://www.ubswarburg.com
>
>This message contains confidential information and is intended only
>for the individual named.  If you are not the named addressee you
>should not disseminate, distribute or copy this e-mail.  Please
>notify the sender immediately by e-mail if you have received this
>e-mail by mistake and delete this e-mail from your system.
>
>E-mail transmission cannot be guaranteed to be secure or error-free
>as information could be intercepted, corrupted, lost, destroyed,
>arrive late or incomplete, or contain viruses.  The sender therefore
>does not accept liability for any errors or omissions in the contents
>of this message which arise as a result of e-mail transmission.  If
>verification is required please request a hard-copy version.  This
>message is provided for informational purposes and should not be
>construed as a solicitation or offer to buy or sell any securities or
>related financial instruments.
>
>




_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com

We have a very robust application using Access, SQL or Oracle back-ends with
Access as the front-end.  

You might try splitting out your database into libraries and have the forms
and code in one database which runs on your local drive, attach to tables in
another database (Access, SQL or Oracle) which is on the network, and any
other libraries (security files, ini files or other databases which feed
yours) on the network.

You would be surprised at how efficiently Access can perform if created
correctly.  We have clients with millions of record rows (in SQL & Oracle),
but the front end is all the same.  Access.

Regards,

Beth
INI, Inc.
xxx.xxx.xxxx  x110
www.iniinc.com

-----Original Message-----
From: Scott Eisenreich [mailto:seisenreich@h...] 
Sent: Tuesday, July 30, 2002 2:15 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS

Hi George,

I had a similar problem with an Access database I developed for my company. 
It worked ok until we got it on the network at which time it bogged-down so 
much that it became useless! One problem with Access forms is that in order 
to have data available, they need to query the entire table that they are 
based on. My solution was to re-write the front end in XML/HTML. I agree 
with Raheel, Access is not built for use over a network connection! You can 
use the built-in functionality of Access 2000 to create HTML data access 
pages. These do not query the table, but create a view or "snapshot" of the 
data. They are still connected to the table, and run much faster that the 
traditional Access forms. You can recreate the functionality of your forms 
by using javascript or vbscript (vbscript cannot be viewed in some browser 
however). You can find a lot of information on-line (make google your home 
page!) and there are several books out there on scripting.

Scott


>From: Raheel.Hennan@u...
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: 500,000 + RECORDS
>Date: Tue, 30 Jul 2002 16:13:34 +0100
>
>I don't feel I can add much to what these more experience guys below
>have said, I have used Access for similar sizes of information to that
>which you are using.  Whilst I have been using Access 97 at work I
>understand Access 2000 (which I have at home) is not much faster.  I
>would say that if you are providing this product to a client, Access is
>not the ideal tool for it, (at least not for this number of records).
>Access has a lot of workarounds to do most things but the advice I have
>received from more experienced users are that its drivers are not
>designed to handle very large loads of information in the speed that you
>or your clients require.
>
>Many of my queries take several minutes to run, it is only that we use
>the Databases internally that speed has never been a major issue, I
>would never dream of supplying a client with an Access Database, as the
>guys have suggested you could use SQL Server as a back end but it would
>seem that you are ultimately moving towards a complete transition to a
>more powerful database, halfway houses are never professional solutions
>in my view. If your solutions are in Microsoft Generally speaking than
>SQL Server is good transition to make.
>
>I would take the advice of the other users below, buy some good books (I
>normally get them from www.Amazon.co.uk were you can look at reviews). I
>have found that it is better to start making transitions early rather
>than keep using quick fixes.
>
>Regds,
>Raheel
>
>
>-----Original Message-----
>From: dcarnley
>Sent: 30 July 2002 15:56
>To: access
>Cc: dcarnley
>Subject: [access] Re: 500,000 + RECORDS
>
>
>When you write an application in Access, either as an MDB or ADP, you
>are
>using VBA, Visual Basic for Applications.  This is a slimmed-down
>version of
>VB.  Therefore, VB is more powerful (whatever that means:)  Some things
>are
>easier but you have fewer options and some things are more difficult.
>
>You can move your data to SQL Server without converting your MDB to an
>ADP.
>I would love to write all day about how to do that but I am very busy
>today
>with my job (ack!). Get 2 books on it and read'em both!
>
>David
>
>-----Original Message-----
>From: George Oro [mailto:george@c...]
>Sent: Tuesday, July 30, 2002 8:09 AM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>Hi Amy,
>
>Many thanks for your further explanation, I guess this is the time for
>me to
>move to Access Project.
>
>By the way, which is more powerful Access Project or VB?
>
>Cheers,
>George
>
>
>
>-----Original Message-----
>From: Amy Wyatt [mailto:amyw@c...]
>Sent: Tuesday, July 30, 2002 1:49 PM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>What David means is in SQL you can create views that are basically the
>same as a query in Access. The syntax is a little different but SQL has
>a
>pretty good GUI to create them that is very similar to the grid in
>Access
>Query designer. Once you create a view in SQL you can link it to an
>Access
>front-end just like you link tables. That way the SQL engine will
>actually
>run the query and just return the recordset to the Access front-end.
>
>One thing, and I agree with David on this, when you use Access Projects
>there is not DAO unless you do some add-ins. You can link through ODBC
>and
>in my case this worked well but I am not dealing with quite as large of
>a
>database. If you are not too far along writing code in Access then I
>would
>strongly suggest moving to the Access Project venue. If do have quite a
>bit of code in a regular Access database the you could have quite a bit
>of
>debugging to do.
>
>An excellent book actually comes from out Host (Wrox). SQL Server 2000
>Programming: Beginning Edition is very well done and I learned a great
>deal from it about SQL programming.
>
>Good luck,
>
>Amy
>
> > David,
>
>This tips is very good, but what do you mean by "move all of your
>access queries to views and then treat them like linked tables in
>access"?
>Can you elaborate this furthermore?
>
>
>Many thanks,
>George
>
>
>-----Original Message-----
>From: Carnley, Dave [mailto:dcarnley@a...]
>Sent: Monday, July 29, 2002 7:22 PM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>Buy a book!
>
>Whole careers are based on doing this correctly.
>
>But here are some rules of thumb:
>
>->make the back-end server do all the work.  This means move all of your
>access queries to views and then treat them like linked tables in
>access.
>So
>you probably have to re-work just a little any objects the use them.
>
>->do all CRUD using stored procedures.  That is, instead of having the
>statement
>
>adoConn.exec ("INSERT INTO tbl_X (x1,x2) VALUES ('X1','X2')")
>
>you should have
>
>adoConn.Exec ("exec sp_tblX_insert ('x1','x2')")
>
>where sp_tblX_insert is a stored procedure that does the actual insert.
>This looks trivial for this example but it will make your life SO MUCH
>EASIER in the long run.
>
>-> Control your indexing.  Look at all the forms and reports in your
>application and determine what fields they use to query the db.  Index
>only
>those fields.  Remove any extra indices.
>
>...I could list 100 more...
>
>David
>
>PS - for you youngsters out there CRUD is Create Read Update Delete   ;
>)
>
>
>
>
>
>-----Original Message-----
>From: George Oro [mailto:george@c...]
>Sent: Monday, July 29, 2002 9:56 AM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>Hi Amy,
>
>Thanks for the tips, actually I'm not so familiar using SQL as my
>back-end.
>I remember I upsized my tables to SQL using the Upsizing
>Wizards and it doesn't makes any difference and it seems its getting
>more
>slower.
>
>What is the best way to use the SQL as a back-end?
>
>
>Cheers,
>George
>
>
>-----Original Message-----
>From: Amy Wyatt [mailto:amyw@c...]
>Sent: Monday, July 29, 2002 3:56 PM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>Just an observation from my experience with Access 2000. When it comes
>to
>forms, it is just basically slower than it's predicessor (Access97) and
>if
>you add a variable of Accessing the data from a Server you slow down a
>lot
>more. My moving the data into SQL has increased the speed of my
>application immensely but I am not dealing with that number of records.
>However, if you move it into SQL and then create some basic queries as
>Views that simplify the subsequent queries in Access then you may
>increase
>your speed also. Try creating a view in SQL for the qryMembers1 and
>linking it to your Access front end. Remember, a query within a query
>takes twice the time to run because the impedded query has to run first
>before the other query can even start to run. SQL has a faster engine
>for
>processing queries.
>
>You may also try using the filtering options rather than actually
>creating
>and running the query. I have also found this to increase speed
>(especially in reports) than the query itself (don't ask me why).
>
>Anyway,  good luck.
>
>Amy
>
> > Hi Guys,
>
>We have a prospective client now having the above number of records as
>their contacts. When I tried to create a temp table having
>the above records to test on our standard Membership database which we
>are
>trying to sell to them, my Members Information form is
>opening after 30 seconds which is not acceptable. I set up my Records
>Source as mentioned below:
>
>tblMembers - My main table
>qryMembers1(Source tblMembers) - To filter the members only having
>fldStatus="Active"
>qryMembers2 (Source qryMembers1)- I set the below criteria to a couple
>of
>fields (10)for my search box on my form (AfterUpdate 
>Me.Requery)
>Like "*" &[Forms]![frmMembers]![txtSearchBox]& "*"
>
>Basically the qryMembers2 is the source of my Members Information form.
>I
>know my setup is the reason why my form is opening very
>slow, because when I point directly my record source to the tblMembers,
>my
>form is opening after 4 seconds which is quite
>acceptable. The problem is I need that criteria which I defined to
>qryMembers1 and 2 and the data might grow and grow. So if the
>records will double then that 4 sec. will be 8 sec. which is not
>acceptable now.
>
>Only my objective is to open my Membership form in normal speed having
>the
>above numbers of records plus the criteria which I
>defined from my queries.
>
>I tried to upsize to SQL using the wizard and it is more slow.......
>
>
>Any idea or suggestion is highly appreciated...
>
>P.S.
>My Members Information form having 20-30 fields and 4 subforms.
>My Front/Back end is all in access2k under win2K.
>
>
>Cheers,
>
>George
>
>
>
>
>
>
>
>
>
>
>Visit our website at http://www.ubswarburg.com
>
>This message contains confidential information and is intended only
>for the individual named.  If you are not the named addressee you
>should not disseminate, distribute or copy this e-mail.  Please
>notify the sender immediately by e-mail if you have received this
>e-mail by mistake and delete this e-mail from your system.
>
>E-mail transmission cannot be guaranteed to be secure or error-free
>as information could be intercepted, corrupted, lost, destroyed,
>arrive late or incomplete, or contain viruses.  The sender therefore
>does not accept liability for any errors or omissions in the contents
>of this message which arise as a result of e-mail transmission.  If
>verification is required please request a hard-copy version.  This
>message is provided for informational purposes and should not be
>construed as a solicitation or offer to buy or sell any securities or
>related financial instruments.
>
>




_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com

Hi Beth,

You make me happy today, can you elaborate your suggestion as far as you can?

At the moment my setup is, I just upsize all my table to SQL using the upsizing wizard. My access front is on my machine and
back-end on the server.

Hope you got time to spend.

Thanks is advance,
George


-----Original Message-----
From: Beth Moffitt [mailto:BethMoffitt@i...]
Sent: Tuesday, July 30, 2002 11:21 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


We have a very robust application using Access, SQL or Oracle back-ends with
Access as the front-end.

You might try splitting out your database into libraries and have the forms
and code in one database which runs on your local drive, attach to tables in
another database (Access, SQL or Oracle) which is on the network, and any
other libraries (security files, ini files or other databases which feed
yours) on the network.

You would be surprised at how efficiently Access can perform if created
correctly.  We have clients with millions of record rows (in SQL & Oracle),
but the front end is all the same.  Access.

Regards,

Beth
INI, Inc.
xxx.xxx.xxxx  x110
www.iniinc.com

-----Original Message-----
From: Scott Eisenreich [mailto:seisenreich@h...]
Sent: Tuesday, July 30, 2002 2:15 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS

Hi George,

I had a similar problem with an Access database I developed for my company.
It worked ok until we got it on the network at which time it bogged-down so
much that it became useless! One problem with Access forms is that in order
to have data available, they need to query the entire table that they are
based on. My solution was to re-write the front end in XML/HTML. I agree
with Raheel, Access is not built for use over a network connection! You can
use the built-in functionality of Access 2000 to create HTML data access
pages. These do not query the table, but create a view or "snapshot" of the
data. They are still connected to the table, and run much faster that the
traditional Access forms. You can recreate the functionality of your forms
by using javascript or vbscript (vbscript cannot be viewed in some browser
however). You can find a lot of information on-line (make google your home
page!) and there are several books out there on scripting.

Scott


>From: Raheel.Hennan@u...
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: 500,000 + RECORDS
>Date: Tue, 30 Jul 2002 16:13:34 +0100
>
>I don't feel I can add much to what these more experience guys below
>have said, I have used Access for similar sizes of information to that
>which you are using.  Whilst I have been using Access 97 at work I
>understand Access 2000 (which I have at home) is not much faster.  I
>would say that if you are providing this product to a client, Access is
>not the ideal tool for it, (at least not for this number of records).
>Access has a lot of workarounds to do most things but the advice I have
>received from more experienced users are that its drivers are not
>designed to handle very large loads of information in the speed that you
>or your clients require.
>
>Many of my queries take several minutes to run, it is only that we use
>the Databases internally that speed has never been a major issue, I
>would never dream of supplying a client with an Access Database, as the
>guys have suggested you could use SQL Server as a back end but it would
>seem that you are ultimately moving towards a complete transition to a
>more powerful database, halfway houses are never professional solutions
>in my view. If your solutions are in Microsoft Generally speaking than
>SQL Server is good transition to make.
>
>I would take the advice of the other users below, buy some good books (I
>normally get them from www.Amazon.co.uk were you can look at reviews). I
>have found that it is better to start making transitions early rather
>than keep using quick fixes.
>
>Regds,
>Raheel
>
>
>-----Original Message-----
>From: dcarnley
>Sent: 30 July 2002 15:56
>To: access
>Cc: dcarnley
>Subject: [access] Re: 500,000 + RECORDS
>
>
>When you write an application in Access, either as an MDB or ADP, you
>are
>using VBA, Visual Basic for Applications.  This is a slimmed-down
>version of
>VB.  Therefore, VB is more powerful (whatever that means:)  Some things
>are
>easier but you have fewer options and some things are more difficult.
>
>You can move your data to SQL Server without converting your MDB to an
>ADP.
>I would love to write all day about how to do that but I am very busy
>today
>with my job (ack!). Get 2 books on it and read'em both!
>
>David
>
>-----Original Message-----
>From: George Oro [mailto:george@c...]
>Sent: Tuesday, July 30, 2002 8:09 AM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>Hi Amy,
>
>Many thanks for your further explanation, I guess this is the time for
>me to
>move to Access Project.
>
>By the way, which is more powerful Access Project or VB?
>
>Cheers,
>George
>
>
>
>-----Original Message-----
>From: Amy Wyatt [mailto:amyw@c...]
>Sent: Tuesday, July 30, 2002 1:49 PM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>What David means is in SQL you can create views that are basically the
>same as a query in Access. The syntax is a little different but SQL has
>a
>pretty good GUI to create them that is very similar to the grid in
>Access
>Query designer. Once you create a view in SQL you can link it to an
>Access
>front-end just like you link tables. That way the SQL engine will
>actually
>run the query and just return the recordset to the Access front-end.
>
>One thing, and I agree with David on this, when you use Access Projects
>there is not DAO unless you do some add-ins. You can link through ODBC
>and
>in my case this worked well but I am not dealing with quite as large of
>a
>database. If you are not too far along writing code in Access then I
>would
>strongly suggest moving to the Access Project venue. If do have quite a
>bit of code in a regular Access database the you could have quite a bit
>of
>debugging to do.
>
>An excellent book actually comes from out Host (Wrox). SQL Server 2000
>Programming: Beginning Edition is very well done and I learned a great
>deal from it about SQL programming.
>
>Good luck,
>
>Amy
>
> > David,
>
>This tips is very good, but what do you mean by "move all of your
>access queries to views and then treat them like linked tables in
>access"?
>Can you elaborate this furthermore?
>
>
>Many thanks,
>George
>
>
>-----Original Message-----
>From: Carnley, Dave [mailto:dcarnley@a...]
>Sent: Monday, July 29, 2002 7:22 PM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>Buy a book!
>
>Whole careers are based on doing this correctly.
>
>But here are some rules of thumb:
>
>->make the back-end server do all the work.  This means move all of your
>access queries to views and then treat them like linked tables in
>access.
>So
>you probably have to re-work just a little any objects the use them.
>
>->do all CRUD using stored procedures.  That is, instead of having the
>statement
>
>adoConn.exec ("INSERT INTO tbl_X (x1,x2) VALUES ('X1','X2')")
>
>you should have
>
>adoConn.Exec ("exec sp_tblX_insert ('x1','x2')")
>
>where sp_tblX_insert is a stored procedure that does the actual insert.
>This looks trivial for this example but it will make your life SO MUCH
>EASIER in the long run.
>
>-> Control your indexing.  Look at all the forms and reports in your
>application and determine what fields they use to query the db.  Index
>only
>those fields.  Remove any extra indices.
>
>...I could list 100 more...
>
>David
>
>PS - for you youngsters out there CRUD is Create Read Update Delete   ;
>)
>
>
>
>
>
>-----Original Message-----
>From: George Oro [mailto:george@c...]
>Sent: Monday, July 29, 2002 9:56 AM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>Hi Amy,
>
>Thanks for the tips, actually I'm not so familiar using SQL as my
>back-end.
>I remember I upsized my tables to SQL using the Upsizing
>Wizards and it doesn't makes any difference and it seems its getting
>more
>slower.
>
>What is the best way to use the SQL as a back-end?
>
>
>Cheers,
>George
>
>
>-----Original Message-----
>From: Amy Wyatt [mailto:amyw@c...]
>Sent: Monday, July 29, 2002 3:56 PM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>Just an observation from my experience with Access 2000. When it comes
>to
>forms, it is just basically slower than it's predicessor (Access97) and
>if
>you add a variable of Accessing the data from a Server you slow down a
>lot
>more. My moving the data into SQL has increased the speed of my
>application immensely but I am not dealing with that number of records.
>However, if you move it into SQL and then create some basic queries as
>Views that simplify the subsequent queries in Access then you may
>increase
>your speed also. Try creating a view in SQL for the qryMembers1 and
>linking it to your Access front end. Remember, a query within a query
>takes twice the time to run because the impedded query has to run first
>before the other query can even start to run. SQL has a faster engine
>for
>processing queries.
>
>You may also try using the filtering options rather than actually
>creating
>and running the query. I have also found this to increase speed
>(especially in reports) than the query itself (don't ask me why).
>
>Anyway,  good luck.
>
>Amy
>
> > Hi Guys,
>
>We have a prospective client now having the above number of records as
>their contacts. When I tried to create a temp table having
>the above records to test on our standard Membership database which we
>are
>trying to sell to them, my Members Information form is
>opening after 30 seconds which is not acceptable. I set up my Records
>Source as mentioned below:
>
>tblMembers - My main table
>qryMembers1(Source tblMembers) - To filter the members only having
>fldStatus="Active"
>qryMembers2 (Source qryMembers1)- I set the below criteria to a couple
>of
>fields (10)for my search box on my form (AfterUpdate 
>Me.Requery)
>Like "*" &[Forms]![frmMembers]![txtSearchBox]& "*"
>
>Basically the qryMembers2 is the source of my Members Information form.
>I
>know my setup is the reason why my form is opening very
>slow, because when I point directly my record source to the tblMembers,
>my
>form is opening after 4 seconds which is quite
>acceptable. The problem is I need that criteria which I defined to
>qryMembers1 and 2 and the data might grow and grow. So if the
>records will double then that 4 sec. will be 8 sec. which is not
>acceptable now.
>
>Only my objective is to open my Membership form in normal speed having
>the
>above numbers of records plus the criteria which I
>defined from my queries.
>
>I tried to upsize to SQL using the wizard and it is more slow.......
>
>
>Any idea or suggestion is highly appreciated...
>
>P.S.
>My Members Information form having 20-30 fields and 4 subforms.
>My Front/Back end is all in access2k under win2K.
>
>
>Cheers,
>
>George
>
>
>
>
>
>
>
>
>
>
>Visit our website at http://www.ubswarburg.com
>
>This message contains confidential information and is intended only
>for the individual named.  If you are not the named addressee you
>should not disseminate, distribute or copy this e-mail.  Please
>notify the sender immediately by e-mail if you have received this
>e-mail by mistake and delete this e-mail from your system.
>
>E-mail transmission cannot be guaranteed to be secure or error-free
>as information could be intercepted, corrupted, lost, destroyed,
>arrive late or incomplete, or contain viruses.  The sender therefore
>does not accept liability for any errors or omissions in the contents
>of this message which arise as a result of e-mail transmission.  If
>verification is required please request a hard-copy version.  This
>message is provided for informational purposes and should not be
>construed as a solicitation or offer to buy or sell any securities or
>related financial instruments.
>
>




_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com

	Me too got the fear that I might not get the program work when i suddenly switch to SQL

Enzo

-----Original Message-----
From: George Oro [mailto:george@c...]
Sent: Wednesday, July 31, 2002 12:53 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


Hi Beth,

You make me happy today, can you elaborate your suggestion as far as you can?

At the moment my setup is, I just upsize all my table to SQL using the upsizing wizard. My access front is on my machine and
back-end on the server.

Hope you got time to spend.

Thanks is advance,
George


-----Original Message-----
From: Beth Moffitt [mailto:BethMoffitt@i...]
Sent: Tuesday, July 30, 2002 11:21 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS


We have a very robust application using Access, SQL or Oracle back-ends with
Access as the front-end.

You might try splitting out your database into libraries and have the forms
and code in one database which runs on your local drive, attach to tables in
another database (Access, SQL or Oracle) which is on the network, and any
other libraries (security files, ini files or other databases which feed
yours) on the network.

You would be surprised at how efficiently Access can perform if created
correctly.  We have clients with millions of record rows (in SQL & Oracle),
but the front end is all the same.  Access.

Regards,

Beth
INI, Inc.
xxx.xxx.xxxx  x110
www.iniinc.com

-----Original Message-----
From: Scott Eisenreich [mailto:seisenreich@h...]
Sent: Tuesday, July 30, 2002 2:15 PM
To: Access
Subject: [access] Re: 500,000 + RECORDS

Hi George,

I had a similar problem with an Access database I developed for my company.
It worked ok until we got it on the network at which time it bogged-down so
much that it became useless! One problem with Access forms is that in order
to have data available, they need to query the entire table that they are
based on. My solution was to re-write the front end in XML/HTML. I agree
with Raheel, Access is not built for use over a network connection! You can
use the built-in functionality of Access 2000 to create HTML data access
pages. These do not query the table, but create a view or "snapshot" of the
data. They are still connected to the table, and run much faster that the
traditional Access forms. You can recreate the functionality of your forms
by using javascript or vbscript (vbscript cannot be viewed in some browser
however). You can find a lot of information on-line (make google your home
page!) and there are several books out there on scripting.

Scott


>From: Raheel.Hennan@u...
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: 500,000 + RECORDS
>Date: Tue, 30 Jul 2002 16:13:34 +0100
>
>I don't feel I can add much to what these more experience guys below
>have said, I have used Access for similar sizes of information to that
>which you are using.  Whilst I have been using Access 97 at work I
>understand Access 2000 (which I have at home) is not much faster.  I
>would say that if you are providing this product to a client, Access is
>not the ideal tool for it, (at least not for this number of records).
>Access has a lot of workarounds to do most things but the advice I have
>received from more experienced users are that its drivers are not
>designed to handle very large loads of information in the speed that you
>or your clients require.
>
>Many of my queries take several minutes to run, it is only that we use
>the Databases internally that speed has never been a major issue, I
>would never dream of supplying a client with an Access Database, as the
>guys have suggested you could use SQL Server as a back end but it would
>seem that you are ultimately moving towards a complete transition to a
>more powerful database, halfway houses are never professional solutions
>in my view. If your solutions are in Microsoft Generally speaking than
>SQL Server is good transition to make.
>
>I would take the advice of the other users below, buy some good books (I
>normally get them from www.Amazon.co.uk were you can look at reviews). I
>have found that it is better to start making transitions early rather
>than keep using quick fixes.
>
>Regds,
>Raheel
>
>
>-----Original Message-----
>From: dcarnley
>Sent: 30 July 2002 15:56
>To: access
>Cc: dcarnley
>Subject: [access] Re: 500,000 + RECORDS
>
>
>When you write an application in Access, either as an MDB or ADP, you
>are
>using VBA, Visual Basic for Applications.  This is a slimmed-down
>version of
>VB.  Therefore, VB is more powerful (whatever that means:)  Some things
>are
>easier but you have fewer options and some things are more difficult.
>
>You can move your data to SQL Server without converting your MDB to an
>ADP.
>I would love to write all day about how to do that but I am very busy
>today
>with my job (ack!). Get 2 books on it and read'em both!
>
>David
>
>-----Original Message-----
>From: George Oro [mailto:george@c...]
>Sent: Tuesday, July 30, 2002 8:09 AM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>Hi Amy,
>
>Many thanks for your further explanation, I guess this is the time for
>me to
>move to Access Project.
>
>By the way, which is more powerful Access Project or VB?
>
>Cheers,
>George
>
>
>
>-----Original Message-----
>From: Amy Wyatt [mailto:amyw@c...]
>Sent: Tuesday, July 30, 2002 1:49 PM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>What David means is in SQL you can create views that are basically the
>same as a query in Access. The syntax is a little different but SQL has
>a
>pretty good GUI to create them that is very similar to the grid in
>Access
>Query designer. Once you create a view in SQL you can link it to an
>Access
>front-end just like you link tables. That way the SQL engine will
>actually
>run the query and just return the recordset to the Access front-end.
>
>One thing, and I agree with David on this, when you use Access Projects
>there is not DAO unless you do some add-ins. You can link through ODBC
>and
>in my case this worked well but I am not dealing with quite as large of
>a
>database. If you are not too far along writing code in Access then I
>would
>strongly suggest moving to the Access Project venue. If do have quite a
>bit of code in a regular Access database the you could have quite a bit
>of
>debugging to do.
>
>An excellent book actually comes from out Host (Wrox). SQL Server 2000
>Programming: Beginning Edition is very well done and I learned a great
>deal from it about SQL programming.
>
>Good luck,
>
>Amy
>
> > David,
>
>This tips is very good, but what do you mean by "move all of your
>access queries to views and then treat them like linked tables in
>access"?
>Can you elaborate this furthermore?
>
>
>Many thanks,
>George
>
>
>-----Original Message-----
>From: Carnley, Dave [mailto:dcarnley@a...]
>Sent: Monday, July 29, 2002 7:22 PM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>Buy a book!
>
>Whole careers are based on doing this correctly.
>
>But here are some rules of thumb:
>
>->make the back-end server do all the work.  This means move all of your
>access queries to views and then treat them like linked tables in
>access.
>So
>you probably have to re-work just a little any objects the use them.
>
>->do all CRUD using stored procedures.  That is, instead of having the
>statement
>
>adoConn.exec ("INSERT INTO tbl_X (x1,x2) VALUES ('X1','X2')")
>
>you should have
>
>adoConn.Exec ("exec sp_tblX_insert ('x1','x2')")
>
>where sp_tblX_insert is a stored procedure that does the actual insert.
>This looks trivial for this example but it will make your life SO MUCH
>EASIER in the long run.
>
>-> Control your indexing.  Look at all the forms and reports in your
>application and determine what fields they use to query the db.  Index
>only
>those fields.  Remove any extra indices.
>
>...I could list 100 more...
>
>David
>
>PS - for you youngsters out there CRUD is Create Read Update Delete   ;
>)
>
>
>
>
>
>-----Original Message-----
>From: George Oro [mailto:george@c...]
>Sent: Monday, July 29, 2002 9:56 AM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>Hi Amy,
>
>Thanks for the tips, actually I'm not so familiar using SQL as my
>back-end.
>I remember I upsized my tables to SQL using the Upsizing
>Wizards and it doesn't makes any difference and it seems its getting
>more
>slower.
>
>What is the best way to use the SQL as a back-end?
>
>
>Cheers,
>George
>
>
>-----Original Message-----
>From: Amy Wyatt [mailto:amyw@c...]
>Sent: Monday, July 29, 2002 3:56 PM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>
>Just an observation from my experience with Access 2000. When it comes
>to
>forms, it is just basically slower than it's predicessor (Access97) and
>if
>you add a variable of Accessing the data from a Server you slow down a
>lot
>more. My moving the data into SQL has increased the speed of my
>application immensely but I am not dealing with that number of records.
>However, if you move it into SQL and then create some basic queries as
>Views that simplify the subsequent queries in Access then you may
>increase
>your speed also. Try creating a view in SQL for the qryMembers1 and
>linking it to your Access front end. Remember, a query within a query
>takes twice the time to run because the impedded query has to run first
>before the other query can even start to run. SQL has a faster engine
>for
>processing queries.
>
>You may also try using the filtering options rather than actually
>creating
>and running the query. I have also found this to increase speed
>(especially in reports) than the query itself (don't ask me why).
>
>Anyway,  good luck.
>
>Amy
>
> > Hi Guys,
>
>We have a prospective client now having the above number of records as
>their contacts. When I tried to create a temp table having
>the above records to test on our standard Membership database which we
>are
>trying to sell to them, my Members Information form is
>opening after 30 seconds which is not acceptable. I set up my Records
>Source as mentioned below:
>
>tblMembers - My main table
>qryMembers1(Source tblMembers) - To filter the members only having
>fldStatus="Active"
>qryMembers2 (Source qryMembers1)- I set the below criteria to a couple
>of
>fields (10)for my search box on my form (AfterUpdate 
>Me.Requery)
>Like "*" &[Forms]![frmMembers]![txtSearchBox]& "*"
>
>Basically the qryMembers2 is the source of my Members Information form.
>I
>know my setup is the reason why my form is opening very
>slow, because when I point directly my record source to the tblMembers,
>my
>form is opening after 4 seconds which is quite
>acceptable. The problem is I need that criteria which I defined to
>qryMembers1 and 2 and the data might grow and grow. So if the
>records will double then that 4 sec. will be 8 sec. which is not
>acceptable now.
>
>Only my objective is to open my Membership form in normal speed having
>the
>above numbers of records plus the criteria which I
>defined from my queries.
>
>I tried to upsize to SQL using the wizard and it is more slow.......
>
>
>Any idea or suggestion is highly appreciated...
>
>P.S.
>My Members Information form having 20-30 fields and 4 subforms.
>My Front/Back end is all in access2k under win2K.
>
>
>Cheers,
>
>George
>
>
>
>
>
>
>
>
>
>
>Visit our website at http://www.ubswarburg.com
>
>This message contains confidential information and is intended only
>for the individual named.  If you are not the named addressee you
>should not disseminate, distribute or copy this e-mail.  Please
>notify the sender immediately by e-mail if you have received this
>e-mail by mistake and delete this e-mail from your system.
>
>E-mail transmission cannot be guaranteed to be secure or error-free
>as information could be intercepted, corrupted, lost, destroyed,
>arrive late or incomplete, or contain viruses.  The sender therefore
>does not accept liability for any errors or omissions in the contents
>of this message which arise as a result of e-mail transmission.  If
>verification is required please request a hard-copy version.  This
>message is provided for informational purposes and should not be
>construed as a solicitation or offer to buy or sell any securities or
>related financial instruments.
>
>




_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com

Are you accessing (no pun intended) the application from one office or 
across a T1 line? The reason I asked is that I did most of what you 
suggested & the performance was fine locally, but our remote offices were 
experiencing 4-8 min for the first form to load! The HTML version loads in 
1-20 seconds.


>From: Beth Moffitt <BethMoffitt@i...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: 500,000 + RECORDS
>Date: Tue, 30 Jul 2002 14:20:44 -0500
>
>We have a very robust application using Access, SQL or Oracle back-ends 
>with
>Access as the front-end.
>
>You might try splitting out your database into libraries and have the forms
>and code in one database which runs on your local drive, attach to tables 
>in
>another database (Access, SQL or Oracle) which is on the network, and any
>other libraries (security files, ini files or other databases which feed
>yours) on the network.
>
>You would be surprised at how efficiently Access can perform if created
>correctly.  We have clients with millions of record rows (in SQL & Oracle),
>but the front end is all the same.  Access.
>
>Regards,
>
>Beth
>INI, Inc.
>xxx.xxx.xxxx  x110
>www.iniinc.com
>
>-----Original Message-----
>From: Scott Eisenreich [mailto:seisenreich@h...]
>Sent: Tuesday, July 30, 2002 2:15 PM
>To: Access
>Subject: [access] Re: 500,000 + RECORDS
>
>Hi George,
>
>I had a similar problem with an Access database I developed for my company.
>It worked ok until we got it on the network at which time it bogged-down so
>much that it became useless! One problem with Access forms is that in order
>to have data available, they need to query the entire table that they are
>based on. My solution was to re-write the front end in XML/HTML. I agree
>with Raheel, Access is not built for use over a network connection! You can
>use the built-in functionality of Access 2000 to create HTML data access
>pages. These do not query the table, but create a view or "snapshot" of the
>data. They are still connected to the table, and run much faster that the
>traditional Access forms. You can recreate the functionality of your forms
>by using javascript or vbscript (vbscript cannot be viewed in some browser
>however). You can find a lot of information on-line (make google your home
>page!) and there are several books out there on scripting.
>
>Scott
>
>
> >From: Raheel.Hennan@u...
> >Reply-To: "Access" <access@p...>
> >To: "Access" <access@p...>
> >Subject: [access] Re: 500,000 + RECORDS
> >Date: Tue, 30 Jul 2002 16:13:34 +0100
> >
> >I don't feel I can add much to what these more experience guys below
> >have said, I have used Access for similar sizes of information to that
> >which you are using.  Whilst I have been using Access 97 at work I
> >understand Access 2000 (which I have at home) is not much faster.  I
> >would say that if you are providing this product to a client, Access is
> >not the ideal tool for it, (at least not for this number of records).
> >Access has a lot of workarounds to do most things but the advice I have
> >received from more experienced users are that its drivers are not
> >designed to handle very large loads of information in the speed that you
> >or your clients require.
> >
> >Many of my queries take several minutes to run, it is only that we use
> >the Databases internally that speed has never been a major issue, I
> >would never dream of supplying a client with an Access Database, as the
> >guys have suggested you could use SQL Server as a back end but it would
> >seem that you are ultimately moving towards a complete transition to a
> >more powerful database, halfway houses are never professional solutions
> >in my view. If your solutions are in Microsoft Generally speaking than
> >SQL Server is good transition to make.
> >
> >I would take the advice of the other users below, buy some good books (I
> >normally get them from www.Amazon.co.uk were you can look at reviews). I
> >have found that it is better to sta