Question about loading a database

[PaulB]

Hi

I've made an application that runs over a LAN. It uses MS Access 97 databases.

These databases will eventually get to about 15000 records.

I'm using DAO in code and a few ADO controls.

Does the whole database have to be copied into the computers memory for it to be used?

What sort of speed can I expect from the DAO when accessing a database this big?

I use code similar to this...

Dim db As Database
Dim rs As Recordset
Dim recordsource As String

recordsource = "SELECT * FROM stock WHERE cid = " & cID & " AND aid = " & aID

Set db = OpenDatabase(recordPath)
Set rs = db.OpenRecordset(recordsource)

If rs.BOF = False Then... blah blah blah

endif

rs.Close
db.Close


Does an Access database only send the data requested?

Also, I've seen on the MSDN somthing about pre-loading the database so that there isn't a pause when the app is first run. How can this be achieved?

This is quite urgent and I appreciate any help offered.

[gravyboy]

Why not use

Do While Not rs.EOF

your code


Loop

As for speed reckon an hour to do 8-10k records, it can depend on what you are doing in the Do ... Loop AND on the level of network traffic.

------------------
Matt G
Either mattg@gravyboy.fsnet.co.uk] or matt.graveston@centrica.co.uk

[This message has been edited by gravyboy (edited 02-04-2000).]

[JHausmann]

To improve speed, you will want to create local Db's that are linked to the network database. Access will send structure and index information to the client if the DB doesn't exist locally...

[PaulB]

Hi

Thanks for replying.

Is there aweb site or a book that will have information about linking a local DB to the one on the server?

[Clunietp]

Won't all the records have to be sent over the LAN to the client on every query anyways if a linked table is used?

[JHausmann]

From "The Hitchhiker's Guide to VB and SQL Server (6th Edition, page 222):

"The downside to this approach [opening SQL server databases] is the expense incurred when the database object is first opened - the data definition language queries can take considerable time to perform. And this isn't a one time penalty, either: additional DDL queries are required when the recordset object is created against nonattached tables."

"Access 97 Unleashed" advises (on page 893) that "linked tables are faster, more convenient and more powerful" than opening tables directly in code.

[PaulB]

Hi

Thanks for the help so far.

Could someone please tell me how to use linked tables.

Thanks!

[Clunietp]

Interesting.....thanks John

I didn't need citations, I just wondered what a local DB did for you, thanks though! :)

[JHausmann]

Originally posted by Clunietp:
Interesting.....thanks John

I didn't need citations, I just wondered what a local DB did for you, thanks though! :)


PaulB was asking for 'em, I figured I'd kill two birds...

[JHausmann]

Originally posted by PaulB:
Hi

Thanks for the help so far.

Could someone please tell me how to use linked tables.

Thanks!

You use them like any other table, the only difference is that the data _really_ resides somewhere else...