Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-01-2018, 05:26 AM   #1
NotAnExpert
Newly Registered User
 
Join Date: Feb 2017
Posts: 25
Thanks: 9
Thanked 0 Times in 0 Posts
NotAnExpert is on a distinguished road
Pass Through Query

Good afternoon Gents and Gentesses

I am looking at Access 2010, and am in the process of creating a small database that will look at Sage 200c data directly on the server and hopefully not lock any tables and records while I run queries on it.

I have a form (frmMainForm)
On frmMainForm there is a textbox (txtJobRef) and a subform (sfrmJobItems)

sfrmJobItems is based on a pass through query looking at data on SQL Server (where the Sage 200c data sits) and the passthrough query is currently built as follows:

Code:
SELECT DocumentNo, ItemDescription, PrintSequenceNumber, LineQuantity FROM JobListItems;
I am after the subform requerying when the user types a job number into the txtJobRef textbox and presses 'enter' or clicks the search button.

My issue is that I am unsure how to make the pass through query look at the Access Form textbox. Under Normal Circumstances the standard query would simply look at [Forms]![frmMainForm]![txtJobRef] but I have been unable so far to make this work...

Are there any pointers on why this would be, or if someone would be kind enough to offer advice on the correct syntax for this it would be much appreciated.

Note, it is important to me that while a user is using the Microsoft Access database to view this information, that it doesn't cause locks that make the main program hang for the other users...

NotAnExpert is offline   Reply With Quote
Old 03-01-2018, 06:56 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,436
Thanks: 128
Thanked 1,476 Times in 1,448 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Pass Through Query

The easiest way is to build a query definition - something like
Code:
        Dim db           As DAO.Database
        Dim rs           As DAO.Recordset
        Dim qdfpt        As QueryDef

        Set db = CurrentDb
        Set qdfpt = db.QueryDefs("passYourSavedQuery")
        qdfpt.Connect = "ODBC;DRIVER=SQL Server;SERVER=YourServer;;DATABASE=YourDatabase;UseTrustedConnection=True"
        qdfpt.SQL = "SELECT DocumentNo, ItemDescription, PrintSequenceNumber, LineQuantity FROM JobListItems WHERE JobNumber = " & [Forms]![frmMainForm]![txtJobRef] & " ;"

        db.Close
        DoCmd.OpenQuery "passYourSavedQuery", acViewNormal, acReadOnly
Change the connection details appropriately
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
NotAnExpert (03-01-2018)
Old 03-01-2018, 07:05 AM   #3
NotAnExpert
Newly Registered User
 
Join Date: Feb 2017
Posts: 25
Thanks: 9
Thanked 0 Times in 0 Posts
NotAnExpert is on a distinguished road
Re: Pass Through Query

Thank you for your help again Minty, nice to bump into you again.

I'm curious, where exactly do I put that code? I'm sorry to ask, but this is completely new to me...

NotAnExpert is offline   Reply With Quote
Old 03-01-2018, 07:11 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,436
Thanks: 128
Thanked 1,476 Times in 1,448 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Pass Through Query

I would add it to a cmd button to run the code and in the example provided open the query.

If you put the button on the form with the job number on it you can simply use Me.txtJobRef instead of the form reference.

You will need to create the query first and save it, this will then overwrite it with the SQL statement above each time it is run.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 03-01-2018, 07:17 AM   #5
NotAnExpert
Newly Registered User
 
Join Date: Feb 2017
Posts: 25
Thanks: 9
Thanked 0 Times in 0 Posts
NotAnExpert is on a distinguished road
Re: Pass Through Query

How would this reflect in the subform which will have the items listed dependent on the jobref?
NotAnExpert is offline   Reply With Quote
Old 03-01-2018, 08:30 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,436
Thanks: 128
Thanked 1,476 Times in 1,448 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Pass Through Query

I would set the rowsource of the subform to the passthrough query.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 03-01-2018, 09:33 AM   #7
NotAnExpert
Newly Registered User
 
Join Date: Feb 2017
Posts: 25
Thanks: 9
Thanked 0 Times in 0 Posts
NotAnExpert is on a distinguished road
Re: Pass Through Query

Thank you Minty, it does look as though the format of the Pass Through Query is being changed by the after event of the textbox (which is what I was after) now the code you suggested has been placed there.

However, I now have another issue, I am now seeing the following issue if I run the query on its own after the event has been run...

ODBC --Call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting the varchar value '###2688471#586449' to data type int. (#245)

I suspect the 2688471 etc is the actual ID of the row that is coming from the sage server, but I don't know why that is even being looked at?! it only seems to cause an issue if an actual jobref is referred to in the Pass Through, because if I run the Pass Through without any job ref being referred to, it lists all the items perfectly fine?!

and the subform refreshes and has #Name? along each field in the row??

NotAnExpert is offline   Reply With Quote
Old 03-01-2018, 09:41 AM   #8
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,436
Thanks: 128
Thanked 1,476 Times in 1,448 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Pass Through Query

That appears as if the job number may be a text field instead of an integer ?

Try just adjusting the pass through to a string? Just herd code it and run it directly.

"SELECT DocumentNo, ItemDescription, PrintSequenceNumber, LineQuantity FROM JobListItems WHERE JobNumber = '12387197' ;"
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
NotAnExpert (03-01-2018)
Old 03-01-2018, 09:49 AM   #9
NotAnExpert
Newly Registered User
 
Join Date: Feb 2017
Posts: 25
Thanks: 9
Thanked 0 Times in 0 Posts
NotAnExpert is on a distinguished road
Re: Pass Through Query

Oddly I remember running into a similar issue previously where the ' was required around the field, I tried it here in the code you supplied and now it's perfect!

Now I can refer to the items from our orders, I can do a heck of a lot more using our existing databases and upgrade them all, many thanks for that!

One last thing, is it possible to load the results from this pass through query into a combo box? I'm positive it will be, i'll have a play with it first.

Again many thanks Minty, you're a star.
NotAnExpert is offline   Reply With Quote
Old 03-01-2018, 10:17 AM   #10
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,436
Thanks: 128
Thanked 1,476 Times in 1,448 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Pass Through Query

Yes - it's just a record set at the end of the day so you can use it almost anywhere.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 03-02-2018, 02:10 AM   #11
NotAnExpert
Newly Registered User
 
Join Date: Feb 2017
Posts: 25
Thanks: 9
Thanked 0 Times in 0 Posts
NotAnExpert is on a distinguished road
Re: Pass Through Query

Hi, thanks again Minty, always appreciated.

Now for my next question...

Access can use the NZ function to get null and blank fields as well as an option to specify "*" for wildcard search, is there a way to use this in the SQL String?

i.e. DocumentNo is fine and works, but if I add another textbox (txtSearch) to the form, but I want to be able to search any part of a particular field, how do I formulate the string? normally I would have used:

Like Nz("*" & [Forms]![frmProductSearch]![txtSearch] & "*","*")

but the " might pose an issue? in the SQL String?

Cheers...
NotAnExpert is offline   Reply With Quote
Old 03-02-2018, 03:00 AM   #12
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,436
Thanks: 128
Thanked 1,476 Times in 1,448 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Pass Through Query

The wildcard character in SQL is % So

Like '%'" & YourValue & "'%' ...

In SQL you would use normally use COALESCE(Field1,ValueIfNull) to capture the Null value. As with everything , there are other methods :-)
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
NotAnExpert (03-02-2018)
Old 03-02-2018, 04:08 AM   #13
NotAnExpert
Newly Registered User
 
Join Date: Feb 2017
Posts: 25
Thanks: 9
Thanked 0 Times in 0 Posts
NotAnExpert is on a distinguished road
Re: Pass Through Query

Thank you again Minty, it seems to be working.

Now i've got to figure out why having this access database open causes the Sage200c program to lock up and stop users from being able to perform certain tasks.

Kindest regards
NotAnExpert is offline   Reply With Quote
Old 03-02-2018, 04:31 AM   #14
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,436
Thanks: 128
Thanked 1,476 Times in 1,448 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Pass Through Query

Check the connection properties.
Make sure the recordset type is set to snapshot or similar.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
NotAnExpert (03-05-2018)
Old 03-05-2018, 01:13 AM   #15
NotAnExpert
Newly Registered User
 
Join Date: Feb 2017
Posts: 25
Thanks: 9
Thanked 0 Times in 0 Posts
NotAnExpert is on a distinguished road
Re: Pass Through Query

Hi, I have done this, but it seems it still locks the table up that people need to be accessing on a regular read write basis.

It's strange though as the pass through query is aimed at a view, and not the tables themselves, would this even matter?

I apologise, I know I don't know exactly what question to ask...

Thanks for help so far, any other suggestions?

Kindest regards

NotAnExpert is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Performance of a Query Based on Linked Tables Compared to a Pass-Through Query Steve R. Theory and practice of database design 8 05-29-2013 05:44 AM
Pass through query to SQL Stored Proc, pass parameter urir10 Queries 1 10-03-2012 10:29 AM
Pass text box value to Access Pass Thru Query bconner Modules & VBA 3 02-16-2012 01:41 PM
Pass through within a pass through query skwilliams Queries 0 06-22-2011 07:18 AM
Procedure works Great First Pass, but Not the Second Pass quest4 Forms 8 07-15-2005 11:02 AM




All times are GMT -8. The time now is 03:30 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World