473,698 Members | 2,313 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access Frontend and SQL Backend Database Question

Background:
I have recently started to look at converting an access backend into
an SQL backend.

Question:
By default, when using a query in access does the SQL server only send
the records requested from the query OR does SQL still send everything
and the filtering is done by Access?

If the latter, how to I get SQL to do the hard work and not access?

Thanks
Matt

May 21 '07 #1
2 4028
Lynx101 wrote:
Background:
I have recently started to look at converting an access backend into
an SQL backend.

Question:
By default, when using a query in access does the SQL server only send
the records requested from the query OR does SQL still send everything
and the filtering is done by Access?
"Still"? That suggests you think Access/Jet sends everything. Access/Jet does
not and neither will a SQL Server back end.
If the latter, how to I get SQL to do the hard work and not access?
A standard Access query against an ODBC linked table will usually send most of
the request to the server, but the amount of work done on the server varies
depending on the structure of the query. To guarantee 100% work by the server
you use a passthrough query. Only problem is that passthrough queries are
always read-only.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
May 21 '07 #2
Question:
By default, when using a query in access does the SQL server only send
the records requested from the query OR does SQL still send everything
and the filtering is done by Access?
yes, by default, only the one record is transmitted. So often, a form is
loaded directly against a large table without filters.

In the case of a jet back end on a network, or sql server, if you restrict
the record to be loaded, then ONLY that record is transferred. This applies
to sql server, or a *even* standard back end file share mdb file.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
May 21 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

17
4088
by: Jon Ole Hedne | last post by:
I have worked on this problem some hours now (read many-many...), and I can't solve it: In vba-code I create a table with Connection.Execute, and add some data to it. This table is saved in the backend-database. After generating this table, I want to open a report based on a query with data from this temporary table. In the report's OnOpen event I set the report's record source to the query pointing to the temporary table. If I run...
1
2348
by: matt bourke | last post by:
Hi, Can you please tell me what the advantage is of using an access backend with a VB frontend OVER access as a frontend and backend. M
4
2809
by: Squirrel | last post by:
I've developed an Access 2002 database which will be deployed with the backend on a server and frontend on the users' PCs. I've now been advised that new employees will be given laptops with Access 2003 installed and that the server software will not be updated. Also some of the users are using Access 2000 and some Access 2002. My question is - will this work? There is a lack of IT support in this department - they've contracted with...
13
7483
by: Manuel Lopez | last post by:
I have a puzzling form timer problem that I didn't experience prior to Access 2003 (though I'm not sure access 2003 is to blame). Here's the situation: a computer has two access 2003 databases on it, a frontend and a backend. Case 1: If vba code on the frontend updates many rows (360,000) on the backend, a form's timer event (from the frontend) will stop firing until the user gives the form focus. (Note that the update itself always...
42
5631
by: PC Datasheet | last post by:
I have zero experience with using a SQL database for a backend and Access for a frontend. I have some questions: 1. Does an SQL database have tables? 2. How does Access connect to the data in an SQL database? Linking, importing, or ??? 3. Earlier today there was a thread regarding DAO and ADO. In the thread it was said that ADO is very useful when the backend is a SQL database. Could someone explain that?
2
1465
by: Gary | last post by:
I have the backend database on a network drive. There are 3 employees that access this backend database through their respective frontend databases (located on their respective C drive). One of these employees has a laptop. Q1. The laptop provides a problem in that each time it is connected to the network, he cannot access the backend. The database window still has the linked tables. The only remedy is to simply deleted the linked tables...
1
3097
by: odrap | last post by:
I have a database written in MS Access 2.O that i'm intend to convert to MS ACCESS 2007. The database is a multiuser application which consist of a application database as the frontend and a Data Database as the backend. In my MS Access 2.0 program the code is present to make the link between the data database on the backend and the application database on the frontend. Both computers are part of a local network. I know that MS Access 2007...
4
2343
by: ThePhenix | last post by:
Hi everybody, I have recently been doing a conversion for my boss for a access 2000 database (both front and backend) to a MSSQL backend. I finished the conversion today (as quite a lot of the code / queries ran slow due to access running the queries locally rather than on the server). And tested it on my and my boss's machine with no problems so he gave the go ahead to update everybody to our new mssql 2000 backend with the modified...
2
15621
nico5038
by: nico5038 | last post by:
Access 2007 Linkedtable manager refuses to relink tables having a field with the "Attachment" datatype. Problem: When placing a split database in another folder, the Linked table manager should be used to relink the tables. The Linkedtable manager does however refuse to relink tables having an "Attachment" datatype field. Symptoms: Open the Linkedtable manager and select a linked table with an "Attachment" datatype field. Now try to...
2
3735
by: Steve | last post by:
I have zero experience with ODBC. If I have an Access frontend connected to a SQL Database using ODBC, are the tables connected like a frontend/backend Access database where the the tables you see in the frontend are only "copies" of the tables in the backend? Or ar the tables "real" tables connected to the SQL tables in some manner? If the latter, can an Access table have more fields than the SQL table it is linked to? The reason for my...
0
8676
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9161
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9029
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8897
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8867
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7732
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4370
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3050
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2332
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.