Reference an open recordset in VBA using SQL

[Jim Franklin]

Hi,

Can anyone tell me if this is possible? I have an Access 2003 application
which is receiving xml data as a string through a Winsock control. This data
is then parsed into a recordset (lets call it rstA) in memory. I don't want
to write the data to a table as it is only being used for a checking process
once every 15 seconds!

I need to match this data to a separate table of data stored in Access, to
see which records exist in both (the same key ID field exists in both.) At
the moment I am planning to loop through the Access records (as rstB), one
by one, and use a Find or Seek method on my rstA recordset to see if a
corresponding record exists.

I know this is far more inefficient than combining the two recordsets into a
new one using a single SQL statement. But I have no idea if it is possible
to do this? The number of records in each is fairly small (<100)

Can anyone advise? Many thanks for your help,

Jim

 

[Jim Franklin]

Thanks Doug, I didn't even know about ADO disconnected recordsets!

I had a look at them, but I don't think this will help me. The 2 sets of
data (Access db and external controller) are constantly changing, so every
time I synchronise them (every 15 secs or so) I need to use the most
up-to-date data.

They both relay different information about each item passing through a
multi-stage conveyor process. Apart from when I synchronise, I don't need
the data from the external controller. At any one time, about 90% of the
items will match and I need to combine the data from both sources for each
item.

If these were both Access tables, one would simply run a SQL command with a
JOIN operation to combine the 2 sets of data. But because one of my
datasources exists as an open recordset in memory, I cannot seem to do this.
Therefore my code has to loop through each record of my Access table and
check if that item exists in the recordset of imported data. As there are
about 100 records, it means looping 100 times. Maybe there isn't a more
efficient way of doing it?

I hope that makes sense to someone!

Jim