Best Way to get ODBC Data

[igendreau]

Here is my issue: I am building an application in Access, and I need to import some basic data from an ODBC source. The database is Oracle, and is hosted remotely. Now, I know that our bandwidth as well as the host's bandwidth plays a huge factor in record retrieval. I just want to see if my code is bringing back the records in the most efficient way possible.

In this example, the user enters a value for txtProjectNo, and upon clicking a button, I go out to the ODBC table (which I've setup as a linked table in my Tables tab), retrieve the record, and drop the value in a text field:

Dim rsCustomer As ADODB.Recordset
Set rsCustomer = New ADODB.Recordset
rsCustomer.Open "SELECT CustomName FROM tblCustomers WHERE
PROJECT_NO='" & txtProjectNo & "'", CurrentProject.Connection,
adOpenStatic, adLockOptimistic
txtCustomerNo = rsCustomer.Fields("CUSTOMER_NO")
rsCustomer.Close

Is there a more efficient way that I should be handling this? The records, even as simple as this, pull over so slowly that I can't use this process. And it is important that I avoid needing a scheduled "data import" & using local data if possible... Thanks for the help!

[OTWarrior]

how many records are we talking about in the datasource? Are they in multiple tables on the server?

One thing I have found that speeds up major queries (especially when there are alot of linked fields) is to keep any lookup tables local, and just pull in the data that changes. (for example, if you had a list of locations such as England, USA, France, you wouldn't need to pull that from the server as it wouldn't change that often (if at all), so you could keep a copy locally)

Without knowing the amount of data you are pulling, or it's structure, it is hard to think of a way of it being more efficient. Hopefully my suggestion is of use to you, if not then would you please give more information.