What is quicker way to retrieve data

[adnancanada]

I have PO detail in backend table in SQL server. This is PO detail table which is very large table millions of records. I have almost 100 POs to get all PO details.



If I create table listed 100 POs and link to backend table it takes lots of time to retrieve it. If I put PO nbr in POs feild in query IN ("POnbr1","POnbr2", "POnbr3"....................) means I have to copy and paste all POs in IN (.....). it gives me result very quick.

Is there any possibility I dont need to copy paste. If I make list in forms and select all to run query.

Is there any good solution to get data quicker ? or setup recordset in vba ?

[ranman256]

you dont need the IN (...) to pull the data.
create a query, to pull your data using the WHERE criteria

select * from table where [poID] like "POnbr*"

[adnancanada]

you dont need the IN (...) to pull the data.
create a query, to pull your data using the WHERE criteria

select * from table where [poID] like "POnbr*"

I have query but if I have 100 POs to pull then

Select * from table where PoId IN (POnbr1, POnbr2.......................) which is really pain in the butt.

[aytee111]

Using IN or a list in WHERE is the same, both give the list of available values. I prefer IN myself, easier to see. I don't know if there is a limit in SQL Server on the length of this, however, how many values you are allowed.

You need to make a VBA subroutine which reads the table with the list of PO numbers and creates the IN(...) string, which you will then use in creating the pass-thru query.

[ranman256]

The IN is bad way to do it.
use another lookup table of POs and join it to the main table.
this will pull only those in the list.

[Bulzie]

If I create table listed 100 POs and link to backend table it takes lots of time to retrieve it.

Do you have an index on the PO number field in SQL? This could speed up the search. Also make sure it is a pass-through query so it runs quicker and does processing on SQL server.

[orange]

See this article.


You can use it to exclude or adjust to include.

Good luck.

[adnancanada]

Using IN or a list in WHERE is the same, both give the list of available values. I prefer IN myself, easier to see. I don't know if there is a limit in SQL Server on the length of this, however, how many values you are allowed.

You need to make a VBA subroutine which reads the table with the list of PO numbers and creates the IN(...) string, which you will then use in creating the pass-thru query.

Can we run any simple query via vba.

Select * from POdetail where POnbr IN ('4500909343', '4560990090', '4530009099'.....)

[Bulzie]

What are you doing with the detail data once retrieved from SQL? Displaying on a form or report? Are these 100 PO numbers going to change daily or is it same 100 each day?

Check that there is an index on the PONumber in your SQL table, will speed up the query no matter which way you do it.

[adnancanada]

What are you doing with the detail data once retrieved from SQL? Displaying on a form or report? Are these 100 PO numbers going to change daily or is it same 100 each day?

Check that there is an index on the PONumber in your SQL table, will speed up the query no matter which way you do it.

No everyday I have to change it. I want to make it more automated. Just upload POs and run query.

[orange]

Did you take a look at the link I posted in post #7? Is it applicable to your situation?

[Bulzie]

What's the nature of the 100 POs you have to input each day. Is it possible to use criteria to get them from a recordset each day instead of you having to type them in?

[adnancanada]

What's the nature of the 100 POs you have to input each day. Is it possible to use criteria to get them from a recordset each day instead of you having to type them in?

Third party logistic give us around 100 POs which exists in SQL server. Based on these PO, we need to get weight, volume and PO date created from PO detail. I created a table with one field "POnbr" on SQL Server via pass through query. When I link this table with POdetail table it stuck. It takes lot of time and ended up with odbc connection failier. When I use IN operator in query it works fine.

I want to know how vba code to run it. I simply want to run below query.

Select PO, weight, volume, pocreated from POdetail where PO IN (my 100 POs)

[Bulzie]

Since you have to input the 100 POs each day manually, you won't get any benefit from vba code creating the IN() PO values as you will be typng them in anyway. So put that select statement into a passthrough query in Access, set up the ODBC connection in properties and run it. If you get syntax errors, go to SQL and run your query there to make sure it works and is pulling correctly, then copy that SQL code to put in your Passthrough query in Access.

[adnancanada]

Since you have to input the 100 POs each day manually, you won't get any benefit from vba code creating the IN() PO values as you will be typng them in anyway. So put that select statement into a passthrough query in Access, set up the ODBC connection in properties and run it. If you get syntax errors, go to SQL and run your query there to make sure it works and is pulling correctly, then copy that SQL code to put in your Passthrough query in Access.

Dear my pass through query is working fine some time I have more than 400POs what should I do in this case ? I cant type 400 POs or copy paste 400 there is limit of IN operator.