Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...Thanks for a great forum. My problem was answered just by scrolling through previously solved problems. Great service!!..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft: Access Reports Forum

Populating Access 2010 table from SQL ServerHelpful Member! 

thread703-1687468
Read More Threads Like This One
PeDa (TechnicalUser)
5 Jul 12 4:30
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 (Programmer)
5 Jul 12 8:26
I would use a pass-through query as the Record Source of the report.

Duane
Hook'D on Access
MS Access MVP

PeDa (TechnicalUser)
7 Jul 12 9:00
>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 (Programmer)
7 Jul 12 12:34
If you have a DSN or want to create one, just use the builder [...]. Otherwise try:

CODE --> ConnectionString

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 (TechnicalUser)
9 Jul 12 12:02
Thank you. I have managed to set up a pass through query as follows before opening my form:

CODE

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

CODE

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).
Helpful Member!  dhookom (Programmer)
9 Jul 12 12:24
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 (TechnicalUser)
9 Jul 12 14:27
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.
Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Access Reports

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close