Setting subform recoursource with connection string (SQL server)

  • Thread starter Thread starter Santiago Gomez
  • Start date Start date
S

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
 
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.
 
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.
 
Back
Top