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

 

[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

 

[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.