How to query db with memory array data?

[amorosik]

Sometimes it might be useful to 'interlock' two different structures to execute a query and feed a recordset
For example, a physical database table called ARTICOLI and an in-memory string array ARTICOLI_CODICI containing only the item codes to be displayed could essentially be 100-200 strings containing values
So the question is: how can we most efficiently create a single query that 'fetches' data from both the database table ARTICOLI and the in-memory array ARTICOLI_CODICI ?

 

[MajP]

If you have an array of 200 item codes
Code 1
Code 2
....
Code 200

In what way could you combine this? Are you wanting to join or Union the data. If you did a join that would not help because you would already have to have the code in the real table.
You can add a column to a disconnected ADO.recordset, but not sure if that helps.
If not probably going to require writing to a temp table from the array.

If you have to do this alot you might want to look at LINQ in another platform which allows SQL like commands with in memory objects.

 

[tvanstiphout]

Sometimes it might be useful to 'interlock' two different structures to execute a query and feed a recordset
For example, a physical database table called ARTICOLI and an in-memory string array ARTICOLI_CODICI containing only the item codes to be displayed could essentially be 100-200 strings containing values
So the question is: how can we most efficiently create a single query that 'fetches' data from both the database table ARTICOLI and the in-memory array ARTICOLI_CODICI ?

Do you want to return records from the table that match the array values?
If so, you can create dynamic SQL string with the IN clause:

Sub testDynamicSQL()
Dim sql As String
Dim aryCodes() As String
Dim varCode As Variant

    aryCodes = Split("code 1,code 2,code 3,code 200", ",")
    
    sql = "select * from ARTICOLI where someField in ("
    For Each varCode In aryCodes
        sql = sql & "'" & varCode & "',"
    Next
    sql = Left(sql, Len(sql) - 1)   'Strip trailing comma
    sql = sql & ")"
    Debug.Print sql
    DoCmd.OpenQuery sql
End Sub

 

[CJ_London]

Perhaps use a function called by the query to retrieve the in memory data

 

[amorosik]

If you have an array of 200 item codes
Code 1
Code 2
....
Code 200

In what way could you combine this? Are you wanting to join or Union the data. If you did a join that would not help because you would already have to have the code in the real table.
You can add a column to a disconnected ADO.recordset, but not sure if that helps.
If not probably going to require writing to a temp table from the array.

If you have to do this alot you might want to look at LINQ in another platform which allows SQL like commands with in memory objects.

Yes, like a classic join between two tables
I'd like to understand how, and if it's possible, to use an in-memory structure (an array of integers, a disconnected DB ADO recordset, or similar), instead of a database table to create a query or function that returns the same results as a database query between two tables.

 

[cheekybuddha]

Yes, like a classic join between two tables
I'd like to understand how, and if it's possible, to use an in-memory structure (an array of integers, a disconnected DB ADO recordset, or similar), instead of a database table to create a query or function that returns the same results as a database query between two tables.

By the time you have a recordset it is too late. You can't join recordsets.

Your 2 real options are:
1. Build the array into an SQL WHERE clause (per Post #3)
2. Load the array in to a temp table and join that to your existing table (more hassle if BE is in Access, easier in an RDBMS)

 

[Ken Sheridan]

If code similar to Tom's in post #3 is used to build a delimited value list, by assigning the value list to a text box control in a form, the control can then be referenced as a parameter in a query by calling the InParam function from the following module published by Microsoft many years ago:

' Module basInParam
' The functions in this module were published by Microsoft, but
' the article in question is no longer available.
' They are used to simulate the use of the
' IN operator in a query, but by accepting a value list as a parameter
' rather than a literal value list as the IN operator requires.

Option Compare Database
Option Explicit

Function GetToken(stLn, stDelim)
  
    Dim iDelim As Integer, stToken As String
  
    iDelim = InStr(1, stLn, stDelim)
  
    If (iDelim <> 0) Then
        stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
        stLn = Mid$(stLn, iDelim + 1)
    Else
        stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
        stLn = ""
    End If
  
    GetToken = stToken
  
End Function

'============================================================
' The InParam() function is the heart of this article. When
' the query runs, this function references the value list as a parameter.
' The values are interpreted as if they had been entered
' within the parentheses of the IN operator.
'============================================================
Function InParam(Fld, Param)

    Dim stToken As String
    'The following two lines are optional, making queries
    'case-insensitive
    Fld = UCase(Fld)
    Param = UCase(Param)
  
    If IsNull(Fld) Then Fld = ""
        Do While (Len(Param) > 0)
        stToken = GetToken(Param, ",")
        If stToken = LTrim$(RTrim$(Fld)) Then
            InParam = -1
            Exit Function
        Else
            InParam = 0
        End If
    Loop

End Function

Unlike concatenating the value list into a query's SQL statement, the InParam function does not require string expressions to be wrapped in quote characters. In a query it would be called as in the following example:

WHERE InParam([Contacts].[ContactID],[Forms]![frmReportDialogue]![txtContactIDList])=TRUE

 

[cheekybuddha]

Unlike concatenating the value list into a query's SQL statement, the InParam function does not require string expressions to be wrapped in quote characters. In a query it would be called as in the following example:

Certainly won't be able to use any indexes there then?

 

[Ken Sheridan]

Certainly won't be able to use any indexes there then?

I think that's probably true. Does the IN operator make use of the indexes? It can be expanded algebraically to a set of OR operations, in which I'd guess case it would. I always found the InParam function efficient enough in my line of work as an Environmental Planning Officer, but we were generally dealing with quite discrete localised datasets. I'd not use it in wider contexts.

 

[cheekybuddha]

Does the IN operator make use of the indexes?

Usually.

See Section 2 in this link (last bullet point).

 

[MajP]

As far as I can tell the OP is not talking about creating a filter, but joining data

Yes, like a classic join between two tables

So they are likely limited to a temp table.

 

[cheekybuddha]

As far as I can tell the OP is not talking about creating a filter, but joining data

The two can achieve much the same effect.

(eg

SELECT
  a.ID,
  a.Fld
FROM a
INNER JOIN b
        ON a.Fld = b.Fld
;

-- will produce the same as
SELECT
  a.ID,
  a.Fld
FROM a
WHERE a.Fld IN (
  SELECT b.Fld FROM b
);

-- if table b contains three records in Fld: 'a', 'b', 'c', then also equivalent:
SELECT
  a.ID,
  a.Fld
FROM a
WHERE a.Fld IN ('a', 'b', 'c');

)

 

[MajP]

II may have read it wrong focusing on pulling data from the table and the array

So the question is: how can we most efficiently create a single query that 'fetches' data from both the database table ARTICOLI and the in-memory array ARTICOLI_CODICI ?

I was thinking that the array might be a multi dimensional array where it stores the ProductCode and some other information and the table only stores the ProductCode. And you want to join these and pull in other information contained in the array.
"Fetches data from both..."

But when I reread it it may just mean a filter

and an in-memory string array ARTICOLI_CODICI containing only the item codes to be displayed could essentially be 100-200 strings containing values

 

[cheekybuddha]

II may have read it wrong focusing on pulling data from the table and the array

I was thinking that the array might be a multi dimensional array where it stores the ProductCode and some other information and the table only stores the ProductCode. And you want to join these and pull in other information contained in the array.
"Fetches data from both..."

But when I reread it it may just mean a filter

OP has gone AWOLfor the time being. I guess we'll have to wait for clarification.

For an array of c.200 values, I think a temp table is still probably the best solution - you can index the values for better performance on the join (if only a subset of array values is required), and also handle your alternate reading of the original issue.

 

[riktek]

InParam function

Interesting. Looping with a passback.

does not require string expressions to be wrapped in quote characters

Because its arguments are identifiers, or am I all wet?