Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Bay Area, California, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unbound adp report problems (Access 2000 SR-1)

    Im trying to get a report to work in an "unbound" (no connection specified) Access 2000 adp, which is based on a view (or stored procedure, or SQL statement, I'm not picky here).

    I created the report in a "bound" adp, where it works, then imported it into the unbound adp, where I deleted the report's Record Source (I also deleted each control's Control Source when this didn't work).

    I tried to get the report to display using the same (ADO) mechanism I use to with my unbound forms:

    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn
    cmd.CommandText = "procWhatever"
    cmd.CommandType = adCmdStoredProc
    Set prm = ...
    cmd.Parameters.Append prm

    Set rst = New ADODB.Recordset
    With rst
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmd
    End With

    If the default view is 'Continuous Form', I keep the control's Control Source, and populate it as follows:
    Set Me.Recordset = rst

    This doesn't work at all in the report. Neither does Set Me.RecordSource = rst.
    Neither does Me.RecordSource = rst.Name (<A target="_blank" HREF=http://www.mvps.org/access/reports/rpt0014.htm>http://www.mvps.org/access/reports/rpt0014.htm</A>).

    If the default view is 'Single Form', I delete the control's Control Source (unbound), and populate it by looping through the recordset and the controls collection, and assigning the values accordingly:

    With rst
    If Not .BOF And Not .EOF Then
    For Each ctl In Form.Section(0).Controls (which I change to 'For Each ctl In Report.Controls')
    If ctl.ControlType = acTextBox And ctl.tag <> "x" Then
    ctl.Value = rst(ctl.Name)
    End If
    Next ctl
    End If
    End With

    Again, no such luck in my report (with or without the control's Control Source).

    Can you please point me in the right direction. I can't find anything on this topic searching hi & lo. Much obliged.

    Klaus

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unbound adp report problems (Access 2000 SR-1)

    The problem may be in the way you've created the connection object. You didn't include that here, only the "Set cmd.ActiveConnection = cnn" line. If your connection was set as something like CurrentProject.Connection, it would work in the other project because of the connected SQL Server. In your "unbound" project, you have to specify all the necessary connection information because there is no current connection. Without that, the engine has no idea of where to get the information for the recordset. If you did specify it, then post the code so we can see what might be wrong with it.

    The "Me.RecordSource = rst.Name" method only works with a DAO recordset. ADO recordset's don't have a name property that returns the SQL behind them the way that DAO does.
    Charlotte

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Bay Area, California, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unbound adp report problems (Access 2000 SR-1)

    Charlotte,
    thanks for replying.

    For brevity, I didn't include the mechanism for connecting to the SQL Server db in my post, but I use a global connection string, and a function, OpenConnection() As Boolean, that connects to the server when I open the adp as well as when I populate forms, combo boxes etc..

    Before executing any command on the server, I make sure I have a connection:

    fOK = OpenConnection()
    If not fOK then
    - reconnect
    else
    - here's where I run the code you saw in my original post

    This works well for any forms (single and continuous), or to run any stored procedure, it just doesn't work with my reports. If you want to, I can post some code, but I'm not sure what to include: just the code I use to open the report, or the modules to set up the connection?

    I also left a message on microsoft.public.access.adp.sqlserver, and Andy Baron replied that apparently you cannot use (unbound) reports in an unbound adp, or he wasn't aware of a solution to my problem ("Access 2000 reports don't have a recordset property the way forms do. In Access 2002, you can use a shaped recordset to create a report, but I haven't tried it yet. In Access 2000, I think you'll find that without connecting to SQL Server and setting a record source, you won't get very far in building reports. You may want to look at 2002 if you need this, or use another tool for your reporting.")

    The reason I keep my adp unbound is, it's easier to keep the users away from the server objects.

    I was wondering, if there is a way to programmatically set up the connection to the server (i.e.,complete the steps in the File - Connection dialog using VBA which then should also display the server objects in the db window), run the report against a stored proc or view, then programmatically clear the connection dialog again. This way, I would have a bound adp just when opening the report, and an unbound adp using ADO for the remainder of the session.

    Any thoughts?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unbound adp report problems (Access 2000 SR-1)

    Well, Andy Baron certainly should know, since he's an acknowledged expert on Access. And my apologies, I read the subject, but I had brain fade and forgot we were talking about reports here.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •