Getting XL and Access to communicate in VBA

[Charles]

Hello

I can already make Access and XL to communicate in VBA, through DAO
and recordsets, but I have the following problem: I have some
calculations to run on a database with 100,000s of records. These
calculations are a bit too complex for an sql query, so the easy
solution is to run a VBA code to do it.

I have two solutions. Either run the code from XL where the master
code is, and gather all the data from Access using an DAO recordset,
and run the data in XL/VBA. The problem is that recordsets seem to be
very slow. Is there any way to load data very quickly in one go (I
have the feeling Excel and Access are exchanging data each time I do
rcd.movenext, it would be more efficient to transfer directly a large
array in one go instead, but is it possible?)

The other solution I see is having the code sitting in Access and
launching the code from XL. I can do that using a function in Access
and a macro that launches that function in Access. And I can simply
run the macro from XL. What I can't do is to give an argument to this
function, which is problematic for me. Is there anyway to launch some
code in Access from VBA by passing some arguments to this code?

Any help greatly appreciated!
Thanks
Charles

 

[Charles]

And actually even more critical than passing arguments to the code in
Access, how can I get this code to return data to Excel when it's done?

 

[Stefan Hoffmann]

hi Charles,

I have two solutions. Either run the code from XL where the master
code is, and gather all the data from Access using an DAO recordset,
and run the data in XL/VBA. The problem is that recordsets seem to be
very slow.

There is no difference between running your code in Excel or Access.

The problem is the "complex calculation". You don't need do to do it in
one SQL, but try to use as much SQLs as possible.
Take a look at your table structure. Not fully normalzied tables may
cause a severe runtime extension.

Describe your table structure and your calculation. Maybe I can see
something obvious.

mfG
--> stefan <--

 

[Charles]

I have tried running the same VBA code in access and excel, and the
difference is really spectacular.

In my case, I have a very long list of entries (near a million). Table
looks a bit like this:

Reference (text unfortunately, indexed)
VersionID (integer, indexed)
FilterID (integer, indexed)

That's not the only data it stores, but let's ignore the other ones.
Basically the idea is that I store a long list (about 150,000 entries)
every month, with a new version (I can't just use the same entries as
for the previous month as all the parameters I have not shown here
change from month to month, and I need to track the records). So there
is a VersionID (=1, 2, 3,...) that identifies each new monthly set of
150,000 entries.

Now what I am trying to do is to compare any two given versions, and
in particular to find the new elements. Where it gets a bit more
complicated is that in a given version, the same Reference can appear
several times, with a different FilterID. I am only interested in the
number of new References (so I need to do a kind of GROUP BY Reference
query).

So if my table looks like this:

Reference | VersionID | FilterID
A | 1 | 100
A | 1 | 200
B | 1 | 100
C | 1 | 100
A | 2 | 100
A | 2 | 200
B | 2 | 100
C | 2 | 100
D | 2 | 100
A | 3 | 100
A | 3 | 200
B | 3 | 100
C | 3 | 100
D | 3 | 100
So I have 3 versions. At the second version a new reference appears
(D, that's the one I am trying to find). Also, for the reference A,
there are two possible filters (100 and 200).

There is an SQL query that can deal with this (I want to compare
version 1 and 3), something like:

SELECT Tbl.Reference, Tbl.FilterID, Tbl2.Reference FROM Tbl LEFT OUTER
JOIN Tbl as Tbl2 ON Tbl.Reference=Tbl2.Reference AND Tbl2.VersionID =1
WHERE Tbl.VersionID=3 AND Tbl2.Reference IS NULL

that works but:
1. it doesn't group all the refences that have the different FilterID
together, so if A had appeared in version 3, it would return a first A
with a FilterID of 100, and another A with a FilterID of 200. This can
multiply by 2 or 3 the results. But that's only a minor problem, I
could deal with that manually in VBA after.
2. it is dead slow.

What I am planning to do is simply to create in VBA a dynamic array
using two classes, and to do two SELECT:
SELECT Tbl.Reference FROM Tbl GROUP BY Tbl.FilterID HAVING
Tbl.VersionID=3
and
SELECT Tbl.Reference FROM Tbl GROUP BY Tbl.FilterID HAVING
Tbl.VersionID=1

go through the list, add each elements in the same dynamic array, and
flag when I am trying to add an elements that is already there, and
return the elements that have not been flagged as new.

If there is a smarter way to do it in SQL, I am more than happy to do
it.

If I keep doing it in VBA, I think it is more efficient to have the
code loading the SELECT qry in Access, not Excel, and to provide the
access code with the arguments (the version number I want to compare,
so 1 and 3, but that could be 2 and 3, etc) and return a list of
References to XL.

Charles

 

[Stefan Hoffmann]

hi Charles,

Now what I am trying to do is to compare any two given versions, and
in particular to find the new elements. Ok.


So if my table looks like this:

Reference | VersionID | FilterID
A | 1 | 100
A | 1 | 200
B | 1 | 100
C | 1 | 100
A | 2 | 100
A | 2 | 200
B | 2 | 100
C | 2 | 100
D | 2 | 100
A | 3 | 100
A | 3 | 200
B | 3 | 100
C | 3 | 100
D | 3 | 100
So I have 3 versions. At the second version a new reference appears
(D, that's the one I am trying to find). Also, for the reference A,
there are two possible filters (100 and 200).

There is an SQL query that can deal with this (I want to compare
version 1 and 3), something like:
SELECT Tbl.Reference, Tbl.FilterID, Tbl2.Reference FROM Tbl LEFT OUTER
JOIN Tbl as Tbl2 ON Tbl.Reference=Tbl2.Reference AND Tbl2.VersionID =1
WHERE Tbl.VersionID=3 AND Tbl2.Reference IS NULL

Not sure if it works, but you may give it a try:

SELECT A.*
FROM [yourTable] A
LEFT JOIN [yourTable] B
ON A.Reference = B.Reference
AND A.VersionID = 1
AND B.VersionID = 2
WHERE B.Reference Is Null

btw, speed will be here always a problem, as you have a lot of data.

mfG
--> stefan <--

 

[Norman Yuan]

I have tried running the same VBA code in access and excel, and the
difference is really spectacular.

That is expected. Whether you run the VBA code from XL (I assume it means
Excel) or Access, it access data the same way, with DAO (or ADO). So, there
is no point to call macro (which in turn runs the VBA code) in Access from
Excel. Actually, this even slow down the whole thing, because a call from
Excel must be made accross application boundary between Excel and Access,
not to mention that you have "passing argument to macro" issue.

You really need to make one or more good queries in Access, as Stefan
suggested, use SQL as much as you can, so that your DAO recordset only grabs
smaller and optimized data set, which is to be processed by VBA code

 

[Charles]

That is expected. Whether you run the VBA code from XL (I assume it means
Excel) or Access, it access data the same way, with DAO (or ADO). So, there
is no point to call macro (which in turn runs the VBA code) in Access from
Excel. Actually, this even slow down the whole thing, because a call from
Excel must be made accross application boundary between Excel and Access,
not to mention that you have "passing argument to macro" issue.

Sorry, I meant that Access is specacularly quicker than Excel. One
thing to know is that I am using network drives, so performance issues
can be exarcerbated by the very slow access time to a file (but
reasonable download speed).

I'll try Stefan's syntax, perhaps it is quicker than the query I have
mentioned.

Thanks
Charles

 

[Charles]

I start to think that it would be smarter to do this compare one for
all each time I add a new version in a separate table. The most
elegant solution is not necessary the fastest!