Record Source between Access and SQL

[Guest]

I need to pull a query up from SQL through access --- WITHOUT --- using the
form rowsouce property. What am I doing wrong?




Private Sub cboCompany_Click()

Set db = DBEngine.Workspaces(0).Databases(0)

Me.RecordSource = ("Select distinct Company_Name from dbo_Tbl_Quarterly")
Set qdf = db.CreateQueryDef("")
qdf.Connect = _
"ODBC;DSN=New Trusco;Description=Copany SQL 2000
TCP/IP;DATABASE=db_Database;LANGUAGE=us_english;Network=Network;TRUSTED_CONNECTION=Yes"
qdf.ReturnsRecords = True

End Sub

 

[Larry Linson]

I need to pull a query up from
SQL through access
--- WITHOUT --- using the
form rowsouce property.
What am I doing wrong?

Forms don't have a RowSource property, Combo and List Boxes do. Did you mean
"Record Source"? But, then, your code actually constructs an SQL statement
in the current Form's RecordSource.

"Pulll a query up" isn't very precise... do you mean Execute a Query to be
displayed in datasheet view (as though you'd done it via the Query Builder
window)? Please clarify, and perhaps someone can offer useful advice.

Comments on the code: there's nothing to tie the QueryDef you create to
anything else, and there's no code that executes query -- the SELECT
statement you saved as RecordSource looks OK, provided dbo_Tbl_Quarterly is
a linked SQL Server table.

One simple way to change the RecordSource is to use a (predefined) Query as
the RecordSource, and just change the SQL property of that (predefined)
Query. Changing its SQL property does not invalidate its Connect property,
so you would not have to modify or re-create that. You may have to Requery
the Form in that case, but my recollection is not clear on the need to
requerying.

Larry Linson
Microsoft Access MVP