Unbound adp report problems (Access 2000 SR-1)

Topic

New Reply

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 (http://www.mvps.org/access/reports/rpt0014.htm).

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

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote