Report on SQL Server View..via Connection Strin

[sacacompany]

can i have a report in access based on sql server view that is being open/retrieved bis connection string

 

[theDBguy]

I think so. Did you try it? What happened?

 

[sacacompany]

i m using following code but is didnt work...
Private Sub Report_Load()
Dim rs As Object
Dim strSQL As String

' Connect to the database
ConnectDB

' Create a recordset object
Set rs = CreateObject("ADODB.Recordset")

' Set the SQL query
strSQL = "SELECT * FROM MC_PrescriptionQ ;"

' Open the recordset
rs.Open strSQL, DBCon, 1, 3

' Check if any records are found
If Not rs.EOF Then
' Set the record source of the report to the SQL query
Me.RecordSource = strSQL
End If

' Close the recordset
rs.Close

' Clean up
Set rs = Nothing
CloseDB
End Sub

 

[theDBguy]

"Didn't work" meaning? Did you get an error? Something else?

 

[cheekybuddha]

' ...
' Check if any records are found
If Not rs.EOF Then
' Set the record source of the report to the SQL query
Me.RecordSource = strSQL
End If
' ...

This bit won't work as you think, if it were to work at all.

Setting the .RecordSource requires linked tables.

You would want something more along the lines of:

' ...
' Check if any records are found
If Not rs.EOF Then
' Set the recordset of the report to the recordset object
  Set Me.Recordset = rs
End If
' ...

However, I'm not 100% sure that Access reports can use ADODB.Recordset's as their recordset.

If not, you would have to use a DAO.Recordset. I imagine the code would look something like:

' ...
' Set the SQL query
strSQL = "SELECT * FROM MC_PrescriptionQ ;"

' Create a temporary query
With CurrentDb.CreateQueryDef(vbNullString)
  .Connect = DBcon
  .SQL = strSQL
  .ReturnsRecords = True
' Open the recordset
  Set rs = .OpenRecordset
End With

' Check if any records are found
If Not rs.EOF Then
' Set the record source of the report to the SQL query
  Set Me.Recordset = rs
End If
' ...

(NB. all aircode!)

 

[cheekybuddha]

Is there any reason why you don't just use a linked table?

 

[ComputerVersteher]

You can't set the Report.RecordSet property unless it is an ADP Database.

Workaround:
Link the view(MC_PrescriptionQ) then your code can set the Report.RecordSource property (or use a DAO Pass-Through query or a temp table with data from ADO query).

 

[sacacompany]

You can't set the Report.RecordSet property unless it is an ADP Database.

Workaround:
Link the view(MC_PrescriptionQ) then your code can set the Report.RecordSource property (or use a DAO Pass-Through query or a temp table with data from ADO query).

ADP not available...temp table work around looks good...can u please elaborate ....thanks

 

[cheekybuddha]

Still curious as to why you are avoiding linked tables in this instance - it would make building your report much easier.

 

[sacacompany]

well i m using a connection string to connect whenever need be and close DB connection as soon as i m done. Previously i used linked tables but users have to wait long for queries and forms and report to open or load. But your point looks valid and i m now considering to use linked tables for querries and reports and keep the forms unbound and use connection string for CRUD

 

[Pat Hartman]

Is the BE on a LAN? If so, you were doing something very wrong.

 

[sacacompany]

Is the BE on a LAN? If so, you were doing something very wrong.

yes the BE is on LAN SQL server ...please guide me to correct what you feel i m doing incorrectly

 

[Pat Hartman]

Forms/Reports should never be bound directly to tables or to queries that have no selection criteria. All queries should select the fewest rows and columns needed. This allows the server to do the heavy lifting. This method allows you to use bound forms/reports and even with multi-million row tables achieve acceptable performance. Unbound forms/reports and passthrough queries are not necessary and take a lot more work. Access makes every effort to pass through every query. If you don't understand how this process works, you can interfere with it and force Access to request all data from all tables in a join and perform the join locally. You do not want this to happen. So, you need to be aware of what functions are equivalent between SQL Server and Access and so can safely be used in queries. What you need to avoid is UDF's and VBA functions, especially in the Where clause. Neither of those types of queries can be processed by the server so they always result in excess data transfer over the LAN. It is safe to use those query types in the Select clause because Access can process them locally after the small amount of data has been returned from the server.

 

[ebs17]

strSQL = "SELECT * FROM MC_PrescriptionQ ;"

rs.Open strSQL, DBCon, 1, 3
  
   ' Check if any records are found
   If Not rs.EOF Then
       ' Set the record source of the report to the SQL query
       Me.RecordSource = strSQL
   End If

This is particularly bad. You load unfiltered data TWICE. The ADODB recordset has nothing to do with the form recordset.