The only way to get complete control over the query for ODBC is to create them as pass-through queries.
In that mode, Access will not touch them and the SQL will be passed verbatim to the server (so your queries will need to be written in that server's particular SQL syntax or you'll get errors).
In ODBC mode, Access will decompose queries that are bound to controls into batches so it never returns more results than it need to display (that is what you see: it will decompose queries based on the primary key to return specific records only).
So you have to be careful of what you wish: if you optimise your queries using pass-through, you will get less calls, but all the data will be returned.
On the other hand, if you leave Access do the querying for you, it may be chattier, but the net performance may be better since less data may be returned more quickly.
You need to test both and choose what works best depending on the exact query and how it is used.
SQL Server Management Studio allows you to create multiple versions of queries where you can see the SQL text and results simultaneously.
You can use dotted-notation to refer to tables in separate databases on the same server like:
SELECT * FROM database.schema.table
If you need to query data from multiple servers, you can create linked servers in SQL Server that provide all the capabilities of an Access front-end. This allows you to use 4-part dotted-notation like:
SELECT * FROM server.database.schema.table
For example, this code creates 2 Databases on the local SQL Server, with 2 tables that contain a single record each. The SELECT
at the end joins data from the 2 tables into a single output:
CREATE DATABASE Test1 ON PRIMARY
(NAME = 'Test1_Data'
, FILENAME = 'C:\Temp\Test1_data.mdf'
, SIZE = 10MB
, MAXSIZE = UNLIMITED
, FILEGROWTH = 10MB
)
LOG ON
(NAME = 'Test1_Log'
, FILENAME = 'C:\Temp\Test1_log.mdf'
, SIZE = 10MB
, MAXSIZE = UNLIMITED
, FILEGROWTH = 10MB
);
CREATE DATABASE Test2 ON PRIMARY
(NAME = 'Test2_Data'
, FILENAME = 'C:\Temp\Test2_data.mdf'
, SIZE = 10MB
, MAXSIZE = UNLIMITED
, FILEGROWTH = 10MB
)
LOG ON
(NAME = 'Test2_Log'
, FILENAME = 'C:\Temp\Test2_log.mdf'
, SIZE = 10MB
, MAXSIZE = UNLIMITED
, FILEGROWTH = 10MB
);
GO
USE Test1;
GO
CREATE TABLE TestTable1
(
ID INT NOT NULL PRIMARY KEY CONSTRAINT PK_TestTable1 IDENTITY(1,1)
, TestText nvarchar(255)
);
INSERT INTO TestTable1 (TestText) VALUES ('This is table 1 in database 1');
USE Test2;
GO
CREATE TABLE TestTable2
(
ID INT NOT NULL PRIMARY KEY CONSTRAINT PK_TestTable2 IDENTITY(1,1)
, TestText nvarchar(255)
);
INSERT INTO TestTable2 (TestText) VALUES ('This is table 2 in database 2');
SELECT * FROM Test1.dbo.TestTable1
UNION ALL
SELECT * FROM Test2.dbo.TestTable2;
The output is:
ID TestText
1 This is table 1 in database 1
1 This is table 2 in database 2
Best Answer
Yes, Access on the client machine will connect directly to the linked tables located on SQL Server (or whatever ODBC back-end you're using). If you're dealing with forms and reports bound directly to these linked tables, it shouldn't cause any major performance problems. If you've written more complex queries within Access, then performance can depend on how well those have been optimized.
We've got a number of small Access "apps" running against SQL Server. They're great for when you just need to bang out a simple CRUD tool for a few people in an hour or two. Haven't had any real performance problems with them, though occasionally we run into a lock being held longer than it should.
My advice would be to take all the tables stored in that Access back-end and migrate them to SQL Server as well. Shared Access data files tend to be awful for performance and reliability.