SQL statement

[dendic]

Using Access front end and SQL backend. Can anybody tell me if the statement below will quiery at the server and not pass the whole table for access to query. If it doesn't query at the server what code do I use to force SQL to run the query.

Dim Conn As ADODB.Connection
Dim rcdConsignee As New ADODB.Recordset
Dim strSQL As String
Set Conn = CurrentProject.Connection
Set rcdConsignee = New ADODB.Recordset
strSQL = "Select * from consignees WHERE [consigneekey] = " & Me.ConsigneeKey
rcdConsignee.Open strSQL, Conn, adOpenKeyset, adLockOptimistic

Thank you

 

[djj55]

Not sure the answer to your question (I have coded pass throughs before) but what I have used the most is a pass-through query.

Search that and it might help.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[djj55]

Found this just after I posted:

    Dim conn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim Para As ADODB.Parameter
    
    conn.ConnectionString = strConn
    conn.Open
    cmd.CommandTimeout = 600
    cmd.CommandText = "usp_MyProc"
    cmd.CommandType = adCmdStoredProc
    cmd.ActiveConnection = conn
    Set Para = cmd.CreateParameter("@strPK", adVarChar, adParamInput, 10)
    cmd.Parameters.Append Para
    Para.Value = Me.txtAccessField 
    cmd.Execute

It runs a stored procedure so modifying it to run a SQL query should not be hard.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[dendic]

I also use pass through queries but what I need to know if this code will force the server to do the statment and not access.

 

[philhege]

You're connected to SQL, right? You're passing an execute command to the SQL instance. The work is done on the SQL server.

To test, fire up SQL Profiler, limit the output of the trace to the user you employ in your connection string, and see what statements are processed.

-------++NO CARRIER++-------