creating a report from sql server data

#1 (**permalink**) 28-Jul-2006, 08:12 AM

I created an access front-end to a sql server database and I'm stuck
trying to generate a report via a event procedure. I purposely am not
using linked tables and instead connect to the DB using a ADO
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10390-creating-a-report-sql-server-data.html
connection string. I cant figure out how to base a report on a query
where the tables are not linked. I want to be able to use a ADO
connection string to query the db and base a report on it. How do I do
this?

Any ideas would be extreamly appreciated!!!

Thanks

Seth

*

#2 (**permalink**) 28-Jul-2006, 08:12 AM

This is an example of how I create a report in an Access Data Project
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10390
that uses SQL server 2000 as back end, this is in the Report Open
event, and I think CountsCombined is a function or stored procedure. I
construct the string using user selected values in a custom form, and
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10390
then call up the report passing the string as a parameter. Then in the
reports on open event I get the paramter and set it to Me.RecordSource:

strRecordSource = "Exec CountsCombinedDescrNoSumChild '" &
Me![BeginDate] & "','" & Me![ChildCombo] & "','118'"

So basically you use the "Exec" keyword followed by the query name and
comma seperated list of parameters, each one in single quotes. I'm not
sure the details of if the query name has to be the name of an existing
SP, view, or function.