MS Access ADO

[vbc22]

Hi,

After reading this online article I was wondering, if I create an Access mdb file and use ADO code to retrieve recordsets of data, is it still pulling all the data as noted in the article?

Article URL:

http://my.advisor.com/Articles.nsf/dp/6847CE0F237AC9378825681E005C9BC3

Quote from Article:

Many people think placing an Access database on a file server magically transforms it into a client-server database. This is far from true! Imagine a customer table with 100,000 rows and the Access application end-user wants to retrieve the five customers located in Alaska. People don't realize that when data is stored in an Access database, all 100,000 rows may be returned to the workstation depending on the design of the table. If you modify this scenario so you use Access as a front-end to data stored in a SQL Server database, you guarantee only the five rows you need are returned.

Thing is, I want to create an Access database file that has it's tables linked to an Oracle database. It woudl use ADO connections to retrieve recordsets of data. I'm pretty sure when I specify restrictions on a query, it will only return those specified amounts of data.

But what I'm not so sure of is that I also want to incorporate a link to tables from another Access database on a file server. If I query that Access database from the file server using ADO to return a disconnected data set, am I going to be pulling an entire table?

I know about ADP files, but am curious about the scenario above. Any advice or comments would be appreciated.

Regards.

 

[alvechurchdata]

If I query that Access database from the file server using ADO to return a disconnected data set, am I going to be pulling an entire table?

I think that it in tbe worst case you could be pulling the entire mdb. All the tables are stored in the same mdb file and I can't see how ADO can know which bit of that file it needs to retrieve without reading the file. Even if the query can make use of an index ADO still has to find that index in the mdb.

All this is hypothesis on my part. I don't know if ADO uses some clever coding to pull a particular few bytes out of an mdb file but I'd be fascinated to know more.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[vbc22]

I've been meaning to respond to this thread but been too busy. Anyways, I appreciate your comment alvechurchdata.

...

Here's something...

In the Locals window when debugging I notice that when I extract data from a query specifying results like this:

    rst.Open "SELECT * FROM tLocationsData WHERE LOCID=10426", _
            conn, adOpenStatic, adLockReadOnly

the RecordCount only shows 1; as well, when I insert a Debug.Print rst.GetString statement afterwards, it only prints out 1 record.

I have the test database on my computer that performed the above query on a separate database on another computer on the network, and it took less than an eye-blink to bring it across. In the query table there are close to 3,000 records.

I mean, in that instance, I didn't pull 3,000 did I? :-D

Perhaps the article concerns bound forms or careless querying that result in poor performances. I don't know.

Just something academic I'm curious about.

 

[JerryKlmns]

Access database reads the whole table tLocationsData and executes the query localy on your PC to get the resultset. That means there is a transfer of all records to your PC and then applling filtering criteria of the WHERE clause. That's why they dont think Access is capable of client/server applications. They even claim that it should be NOT taken as a RDBMS! It is just an office file with functionality of creating User Interface for something of Database with some Security features.
BUT! It is a tool to use that fits your needs (and many others')....

 

[vbc22]

Thanks for the information...and I guess that's the same with what the article I posted above wrote.

Answers my question then, thanks.