| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2004-10-26 : 14:45:21
|
| Does any body know a good example of an Access app that uses SPs from SQL Server to feed its reports?I have several Access apps using DAO dynamic sql as Record Source with linked tables. I want to switch them to ADO/SPs. On my proof of concept mockup, I created a stored proc, got the data into ADO 2.7 in vba, but failed to bind the recordset to the report.I tried to avoid mapping field by field. This is in Access 97. Is DAO capable of the task? Thanks! |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-26 : 14:48:49
|
| not sure exactly what you are asking or what isn't working for you, but use a pass-through query in Access 97. the pass-through query has a connection string that connects you to SQL. and then the contents of that query are regular T-SQL statements, including calling a stored proc if you like.Access does not parse or care about the contents of a pass-through query -- it simply passes the text to the ODBC provider.- Jeff |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2004-10-26 : 15:04:52
|
| Jeff,Thanks for your reply.I did try using DAO QueryDef passthrough in the following syntax (incomplete), but haven't make it work.Your reply telling me that there is a way to accomplish stored proc call through QueryDef, that is great. I will keep trying then. If you happen to know of a good example, please share with me. I haven't found one.Public Function OpenSQL(spName As String, spParam as string) As Recordset Dim qdTmp As QueryDef Set qdTmp = currentdb.CreateQueryDef("") qdTmp.Connect = "ODBC;DSN=XXX" qdTmp.ReturnsRecords = True qdTmp.SQL = spName qdTmp.Parameters --add spParam qdTmp.Execute Set OpenSQL = qdTmp.OpenRecordset qdTmp.Close Set qdTmp = Nothing End If End Function |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-26 : 15:29:57
|
| no, there is no VB. you don't open a recordset and pass it to a report. The report itself should already be bound to the pass-trhough query, and that shouldn't change at any point.1. Create a pass through query (let's call it "Q1")2. Set the connect property of Q1 to your ODBC connection; make sure "returns records" is true3. Write your T-SQL statement in Q1 to execute the stored proc4. Create your report5. bind the report to Q1All of the above is 1-time design stuff; you don't do it dynamically and you don't need VB. Use the regular Access GUI to accomplish the above steps. Then, you can just open the report and then it runs. No need to use VB to open a recordset or anything like that -- that's what the report does when you run it.if you need to edit the SQL property of the querydef (i.e., to dynamically add parameter values to your T-SQL string) then that would be done in VB, before opening the report.Does this help?- Jeff |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2004-10-26 : 16:16:03
|
| Ok, let me see I understand you correctly.Instead of supplying to Q1 a "Select fld1 from tbl1", I could use "spOne", spOne as a sp in the server that returns fld1 from tbl1.If I want to pass in parameters along with the sp name, which is the case here, then I need to use vba as my queryDef code has done.I guess I just want to get a fuzzy warming feeling that I am doing the right thing. Thanks! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-26 : 16:27:04
|
| Try it ! You'll get warm and fuzzy if/when it works!- Jeff |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2004-10-27 : 09:27:24
|
| Now that I got my recordset through DAO queryDef from sql server stored proc, I could not find a way to make my Access Report to display the data. None of following works. How can I bind them together? Do I have to give up Access for Crystal Report, or do I have to give up the stored proc side of it and stick with the enbedded sql?Private Sub Report_Open(Cancel As Integer)...Me.RecordSource = OpenSQLsp(spTest).NameMe.RecordSource = OpenSQLsp(spTest).Fields(0)Me.Text0 = OpenSQLsp(spTest).Fields(0)Me.Label1 = OpenSQLsp(spTest).Fields(0) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-27 : 10:48:33
|
| Did you follow the steps I gave you? There is no VB.Have you written reports in Access before?- Jeff |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2004-10-27 : 11:07:58
|
| Jeff,Thank you for your reply again!Yes, I did write some reports in access, and I did follow your previous 5 steps post on pass through query to get stored proc. But the goal I try to achieve here is also exactly as you stated in that reply.“if you need to edit the SQL property of the querydef (i.e., to dynamically add parameter values to your T-SQL string) then that would be done in VB, before opening the report.”I want to build a reusable component that will take spName, and paramList (number of them and data type of them changes) as input, and return DAO or ADO recordsets, then bind them to various reports.I have done the VB(A) part. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-27 : 11:21:25
|
| then that's all you do. you edit the T-SQL in the query def and nothing else. Then just open the report. there should be no need to use VB(A) to set textboxes and re-bind everything and all that in your report.What is this "OpenSQLsp" function I see? what are you using that for? Why are you setting the RecordSouce of the report twice? your reports' recordsource should not ever change. it is bound to the pass-through query. only the pass-through query ITSELF changes, the SQL property, if you need to. the property is a simple text string that is either the name of a table or a query, or a SQL statement. in this case, you just set it (at design time) to the name of the query that is your pass through query. this never changes. re-read this paragraph a few times if needed until it makes sense.again,1) create pass-through query. call it "Q1". 2) set the connect, write T-SQL in Q1 to call a stored proc and return results. Put in dummy params if you need to at this stage.3) create report. set the RecordSource to "Q1"4) design your report. Run it, preview it, save it. make sure it worksThe 4 stpes mentioned do NOT include any VB. do not proceed to write ANY vb code if the above 4 steps do not produce a report for you. don't even open up the VB designer screen at any point in time to confuse yourself. the above 4 is done using the standard regular Access GUI. Once you get that working, if you need to dybamically alter the paramters passed, just edit the SQL property of the query before opening the report, using VB. YOu do not edit/change ANY report properties. the report stays bound to the same query. the query itself changes.Does this help/make sense? - Jeff |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2004-10-27 : 12:45:09
|
| Ok, I got it. Thanks! |
|
|
|
|
Stored Proc feed Access report