ODBC Question

[Bob McClellan]

If I create an ODBC link to all the tables on our cloud based server, will this try to pull all the data down
for all the tables or simply create the link to those tables?

I want to write SQL and will use Select top 1000 Field1, Field2, Field3 from SomeTable and will
never double click a table to open it. The tables are very large and I'm concerned about bringing


production to a halt if I try to pull everything down to my .accdb

So, my question is simply this.. If I create an ODBC Link, does this only create the link which would allow me
to write my queries but not pull any data down to the .accdb?

Hope the question is clear.
Thanks in advance,
..bob

[JoeM]

If you create an ODBC link, and connect to the tables that way, the data never resides in data tables in Access, it stays in the database tables.
Depending on on what this other database is (MS SQL, maybe?), you can also improve efficiency by setting up Views on the SQL Server (SQL queries) and referencing those, or setting up Pass-Through Queries in Access. With Pass-Through Queries, you are actually sending up a SQL statement to the SQL Server, doing the query there, and only returning the results. Otherwise, your query brings all the data from the tables involved down, and does the query on the Access side (which is usually slower, especially if large amounts of data are involved).

[Bob McClellan]

Joe...
Thanks so much.... I just created a pass through query and it worked like a charm.
I used Access.adp's in the past but always used SPs for everything. I never explored using
pass through queries.

It's been a while since I've used Access so... being able to simply create the ODBC connection and
write the SQL is perfect.

Thanks Joe!
Very much appreciate it!
..bob

[JoeM]

You are welcome! Glad it all worked out for you.