runtime error 2593

[matthooper]

Hello folks

I am trying to use ado in an access report with no luck.

Private Sub Report_Open(Cancel As Integer)
' Global Vars to be used through code
Dim sQl As String
 
' Declaire remote connection
Dim rmcn As ADODB.Connection, rmrs As ADODB.Recordset
 
' Create connection and open connection - remote
Set rmcn = New ADODB.Connection
 
With rmcn
    .Provider = "Microsoft.Access.OLEDB.10.0"
    .Properties("Data Provider").Value = "SQLOLEDB"
    .Properties("Data Source").Value = "server\sqlexpress"
    .Properties("User ID").Value = "user"
    .Properties("Password").Value = "password"
    .Open
End With
 
' Open recordset and run sql
Set rmrs = New ADODB.Recordset
 
sQl = "SELECT * FROM link.db.dbo.table where field 'value';"
 
With rmrs
    Set .ActiveConnection = rmcn
    .Source = sQl
    .LockType = dbOpenDynaset
    .CursorType = adOpenKeyset
    .Open
 
End With
Set Me.RecordSource = rmrs
 
 
End Sub

There is nothing wrong with accessing the database but trying to set the record source of the report just doen't work

Any thought?

[Rabbit]

Hello folks

I am trying to use ado in an access report with no luck.

Private Sub Report_Open(Cancel As Integer)
' Global Vars to be used through code
Dim sQl As String
 
' Declaire remote connection
Dim rmcn As ADODB.Connection, rmrs As ADODB.Recordset
 
' Create connection and open connection - remote
Set rmcn = New ADODB.Connection
 
With rmcn
    .Provider = "Microsoft.Access.OLEDB.10.0"
    .Properties("Data Provider").Value = "SQLOLEDB"
    .Properties("Data Source").Value = "server\sqlexpress"
    .Properties("User ID").Value = "user"
    .Properties("Password").Value = "password"
    .Open
End With
 
' Open recordset and run sql
Set rmrs = New ADODB.Recordset
 
sQl = "SELECT * FROM link.db.dbo.table where field 'value';"
 
With rmrs
    Set .ActiveConnection = rmcn
    .Source = sQl
    .LockType = dbOpenDynaset
    .CursorType = adOpenKeyset
    .Open
 
End With
Set Me.RecordSource = rmrs
 
 
End Sub

There is nothing wrong with accessing the database but trying to set the record source of the report just doen't work

Any thought?

The recordsource can't be set to a recordset, it has to be set a table/query or in this case, the SQL string.

[NeoPa]

As Rabbit says, you can't set it to a connection.
Click in the RecordSource part of 'Set Me.RecordSource = rmrs' and press F1 and the Help will explain that the attribute is not an object but a string variable (& how you can use it).

[matthooper]

I think i am going to have to create a local table with the data that i have just queried and use that as a datasource.

[NeoPa]

I think i am going to have to create a local table with the data that i have just queried and use that as a datasource.

You say that you've just queried the data. Is it not possible to use that query, or a derivative thereof, to drive the report?