Connect to SQL server.

[Guoqi Zheng]

Dear Sir,

I have an Access project connected to a remote Ms Sql 2000 server. I have an
form inside Access which enable people to edit order information.

This is an online shop application, the records of order table keep
increasing. Each time, when I open the Access form to edit order
information, it take Access project application some time to get all the
order information into local disk. It seems that Access project get all the
order table information into local disk because it is always calculating
when I am opening the form.

Now, I am afraid that when I have 100000 records on that order table, it
will take Access project application a long time to get the data and open
the form.

Do I make myself clear here? Any suggestion for this problem or this is not
a problem???

Thanks in advanced.


--
Met vriendelijke groet,

Guoqi Zheng
Tel: +31 (0) 23 5343545
http://www.meetholland.com

 

[Guoqi Zheng]

Thanks for your reply.

I used Access as client, all the data are in Ms SQL 2000 server.

--
Met vriendelijke groet,

Guoqi Zheng
Tel: +31 (0) 23 5343545
http://www.meetholland.com

 

[Arvin Meyer]

You probably don't need old order information so write a stored procedure on
the orders table(s) to return only those records which you need. A date
stamp will probably be sufficient to get the latest records.

Make sure you include the index in the dataset so that you will be able to
update the table.

If it is still slow, you'll need to do some optimizing on the server to get
the required data quickly. Folks in the SQL-Server admin groups ought to be
able to help you with that. Try at: microsoft.public.sqlserver.server
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Tony Toews]

from previous experience, when dealing with very important applications
or data, it's wise to move away from Access to something like maybe MS
SQL server w/.NET if you want to stay on MS platform.
Rubbish.

I've been told by MANY experienced programmers that Access IS NOT good
for mission critical applications....

Then many experienced programmers don't have a clue. They've only
heard about how Access isn't good for mission critical apps. And
they're somewhat correct. Storing the data in an Access backend is
not a good idea for mission critical apps. But using Access as a
front end to SQL Server works very well.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Albert D. Kallal]

from previous experience, when dealing with very important applications
or data, it's wise to move away from Access to something like maybe MS
SQL server w/.NET if you want to stay on MS platform.
If you want to save $, consider Coldfusion.
I've been told by MANY experienced programmers that Access IS NOT good
for mission critical applications....

Sheer rubbish.

Obviously, you don't have any previous experience. In fact, the original
poster SPECIFICALLY said they are not strong the data in access, but are
using sql server anyway. I can't imagine you publicly stating that using
sql-server is no good?

So, I don't know what you been eating for breakfast, but your statement in
this context is one of sheer silliness.

I think most people would agree for mission critical applications you don't
want to use the JET database engine, but that is not necessary ms-access.

Ms-access is a fine client to ms-sql server, or even Oracle for that matter.
If there is a problem with the application, then the people who set it up
are to blame. (ie: the Oracle, or sql-server would hold the blame....not
ms-access).

I mean, at least your statement would hold SOME ground if the user was
talking about storing some data in ms-access...but the user is not. Perahps
you don't understand what client to server means?

 

[Guoqi Zheng]

Sirs, I would agree with what you said, I am not looking at the above
mentioned post.

But do you have any suggestion for my problem? Do you understand what is my
problem??

Thanks a lot.

 

[Guoqi Zheng]

Thanks, That is correct. The stupid situation is that our employees would
like to be able to search all the records within Access form. Maybe they
need to find an order of two years ago.

--
Met vriendelijke groet,

Guoqi Zheng
Tel: +31 (0) 23 5343545
http://www.meetholland.com

 

[Albert D. Kallal]

Well, we just wanted to correct that poster.

I mean, fact is, you spent time and money to setup and use sql server. sql
server and ms-access is fine combination.

You really should not be experience any performance problems here.

The #1 recommend I can make is that you should NEVER simply load a form
without regards to the records that you tell the form to load. I mean, when
you use a instant teller bank machine, it would be silly to load every
customer in to the machine!

So, if the user needs to edit a invoice number, or whatever, then built a
nice prompt screen that "asks" the user for the invoice number. You then
launch the form with a where clause to the ONE record.

Regardless, your question should not be how to get the 100,000 records to
load faster, but how to only load the records you need. If you design your
application to only work with, and only load the records you need, then you
can easily with files in the 1 million record range.

A file size of 100,000 records is quite small. In fact, in without sql
server, a 4 or 5 users JET application in ms-access can EASILY handle a file
with 100,000 records (it is quite small).

So, just make sure you don't load up the order form with more then one
record. In fact, load up the order form with just the one customer (there of
cause can, and will be many records in the sub-forms that you no doubt have
used).

So, in practice, for all appcltions I write, I NEVER simply load up a form
attached to a table, but ALWAYS present the user with some type of search
form.

Here is a example of what I mean:

http://www.attcanada.net/~kallal.msn/Search/index.html