Setting subform recoursource with connection string (SQL server)

[Santiago Gomez]

Hello all,
I am trying to set the recordsource of a subform with vba using a connection
string, but I get an error.
I think I have the wrong syntax on the last line. (I also tried
Me.Recordset = rst)
Can anyone point me in the right direction?
thanks


Private Sub Command2_Click()
Dim oConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String


oConn.Open "Provider=sqloledb;" & _
"Network Library=DBMSSOCN;" & _
"Data Source=156.235.235.212,1433;" & _
"Initial Catalog=Advantage;" & _
"User ID=username;" & _
"Password=password"
strSQL = "SELECT Table1.Field1, Table1.Field2 FROM Table1;"
rst.Open strSQL, oConn

me.recordsource = strSQL
End Sub

 

[Van T. Dinh]

Is this an Access Database or an Access Project?

If it is an Access Database, the statement:

me.recordsource = strSQL

should work *provided* that Table1 is an ODBC-linked Table in your database.
In this case, you don't need Connection or Recordset.

If you want to use Recordset, you need:

Me.Recordset = {Recordset Object}

However, IIRC, you need DAO Recordset as a Form's Recordset and NOT ADO
Recordset in an Access database. I believe in Access Project, you need ADO
Recordset.

 

[Santiago Gomez]

Thanks for the reply, it is an access 2000 database, but the table is on a
SQL server.
I am trying to avoid having to create a odbc dns connection on every
computer that the database will run.

The tables are not on the database, they are on the SQL server. I have tried
with linked tables (dbo_TableName) but had no luck either.

The question is, How can I set the form's recordset to a string that uses
the connection information I specified?

I can retrieve the recordset by typing
rst.Open strSQL, oConn

But when I try to set
me.recordset =rst

I get a Runtime error '91':
Object variable or With block variable not set

Thanks for any help.

 

[Van T. Dinh]

1. You can use code to create DSN on the required work-stations if you
don't want to go around to create DSN. See the Microsoft Knowledge Base:

http://support.microsoft.com/?id=184608

2. Another method is to use DSN-less connection. See Douglas J. Steele's
article:

http://members.rogers.com/douglas.j.steele/DSNLessLinks.html

3. Like I wrote, I *think* if you want to set the Recordset for a Form in
an Access database, the source Recordset must be a DAO Recordset, not ADO.