Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 4th, 2007, 06:29 PM
Authorized User
Join Date: Jan 2007
Location: , , United Kingdom.
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access Reports from SQL backend

OK, I'll tell you what i've got so far. On opening an Access Report I am running a Stored Procedure (a Select Query). Now my Recordset fields have values, but I cannot associate them to the controls I have placed on my Report. What am I doing wrong?? For example


Here is the code when the Report opens, any help would be much appreciated

Private Sub Report_Open(Cancel As Integer)

' Retrieve family details by entering FamilyID

Dim Rs As ADODB.Recordset ' Recordset produces a virtual table from SELECT query

Dim FolderName, PicName As String
Dim strFamilyName, strAddress, strPostCode As String


strFamilyID = Forms!frmWfsFamilyUpdate!txtFosterID.text

' Open a SQL Connection, to use a SELECT stored procedure.


Set cmd = New ADODB.Command
cmd.ActiveConnection = Con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "selectFamilyDetails"

' CarerID variable inputted into
cmd.Parameters.Append cmd.CreateParameter("carerID", adVarChar, adParamInput, 8, strFamilyID)

' Run Stored Procedure
Set Rs = cmd.Execute

' If Recordset is not at end of file. Assign Report Controls to RS fieldNames

If Not Rs.EOF Then
    strFamilyName = Rs.Fields(2)
    strAddress = Rs.Fields(3)
    strAddress = strAddress & "," & Rs.Fields(4)
    strAddress = strAddress & "," & Rs.Fields(5)
    strPostCode = Rs.Fields(6)
    strPostCode = strPostCode & " " & Rs.Fields(7)
    strPhoneNo = Rs.Fields(8)

End If

strFamilyName = txtFamilyName
strAddress = txtAddress

End Sub

  #2 (permalink)  
Old January 5th, 2007, 08:36 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts


   If you are using Access for OLAP, I would suggest pulling the data you have here and putting it in a local table, and then running your report based off the local table.

   Steps would be:

Create table with fields from your recordset
Create Delete query to clean out table at the beginning of each report pull

Then your code:
SetWarnings to False
Run Delete Query
SetWarnings to True

Open connection to SQL
Open Recordset to SQL
Open Connection to local table
Open recordset on local table

Parse through recordset and post each line to the local table, like:

Do Until rs.EOF
   rsLocal("FamilyName") = Rs.Fields(2)
   rsLocal("Address1") = Rs.Fields(3)
   rsLocal("Address2") = Rs.Fields(4)
   rsLocal("Address3") = Rs.Fields(5)
   rsLocal("PostCode1") = Rs.Fields(6)
   rsLocal("PostCode2") = Rs.Fields(7)
   rsLocal("PhoneNo") = Rs.Fields(8)

Then launch the report. The report should be bound to the local table.



Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Crystal reports from access to sql server sunbagga Pro VB Databases 9 July 16th, 2007 05:57 AM
Locking in Access DB linked to SQL Server backend gibbers Access 3 April 24th, 2007 03:20 PM
Access database/SQL Server backend Question vbJupiter Access VBA 1 October 6th, 2006 07:59 PM
ms access form as criteria on sql server backend ottos13 Access 1 September 13th, 2006 12:14 PM
Access MDB with SQL Backend ashg657 Access 2 July 18th, 2006 12:54 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.