UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Displaying Resulting Records From A Passthorugh Query?, Any Versions    
 
   
Dave2017
post Nov 27 2017, 02:49 PM
Post#1



Posts: 12
Joined: 1-June 17



Hi all,

I've built an Access database and am trying to migrate the back end to SQL. I'm entirely new to SQL, so there are numerous fundamental concepts I'm trying to get my head around. Here's one:

I've figured out how to design a pass through query in VBA to retrieve the record set based on a particular value entered on a form. The record set usually contains multiple records. My question is: what is the best way to display the resulting data set? Is there some way to dump the records into a table that I can then use as a data source for my forms/reports? Or is there some other way to do this?

Thanks,

Dave
Go to the top of the page
 
Doug Steele
post Nov 27 2017, 02:57 PM
Post#2


UtterAccess VIP
Posts: 21,498
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Do you need to update the data, or just display it?

If you only need to display the data, simply use the pass-through query as the RecordSource for the forms (obviously, reports can't update the data, so you can use the pass-through query there)

If you need to update the data, yes, you'll need to dump the data into a temporary Access table, make the changes there, then write a query to synchronize the data from your temporary Access table to the actual SQL table(s), because pass-through queries aren't updatable.

Your comment the pass-through query needing to be based on a particular value entered on a form does cause some concern, though. Since pass-through queries run on the server, they cannot refer to values on Access forms. In order to have your pass-through query be based on a value on your form, you'll have to dynamically change the SQL of the pass-through query before running it.

--------------------
Go to the top of the page
 
Dave2017
post Nov 27 2017, 03:14 PM
Post#3



Posts: 12
Joined: 1-June 17



Hi Doug,

Thanks for your prompt reply!

I'm going to need to do both display and update records. Since I'm just learning how to do this, I thought I'd start with just getting the records I want to display.

Not sure I understand how to set the Query as the record source for a form. My VBA code retrieves the records using 'Set rst = qdf.OpenRecordset', where rst is a DAO.Recordset. Is this the object I should be setting my record source to? Or am I totally missing something here?

Oh, and I'm using a dynamic string to create a SQL for the pass through.

Thanks,

Dave
Go to the top of the page
 
Doug Steele
post Nov 27 2017, 03:59 PM
Post#4


UtterAccess VIP
Posts: 21,498
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I typically create a saved pass-through query, then use that saved query as the RecordSource.

Realistically, you can create any pass-through query, regardless of its SQL. Then, in your form, you can change the SQL of the saved pass-through query to your new SQL string.

But since you say you need to be able to update the data, it's sort of a moot point: as I mentioned earlier, pass-through queries are not updatable.

--------------------
Go to the top of the page
 
Dave2017
post Nov 27 2017, 04:40 PM
Post#5



Posts: 12
Joined: 1-June 17



Got it! Thanks!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 12:16 AM