473,322 Members | 1,540 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Printing a report from SQL Stored Procedure

8
A bit of advice would be good, I'm a bit of a newbie but I'm getting better.

When I first set about this project I was advised to use a Union Query as the Record Source for my Report. On my test system I ran the SQL server local to the client front end. Everything seemed fine, however now I have migrated the Database on to the Live Server, I can no longer print reports.

I'm sure I need to use Adodb but I'm unsure of the Syntax.

Here's my code.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmdPrint_Click()
  3.  
  4. ' Prints FactSheet to be given to Foster Children
  5.  
  6. Dim db                        As DAO.Database
  7. Dim qd                        As DAO.QueryDef
  8.  
  9. Dim stReportName As String
  10. Dim StrCarerID As String
  11.  
  12. Me.txtFosterID.SetFocus
  13.  
  14. StrCarerID = Me.txtFosterID.text
  15.  
  16. Val (StrCarerID)
  17.  
  18. ' Run Stored Procedure
  19.  
  20. If Len(StrCarerID) Then
  21.     Set db = DBEngine(0)(0)
  22.     Set qd = db.QueryDefs("qryViewFosterFamily")
  23.  
  24.     qd.sql = "EXEC dbo.sp_ViewFosterFamily" & " " & StrCarerID
  25.  
  26.     Debug.Print qd.sql
  27.  
  28.     db.QueryDefs.Refresh
  29.  
  30. stReportName = "rptFactSheet"
  31.  
  32. DoCmd.OpenReport stReportName, acViewPreview, , , acWindowNormal
  33.  
  34.     Set qd = Nothing
  35.     Set db = Nothing
  36.  
  37. Else
  38.     MsgBox "No Family Details Entered....", vbCritical, "Print Error"
  39. End If
  40.  
  41. End Sub
  42.  
  43.  
Feb 12 '07 #1
5 2844
NeoPa
32,556 Expert Mod 16PB
Is qryViewFosterFamily defined as a Pass-Thru query?
Can you run it manually from the Access Front-End (After updating it with example SQL of the form you're trying to use in the code)?
Feb 12 '07 #2
feets
8
Is qryViewFosterFamily defined as a Pass-Thru query?
Can you run it manually from the Access Front-End (After updating it with example SQL of the form you're trying to use in the code)?

Yes qryViewFosterFamily is a Pass Thru not a Union query. Apologies for the misinformation.
Feb 12 '07 #3
NeoPa
32,556 Expert Mod 16PB
And the other question...
Feb 12 '07 #4
feets
8
And the other question...
Running the Pass thru query as is, from the front end

Expand|Select|Wrap|Line Numbers
  1. EXEC dbo.sp_ViewFosterFamily 123456
results in a odbc connection error to sql server. One thing that did show up is that the odbc connection to - details were wrong eg

domain\ServerName\instance

Is it possible to change them?
Feb 12 '07 #5
NeoPa
32,556 Expert Mod 16PB
In that case you need to get that sorted out before you ask any Access questions based on it.
SQL Server can have various different types of security in the connection. You need to get this resolved, either by your SQL Server administrator or, failing that, ask a question in the SQL Server forum.
I expect you don't actually have any Access problems as such, but when you've got this resolved, check and post again if you still think you have Access problems.
Feb 12 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Khalid Hussain | last post by:
hi Could someone please help me in following: I need to update Policy status base upon commencement date and then print with new Policy status. I written a stored procedure to update the policy....
3
by: CSDunn | last post by:
Hello, I have a situation with MS Access 2000 in which I need to display report data in spreadsheet orientation (much like a datasheet view for a form). If you think of the report in terms of what...
1
by: Lauren Quantrell | last post by:
I have read the newsgroups and see this is a common issue but I saw no resolution for it: I have an Access2K frotn end and SQL Server 2K backend. In access, I create a temp table using code in a...
8
by: Mark Flippin | last post by:
This is for a reporting problem using: Access 2000 SQL Server 2000 Both at SP3 I've a stored procedure in SQL Server 2000 which builds a result set from a disparate set of tables, utilizing...
8
by: lauren quantrell | last post by:
When I open an Access form I can have no recordset specified, then in the form's OnOpen event I can do something like: Me.paramaters = "@SomeColumn = 22)" Me.recordsource = "dbo.sproc123" But I...
2
by: Bob | last post by:
I'm new to Access projects and SQL server and am not a veteran VB programmer. There's a cry for help! I'm attempting to print the current form on screen by using a command button which the user...
12
by: Bill Nguyen | last post by:
What's the VB syntax to run the CR report using the following SP? I use CrystalreportViewer and ReportDocument. Thanks Bill Here's the SP in SQLserver 2K: CREATE proc mysp_ReportSubmission...
18
by: Brett | last post by:
I have an ASP.NET page that displays work orders in a GridView. In that GridView is a checkbox column. When the user clicks a "Print" button, I create a report, using the .NET Framework printing...
0
it0ny
by: it0ny | last post by:
Hi guys, thanks I am fairly new to this forum so I hope I chose the right place to post this question. I try to make my program printout a deposit's report. I created a class to store the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.