Can I loop a access table for a pass thru query (1 Viewer)

[roosn]

Access 2003 FE
Sql Server BE

Hi
i Can loop a listbox to create a pass thru query using strCriteria as the parameter as thus

' Loop through the selected items in the list box and build a text string
For Each varItem In Me!lstActual.ItemsSelected
strCriteria = strCriteria & "," & Me!lstActual.ItemData(varItem) & ""
Next varItem
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string

strSQL = "SELECT DISTINCT TOP 100 PERCENT dbo.aaaaaaaaaa......

can i loop the values in a access table, to create the strCriteria that i can use in the pass thru query

Many Thanks

 

[namliam]

TOP 100 PERCENT
I.e. Get everything... This doesnt do anything but cost performance...

And yes you can loop a table... Search the forum for "DAO.Recordset" and you will find plenty of examples of how-to and/or how-not-to do this.

Edit:
Does this table exist in Access or SQL server? If on the SQL Server, you may want to join the table intead of reading it. MUCHO fastero!

 

[roosn]

Hi
Thank you for your reply
Yes the table is created in Access, but the work done in sql server by a pass thru query.
the table contains never more than 20 records

 

[namliam]

Did you read the help and get it to work? Or??

 

[roosn]

Hi
I have been thru the query results and tried googling to set me on my way, but as i try to manipulate the code for my need, i am messing up somewhere

basically i have created a table below

part_series_ID series
0 A
1467 A310
1468 A330
1469 A340


i just want to get a strCriteria string value = (1467,1468,1469)

from there i can use with my pass thru query, as i have been successfully doing with multi select listbox values

Thank you for your assistance
Roosn

 

[LPurvis]

As namliam says - you'll want to drop that use of "TOP 100 PERCENT".
I'm sure you have it there because you copied it from a View in SQL 2000 which was ordered (going against the technical definition of a View - and the syntax is no longer adhered to in Views in newer versions anyway).
You can order your statement as you would normally (simply by including a standard ANSI ORDER BY clause without the TOP predicate).

As for your list - iterating through a DAO recordset as namliam mentioned is a definite and easy option.
You can retrieve your string with even less coding effort of course with the like of

strList = CurrentProject.Connection.Execute("SELECT FieldName FROM TableName").GetString(2, , , ",")

and then add your brackets and trim the trailing comma ;-)
strList = "(" & Left(strList, Len(strList) - 1) & ")"

All ready to concat into your PT's SQL statement in a "In" clause I presume.
Just remember to dump that pointless TOP statement.

Cheers

 

[roosn]

Leigh, Namliam

thanks very much, i have got what i wanted to do, working

also, i have removed the 'TOP statement' as advised

best regards

Roosn