Pass Through Query

[NotAnExpert]

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:

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...

 

[Minty]

The easiest way is to build a query definition - something like

        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

 

[NotAnExpert]

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...

 

[Minty]

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.

 

[NotAnExpert]

How would this reflect in the subform which will have the items listed dependent on the jobref?

 

[Minty]

I would set the rowsource of the subform to the passthrough query.

 

[NotAnExpert]

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??

 

[Minty]

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' ;"

 

[NotAnExpert]

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.

 

[Minty]

Yes - it's just a record set at the end of the day so you can use it almost anywhere.

 

[NotAnExpert]

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...

 

[Minty]

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

 

[NotAnExpert]

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

 

[Minty]

Check the connection properties.
Make sure the recordset type is set to snapshot or similar.

 

[NotAnExpert]

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

 

[Minty]

SQL Views can be updateable, depending on how they are set up. I would look there to start with.

If you open the view in SSMS does it also lock the tables? If so then that's your problem.

 

[NotAnExpert]

Hi Minty, can you explain what you mean by this please? I assume you mean opening the view from the SQL Studio Management screen and seeing if that alone causes the hang up?

 

[Minty]

Yes - that was the jist of it.
If the view is editable then I would think it might cause a record lock, but I don't know why.

Normally in SQL server it takes considerable "talent" to lock up an entire table, in fact I'm not sure, short of applying a table design change, how you would achieve that by just querying the data.

It might be some devious / badly designed/ in-built behaviour of the Sage system?