linked tables? big remote database

[The Boondock Saint]

Ive been able to link access 2000 to the database which runs my website...
its a mysql database i think ...

anyway.. one of the tables had alot of data in it... over 5000 entries.....
and its about 6meg...

im only on dial up... so i was wondering.... is it possible.. for me to say
type in one of the entries ID's and then it will only draw down that
information from the server... so i can update that listing?

? any ideas if it can be done?

 

[Albert D.Kallal]

im only on dial up... so i was wondering.... is it possible.. for me to
say type in one of the entries ID's and then it will only draw down that
information from the server... so i can update that listing?

? any ideas if it can be done?

Not only should this be done, but I think if you took a poll of people, from
kindergarten to my old grandmother and asked the following question:


Should a instant teller machines download all of the accounts first and THEN
ask for a account number?

or

Should you ask the user what account number, and THEN download the one
account?

So, what you are asking makes perfect sense. And, in fact, any well designed
application, be it written c++, vb, or a software developers tool like
ms-access should always prompt the user for what record to edit. To "lazy"
just open up a huge table to a form is rather silly, and think this concept
is a basic premise of all software (ie: you don't load everthing, but "ask"
the user what they want first, and THEN load the one thing).

Your best bet is to create some type of "prompt" screen that allows you to
search by lastname, or id, or whatever you need (allow a few options). You
then display the search "results" as a list, and then let the user "edit"
one of the records.

I been developing software steady for 20 years now, and virtually EVERY
single application has the following logic

loop
ask for invoice number, or exit loop
if invoice number existing then
edit invoice
end if
repeat

So, be it a web application, or a ms-access form, you NEVER load up the form
(or the web page) with the 5000 records and THEN ask for the invoice. You
simply need to prompt for the one record, and that way you will only
transfer one record into the form.

Take a look at the following screen shots of a good search screen in
ms-access, and one that is minimal bandwidth:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

By the way, 5000 records in a ms-access is considering VERY tiny. In fact, a
ms-access database with 50,000 is rather small, and when you got such tiny
files of 50,000 records you will find that response time is rather instant
for just about any operation that needs to find a record and edit it. In
your case, however, you are talking about a very slow connection. How slow?

One thing to keep in mind here is that you are using a very slow connection.
So, please read the following article on using ms-access over slow
connections:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html


If you build a prompt screen, and ask for the "one" record, then a dial up
is possible, but you are on a VERY limited connection.

dim strInvoiceNum as string

strInvoiceNum = inputbox("what invoice to edit")
if strInvoiceNum <> "" then
docmd.Openform "frmInvoiceEdit",,,"invoiceNum = '" & strInvocienum & "'"
end if

The above will launch the form "frmInvoiceEdit", and will load only the ONE
rocord for editing.

So, you can write less then 10 lines of code to "prompt", and load the form
to ONE roecrd....

 

[Guest]

Hi Albert,

Just a few comments that I think are useful for all...

1.) Make sure to use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

Otherwise, your code might reference misspelled varialble names
(strInvocienum).

2.) The line of code with a corrected variable name:
docmd.Openform "frmInvoiceEdit",,,"invoiceNum = '" & strInvocienum & "'"

is valid for a text-based invoice number. For a numeric invoice number, use
this instead:

DoCmd.openForm "frmInvoiceEdit", , , "invoiceNum = " & strInvoiceNum
or, with a named argument:

DoCmd.openForm "frmInvoiceEdit", WhereCondition:="invoiceNum = " &
strInvoiceNum

(You'll probably also want to change the prefix of the variable from "str"
to "lng").

3.) Most importantly, this line of code will not prevent a table scan unless
the invoiceNum field is properly indexed. I just did a quickie test using the
JET ShowPlan registry flag to prove this to myself. I think it is too easy
for folks who have developing software for many years to forget to add this
crucial detail.

Tom
____________________________________________

:

<snip>
If you build a prompt screen, and ask for the "one" record, then a dial up
is possible, but you are on a VERY limited connection.

dim strInvoiceNum as string

strInvoiceNum = inputbox("what invoice to edit")
if strInvoiceNum <> "" then
docmd.Openform "frmInvoiceEdit",,,"invoiceNum = '" & strInvocienum & "'"
end if

The above will launch the form "frmInvoiceEdit", and will load only the ONE
rocord for editing.

So, you can write less then 10 lines of code to "prompt", and load the form
to ONE roecrd....