"Bill E." <bi********@netscape.netwrote in news:0ebc7349-14be-49e2-b40f-
f7**********@x1g2000prh.googlegroups.com:
Lyle,
Thanks for the information. I'm not using an ADP and I'm not using
application roles, although I was considering it. I agree that it
would be helpful if it were documented somewhere how Access is
handling connections to SQL Server.
I found something else that was strange. It appeared that pass-
through queries were being called twice for some reason. That is to
say that my trace was showing two calls in a row to the same stored
procedure every time. However, when I ran a pass-through query
directly by clicking on the query and running it, the trace showed
only one call to the proc as expected. I thought perhaps the problem
was due to poor form design on my part. To test this, I created a
form with nothing but a single combobox and bound the combobox to the
pass through query. When I opened the form, I looked at my trace and
voila--two calls to the same proc on SQL Server! Do you have any idea
why this would be happening?
Bill
Not really! All I can think is that ODBC can translate a JET/VBA
expression, say, TRIM(x), to its T-SQL equivalent RTRIM(LTRIM(x)) [this is
not a good example] and perhaps, to do so it must visit the SQL Server
twice, once to assess and plan, and a second time to do the work. But I
think it doesn't attempt to do that with Pass Through Queries, it just
[passes them through].
[OT] I often create and maintain an independent ADO connection (as below)
when dealing with SQL Server and use it rather than
CurrentProject.Connection where ever possible. Depending on the version of
Access, one can use an ADO recordset for bound forms, and bypass the normal
methods of binding.
Dim mDefaultConnection As ADODB.Connection
Private Sub OpenConnection(ByRef Connection As ADODB.Connection)
Set Connection = New ADODB.Connection
With Connection
.CursorLocation = adUseClient
.Provider = "sqloledb.1"
With .Properties
.Item("Data Source") = "Place_Holder"
.Item("Initial Catalog") = "Place_Holder"
-----
.Item("PassWord") = "Place_Holder"
.Item("User ID") = "Place_Holder"
-----
OR
-----
..Item("Integrated Security") = "SSPI"
-----
End With
.Open
End With
End Sub
Public Function DefaultConnection() As ADODB.Connection
If mDefaultConnection Is Nothing Then _
OpenConnection mDefaultConnection
Set DefaultConnection = mDefaultConnection
End Function
Public Function NewConnection() As ADODB.Connection
OpenConnection NewConnection
End Function