[AccessD] Array as source for query

Martin:  Are you implying that I am incomprehensible?  Obtuse?  Clearly I 
have failed in my explanation.  How about you paste the code below in a 
module:

Sub VirtualRecordSet(strFilePath As String)
  Dim db As DAO.Database
  Dim lngI As Long
  Dim strFileName As String
  Dim strSql As String

  Set db = CurrentDb
  strFileName = Dir(strFilePath)
  If Len(strFileName) Then
    strSql = " Select '"
    Do While Len(strFileName)
      strSql = strSql & strFileName & "' As FileName, '"
      strSql = strSql & FileDateTime(strFilePath & strFileName) & "' As 
FileDate, '"
      strSql = strSql & FileLen(strFilePath & strFileName) & "' As FileSize 
>From MSysQueries Union Select '"
      lngI = lngI + 1
      If lngI = 50 Then Exit Do
      strFileName = Dir
    Loop
    strSql = Mid$(strSql, 1, Len(strSql) - 15)
  End If
  db.QueryDefs("qryFileList").SQL = strSql
  DoCmd.OpenQuery "qryFileList"
  db.Close
  Set db = Nothing
End Sub

Copy any query in your application, or make a new one and name it 
'qryFileList'.  This step is for demonstration purposes only.  If you use 
the code to poplulate a list box, you can set the rowsource to the strSql 
variable and skip using a querydef entirely.

Then run the code below from the debug window:

virtualrecordset "C:\"

and have a look at the query in the MSAccess window.  You will see a list of 
files in the directory passed as a parameter together with file date and 
size information.  The example uses the internal hidden MSysQueries table, 
but you can use any table in your database.  It makes no difference what the 
field names are or how many records there are in the table.  The code in the 
example aborts at 50 file names max due to Query length limitations and it 
may not work if you have a pile of really long file names in a selected 
target folder parameter.  You'll get some errors if you use a drive letter 
that doesn't exist as well.

You may want to use a debug.print in the code to watch the virtual recordset 
generated and of course, you can examine the SQL string of the query in 
design mode.

Although the example doesn't acutally use an array, that could have been 
added as an intermediate step.  If you would like, I can fill in the details 
of that as well.  Hopefully this example makes the concept clearer than my 
words.  It is slightly modified from a version I wrote in 1998 demonstrating 
file list functionality and comparing a number of methods of populating the 
list that included a callback method that doesn't run into the query string 
length limitation.

In practical terms, it is possible to set up a procedure that relies on a 
virtual recordset and only goes to a temp table when the string length is 
excessive.  If temp tables are on a remote server, this approach is 
significantly faster than populating and then querying that table over a 
network.  Also, this approach is a slick way of setting up a few rows to 
join to a master table to return a highly customizable junction.  I have 
also used virtual recordsets for things such as returning logged in user 
names, available drives and other such data that depend on a constantly 
changing environment.  Usually, this type of data is displayed in lists and 
for this purpose, I nearly always use callback functions instead.  Still, it 
is kind of fun to use as a recordsource for a form.

Hopefully I haven't muddied the waters further...

Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com





>From: "Martin Reid" <mwp.reid at qub.ac.uk>
>
>Still dont understand your posts Jurgen but nice to see you back.
>
>Martin
>
>Martin WP Reid
>Training and Assessment Unit
>Riddle Hall
>Belfast
>
>tel: 02890 974477
>


>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Your Space. Your Friends. Your Stories. Share your world with Windows Live 
Spaces. http://discoverspaces.live.com/?loc=en-CA