Using SQL Server Views from Access97

[andyj]

Hi There,

I'm trying to connect to an SQL Server and use a view to return a
recordset, as the query on the client is prety expensive in terms of
records.

I've got a DSN called cent_serv that uses a trusted connection and I
was wondering if I needed to create a workspace, then from that, create
a connection, then from within the connection, open a recordset?

I've tried sending a query string via ADO, but I just get the query
structure returned - no data (recordcount is -1).

I'm just after a way to shift the processing from the client to the
server. Perhaps even looking at exactly how expensive the query is
using the server analyzer.

Any help would be great, as I've been messing with this for a while
now.

Thanks in advance,

Andy

 

[fofa]

Use a PASSThrough query and setup your connection information in the
PASSThrough query. This will run the actual query on the SqlServer and
only return the record set from the criteria selected. As long as the
permissions are correct you can use the view to pull from also.

 

[andyj]

Hi,

Thanks for the suggestion, but I've already tried using the passthrough
(should've mentioned that!) but the performance wasn't much better at
all - but this might be attributable to the fact I'm using a subquery
within the passthrough query.

I'll try making the subquery a passthrough as well come to think of it.


I'll also try using the simple query (no sub query) on a huge table and
see if there are any performance benifits using the passthrough.

off home now

Thanks again,

Andy

 

[Howhiareu]

I don't know if this will solve your problem or not, you decide.

Right click on the tables panel and select 'Link Tables'
In the files of type box, scroll down to the bottom and pick ODBC()

If your DSN is a system DSN it will be on the 'Machine Datasource' tab
Select your DSN and click ok.

You will be given a box to select table. On mine, all of the views i
the sql database are shown in the list. I can pick one and it become
a linked table in Access, available the same as any other table is.

I don't know if that solves your performance problem, it should, I'
like to know what you experience

 

[andyj]

Hi Howhiareu (I'm not - I'm at work)!

Thanks for replying I've got a ton of linked tables at the mo (using
the user DSN). I wrote a simple view and guess what...I can link
straight to it as if it were a table. The queries I'd like to link need
converting to SQL Server speak (rather than Access SQL speak), so I'll
do that this morning.

I'll then put the local Access query (using linked tables) and the SQL
linked view against each other by opening & closing each recordset
against a timer and post the times. I'll post the SQL & VBA also.

Thanks again!

Andy

Getting far too excited for a Monday morning!

 

[andyj]

We have a winner.

The code below ran and gave me this message:
"
Query took 53 seconds to return 58025 rows.

View took 1 seconds to return 58025 rows.
"

Thanks again for your input. I've put my vb & SQL below;


Code
-------------------


Private Sub TEST()

Dim dStrt As Date
Dim dEnd As Date
Dim sMsg As String
Dim rsdata As Recordset

' Start timer
dStrt = Now

' open recordset using local query with linked tables
Set rsdata = currentdb.OpenRecordset("SELECT * FROM qryVisitorLog") ' WHERE [Arrival_Date] = #26-jan-2004#")

' make sure we get a sensible recordcount
rsdata.MoveLast: rsdata.MoveFirst

' stop timer
dEnd = Now

' build message with results
sMsg = "Query took " & DateDiff("S", dStrt, dEnd) & " seconds to return " & rsdata.RecordCount & " rows." & vbCrLf & vbCrLf

'Close recordset
rsdata.Close

'NEXT test

' Start timer
dStrt = Now

Set rsdata = currentdb.OpenRecordset("SELECT * FROM vw_VisitorLog") ' WHERE [Arrival_Date] = #26-jan-2004#")

' make sure we get a sensible recordcount
rsdata.MoveLast: rsdata.MoveFirst

dEnd = Now

sMsg = sMsg & "View took " & DateDiff("S", dStrt, dEnd) & " seconds to return " & rsdata.RecordCount & " rows." & vbCrLf & vbCrLf

rsdata.Close

MsgBox sMsg

If Not rsdata Is Nothing Then Set rsdata = Nothing
End Sub


-------------------


[SQL]
CREATE VIEW dbo.vw_VisitorLog
AS
SELECT dbo.tblVisitorIdentity.LastName + ', '
dbo.tblVisitorIdentity.FirstName AS Name,
dbo.tblVisitorIdentity.CompanyName,
dbo.tblVisitorIdentity.Car_Registration_Number
dbo.tblVisitorIdentity.TimeExpected AS Due,
VisBuild.Building_Name,
Host.Name + Host.location + ' (' + Host.ext + ') ' AS HostName,
dbo.tblVisitorIdentity.Parking, dbo.tblVisitorIdentity.RoomBookingID

dbo.tblVisitorIdentity.Arrival_Date,
dbo.tblVisitorIdentity.VisiterID,
dbo.vw_MeetingRoomLocation.Short_Code,
VisBuild.Building_Code,
dbo.tblVisitorIdentity.TimeIn AS [in],
dbo.tblVisitorIdentity.TimeOut AS out,
dbo.tblVisitorIdentity.HostID,
dbo.tblVisitorIdentity.Memo,
dbo.tblVisitorIdentity.BuildingID,
dbo.tblVisitorIdentity.PassExpiryDate,
Contact.FirstName + ' ' + Contact.LastName + ' ('
RIGHT(Contact.Telephone, 4)
+ ')' AS ToContact,
dbo.tblVisitorIdentity.IDCardNo

FROM dbo.vw_Person Host RIGHT OUTER JOIN
dbo.tblVisitorIdentity ON Host.Employee_ID
dbo.tblVisitorIdentity.HostID LEFT OUTER JOIN
dbo.tblBuilding VisBuild O
dbo.tblVisitorIdentity.BuildingID = VisBuild.Building_ID LEFT OUTE
JOIN
dbo.tblRoomRequest O
dbo.tblVisitorIdentity.RoomBookingID = dbo.tblRoomRequest.Booking_I
LEFT OUTER JOIN
dbo.vw_MeetingRoomLocation O
dbo.tblRoomRequest.Booking_ID = dbo.vw_MeetingRoomLocation.Booking_I
LEFT OUTER JOIN
dbo.tblGeneralUsers Contact O
dbo.tblVisitorIdentity.Contact = Contact.Employee_ID
[/SQL]

The Access query:

[SQL]
SELECT [tblVisitorIdentity].[LastName] & ", "
[tblVisitorIdentity].[FirstName] AS Name
tblVisitorIdentity.CompanyName
tblVisitorIdentity.Car_Registration_Number
tblVisitorIdentity.TimeExpected AS Due, tblBuilding_1.Building_Name
[tblGeneralUsers].[FirstName] & " " & [tblGeneralUsers].[LastName] & "
" & [tblBuilding].[Building_Code] & [tblSide].[Side_Code]
[tblFloor].[Floor_Code] & " (" & Right([tblGeneralUsers].[Telephone],4
& ")" AS Host, tblVisitorIdentity.Parking
tblVisitorIdentity.RoomBookingID, tblVisitorIdentity.Arrival_Date
tblVisitorIdentity.VisiterID, qryMeetingRoomLocation.Short_Code
tblBuilding_1.Building_Code, tblVisitorIdentity.TimeIn AS [in]
tblVisitorIdentity.TimeOut AS out, tblVisitorIdentity.HostID
tblVisitorIdentity.Memo, tblVisitorIdentity.BuildingID
tblVisitorIdentity.PassExpiryDate, [contact].[FirstName] & " "
[contact].[LastName] & " (" & Right([contact].[telephone],4) & ")" A
ToContact, tblVisitorIdentity.IDCardNo
FROM tblGeneralUsers AS Contact RIGHT JOIN (((tblBuilding A
tblBuilding_1 RIGHT JOIN ((((tblBuilding RIGHT JOIN tblGeneralUsers O
tblBuilding.Building_ID = tblGeneralUsers.Building_ID) LEFT JOIN
tblSide ON tblGeneralUsers.Side_ID = tblSide.Side_ID) LEFT JOIN
tblFloor ON tblGeneralUsers.Floor_ID = tblFloor.Floor_ID) RIGHT JOIN
tblVisitorIdentity ON tblGeneralUsers.Employee_ID =
tblVisitorIdentity.HostID) ON tblBuilding_1.Building_ID =
tblVisitorIdentity.BuildingID) LEFT JOIN tblRoomRequest ON
tblVisitorIdentity.RoomBookingID = tblRoomRequest.Booking_ID) LEFT JOIN
qryMeetingRoomLocation ON tblRoomRequest.Booking_ID =
qryMeetingRoomLocation.Booking_ID) ON Contact.Employee_ID =
tblVisitorIdentity.Contact
ORDER BY [tblGeneralUsers].[FirstName] & " " &
[tblGeneralUsers].[LastName] & ", " & [tblBuilding].[Building_Code] &
[tblSide].[Side_Code] & [tblFloor].[Floor_Code] & " (" &
Right([tblGeneralUsers].[Telephone],4) & ")",
qryMeetingRoomLocation.Short_Code;

[/SQL]