split db forms

[QB]

I have a split db used over a network. I have read one shouldn't set the
form's recordsource to a table.

So what should be done? And then how do you set the sub-form's link
properties since the main form isn't bound?

I want to do things right, but would need to someone to give me a global
prespective so I can try and understand a little better.

QB

 

[Allen Browne]

If the data is in Access (JET) tables (i.e. not linked to some other kind of
database), and you don't expect to have hundreds of thousands of records in
your table, then you can just bind the form to your table.

There may be reasons why you want to use a query (choose only some records,
specify a sort order, create some calculated fields, ...) but the reality is
that Access is very good at figuring out how to handle the data from a
table, even over a network. So, assuming a correctly designed table (has a
primary key, suitably indexed fields, correctly normalized), it doesn't
matter whether you use a query or not.

 

[QB]

Allen,

I have hundreds of thousands of records in my table, so what is the best
approach in this instance?

QB

 

[Allen Browne]

For tables of that size, you probably want to provide your own navigation
mechanism, and load a very limited number of records into the form.

For example, say you have 500k accounts, each with a unique account number.
You bind your form to a SQL statement that initially returns no records,
e.g.:
SELECT Accounts.* FROM Accounts WHERE (False);

In the Form Header, you provide a text box where the user enters the account
number they want to find. If AcctID is a Text field, and this unbound text
box is named txtAcct2Find, its AfterUpdate event procedure does this kind of
thing:

Dim strSql As String
If Me.Dirty Then Me.Dirty = False 'save first
strSql = SELECT Accounts.* FROM Accounts WHERE AcctID = """ &
Me.txtAcct2Find & "";"
'Debug.Print strSql
Me.RecordSource = strSql

If you need to provide multiple search boxes where the user can enter
combinations of criteria, you might want to download this example and see
how it works:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
It actually builds a WHERE clause and applies it as a Filter, but you would
do exactly the same thing to use it in the WHERE clause of the SQL
statement.