does a record string like the following only pull the results from SQL Server

Posted on 2008-11-05
Last Modified: 2010-03-19
I use Microsoft Access (not a project) as the front end and SQL Server Express 2005 as the data source.  I am trying to speed up my data searches by only retrieiving the filtered information from the SQL Server rather than access pulling all of the data from the table to access and then filtering it.  Am I correct that this code will only pull the correct record from the SQL Server or will it pull the entire table and then filter the record?  Thanks.
Dim cn As ADODB.Connection

   Dim strConnection As String

 strConnection = "ODBC;DSN=qtdsn;TRUSTED_CONNECTION = YES;DATABASE=upsize5sql"

 Set cn = New ADODB.Connection

   With cn

      .Provider = "MSDASQL"

      .Properties("Data Source").Value = strConnection


   End With

   Dim rs As ADODB.Recordset

   Set rs = New ADODB.Recordset

  Set rs = New ADODB.Recordset

   With rs

    Set .ActiveConnection = cn

  .source = "select privacy from tblcustomer where socialsecuritynumber = " & Forms.paymentinputinformation.SSN

    .LockType = adLockOptimistic

    .CursorType = adOpenKeyset

    .CursorLocation = adUseClient


   End With

Open in new window

Question by:garyoallen
    LVL 18

    Expert Comment

    That should work but I think that you'll need single quotes around the form field value:

    Chr$(39) & Forms.paymentinputinformation.SSN & Chr$(39)
    LVL 69

    Accepted Solution

    >>Am I correct that this code will only pull the correct record from the SQL Server or will it pull the entire table and then filter the record?

    Yes you are. SQL Server has an engine that is running (normally on a server) that receives the query, parse it, execute it and return only rows that are the result of the request.

    When you store an Access database on a server folder, there is no engine on that server that can do that job and that's why the complete table is returned and filtered locally.
    LVL 44

    Assisted Solution

    by:Leigh Purvis
    Agreed, as I understand that SSNs, like NI No's, are alphanumeric and hence will be of some Char type which will require quote delimitation.
    I should perhaps make clear though that Jet databases (often referred to as an Access database) don't necessarily load an entire table's worth of data to return records (incase that's the impression you take away from this thread).
    As emoreau rightly says - the Jet engine doesn't run on the server but on the client.  However that doesn't mean that it has to read entire tables.  A properly indexed table would see such requests reading only the index pages in the MDB file to locate the required record - and the single data page containing that record would then be read (fetched over the wire).
    If you're searching on an non-indexed table (tisk tisk) then the engine will have to load the actual pages of record data.  But that would be a practice you'd also want to avoid a server based engine having to perform (the difference/advantage being that it doesn't have to first send the data over the wire as a first step - but it's still making the engine have to work harder - if not the network).
    Another point is to do with cursor types.
    You've requested a Keyset cursor type - however you've specified a Client side cursor.  The resulting recordset will, in fact, have a Static cursor type rather than Keyset.
    ADO replaces types and lock types depending upon what is supported without raising any error.
    It should make little to no different to what you subsequently do in this case - but it can play an important role in data requests and is worth bearing in mind for the future.  (i.e. IMO it's always good to request exactly what you'll get).
    As a final teeny point, though a definite speed difference isn't always forthcoming - it's more common to use an OLEDB provider with ADO.  Although an ODBC, DSN method as you've used is perfectly viable.
    LVL 44

    Expert Comment

    Couldn't have said it better myself;-) - as if!  That post is worth printing and hanging on the wall.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now