Populating Access 2010 table from SQL Server 1

Hello, using SQL Server as backend and Access 2010 as frontend (without ODBC, thus nu visible tabels or queries). Using "Set Me.Recordset = rstMyrecordset" in Report_Open give an error message: error 32585 "This function is only available in an ADP". Any suggestions welcome.

 

[dhookom]

I would use a pass-through query as the Record Source of the report.

Duane
Hook'D on Access
MS Access MVP

 

[PeDa]

>I would use a pass-through query as the Record Source of the report.

Thank you. I gather I have to put the query in a QueryDef. I would appreciate some help with configuring the Connect string for this. I am using Access 2010 and SQL Server Express 2008. My server type is Database Engine, Server name is "." (local), Authenication is Windows authentication, and database name is TRISQAO.
Peter D.

 

[dhookom]

If you have a DSN or want to create one, just use the builder [...]. Otherwise try:

ODBC: Driver={SQL Native Client};Server=(local);Database=TRISQAO; Trusted_Connection=yes;

You may need to add \SQLEXPRESS to the Server.

Duane
Hook'D on Access
MS Access MVP

 

[PeDa]

Thank you. I have managed to set up a pass through query as follows before opening my form:

CurrentDb.QueryDefs.Delete "MyQueryDef"
Set qdf = CurrentDb.CreateQueryDef("MyQueryDef")
With qdf
  .Connect = "ODBC;Driver={SQL Server Native Client 10.0};Server=.;Database=TRISQAO; Trusted_Connection=yes"
  .SQL = "SELECT <column1>,<column2> FROM <table> Where <conditions>
End With
DoCmd.OpenForm "MyForm", , , , , acDialog

and the Open event of the form contains

Set Me.Recordset = CurrentDb.QueryDefs("MyQueryDef")

which unfortunately still gives the same error message (perhaps I should have mentioned I am using a .accdb, and not an ADP).

 

[dhookom]

This is "Access Reports Forum". Why are you opening a form?

Is that your exact code?

I would not delete and then recreate the querydef. Simply change the SQL property of an existing querydef.

Why use a recordset rather than setting the record source property to "MyQueryDef"?

Duane
Hook'D on Access
MS Access MVP

 

[PeDa]

Hallo Duane,

>This is "Access Reports Forum". Why are you opening a form?
Oh dear; slip of the brain; I meant "Open event of the report"

>I would not delete and then recreate the querydef. Simply change the SQL property of an existing querydef.
OK

>Why use a recordset rather than setting the record source property to "MyQueryDef"?
Doing the latter does indeed work. You have solved my problem; many thanks.

Peter D.