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

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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Chr$(39) & Forms.paymentinputinformation.SSN & Chr$(39)
Éric MoreauSenior .Net ConsultantCommented:
>>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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leigh PurvisDatabase DeveloperCommented:
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.
Couldn't have said it better myself;-) - as if!  That post is worth printing and hanging on the wall.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.