UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Replacing VBA With Something Else---reference For Getting Started    
 
   
coastieRPA
post Sep 2 2013, 12:54 PM
Post#1



Posts: 60
Joined: 29-March 08
From: Washington, DC


Hello,
I have some lengthy VBA code for one of my Access Databases. I was told that if I replace the VBA with C++ or something else, the code would run much much faster (the code essentially takes an a single input table, runs a lot of calcs, and then outputs a single table). Would someone please recommend a book or books on this subject? I don't even know the terminology to do a good search in UtterAccess (would this be an Add-In?). I need the whole thing to stay within the accdb.
Thank you in advance,
Russ
Go to the top of the page
 
theDBguy
post Sep 2 2013, 01:17 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,199
Joined: 19-June 07
From: SunnySandyEggo


Hi Russ,
Not sure what you mean because Access cannot run C++ code within the ACCDB file. If you convert your VBA code to C++, you'll have to compile it to a EXE file and then execute it using VBA but it will run external of Access.
Go to the top of the page
 
coastieRPA
post Sep 2 2013, 01:23 PM
Post#3



Posts: 60
Joined: 29-March 08
From: Washington, DC


Hi,
Yes, I knew I was confused when asking the question. I guess my intent is like Excel's Solver or something. I was thinking it would be a separate module or something, not in the VBA editior of course, but something like that...does that help?
Thanks,
Russ
Go to the top of the page
 
TheSmileyCoder
post Sep 2 2013, 01:42 PM
Post#4


UtterAccess VIP
Posts: 1,506
Joined: 19-January 12
From: Denmark, Copenhagen


While the code might execute faster in C, I find it much more likely (and less work) that your existing code can be optimized within VBA. Depending on what your doing, even simple things like adding a index to your tables might speed up the function by a factor of 10 (Yes, I have seen that myself), in a case where a forgot to add a index to a field, that should obviously have been indexed. If you are working on entire tables (or subsets of tables) its also a good chance that some of your VBA code can be moved to a query for better performance.
Go to the top of the page
 
theDBguy
post Sep 2 2013, 01:48 PM
Post#5


Access Wiki and Forums Moderator
Posts: 71,199
Joined: 19-June 07
From: SunnySandyEggo


Hi Russ,
You may be referring to an Add-In. If so, I think you'll still end up using VBA for that. Going back to using C++, you don't really need to create a EXE file. Instead, you can build a DLL and add a reference to it in your Access project.
By the way, how slow is your current code?
Go to the top of the page
 
coastieRPA
post Sep 2 2013, 02:42 PM
Post#6



Posts: 60
Joined: 29-March 08
From: Washington, DC


TheSmileyCoder,
understand and agree. I'm using moving from arrays to Linked Lists and Heaps, so I know that will also help.
theDBguy,
This code is an assignment algorithm, called the Hungarian Algorithm. My current code runs in O(n^3) time, where n is less than 100. This takes about 8 to 10 seconds, including moving the data from a query and putting it into a NxN VBA array. It also includes moving the results from a 2xN VBA array into a table for reports and such. My next step is to increase my problem from about 100 to 2000, which (based on my cocktail napkins calculations) would take about 18 hours. <
All,
Would Add-Ins be faster than regular modules? If it's all VBA, I'm surprised that is the case. If it would be faster, can you recommend a book? I found a msdn article about Access Add ins at http://msdn.microsoft.com/en-us/library/of...office.10).aspx, but I also found something called COM Add Ins for excel at http://msdn.microsoft.com/en-us/library/of...office.10).aspx
Thank you!
Russ
Go to the top of the page
 
TheSmileyCoder
post Sep 2 2013, 03:25 PM
Post#7


UtterAccess VIP
Posts: 1,506
Joined: 19-January 12
From: Denmark, Copenhagen


I see. Maybe I jumped to conclusions a bit to fast, but I often see people going through all sorts of trouble when the easiest solution was to look at their own code (multiple lookups within a loop, that sort of thing).
If it is just a add-in it will NOT run faster, of that I am fairly confident. I don't know how much faster the code would execute if written as a dll and linked from access.
But lets try turning it around a bit, if you COULD get a factor of 10 improvement from such a method, it would still take 1.8 hours. Is that even remotely acceptable? I have no clue as to what the factor might be between internal VBA and external dll, I just wanted to spit that out as something to think about.
Go to the top of the page
 
coastieRPA
post Sep 2 2013, 05:15 PM
Post#8



Posts: 60
Joined: 29-March 08
From: Washington, DC


TheSmileyCoder,
Well, I think 30 minutes is about the max my users want to go...but since this is a 3 months manual process for half a dozen people, I think 2 hours would be good too!
I think changing the data structures will significantly improve run time, but I haven't finished that part yet.
There are really two steps, I guess...1) Staying within VBA modules and changing the data structures, algorithim, etc and 2) changing from VBA to something else. I'm on track to finish part 1, but I was thinking if I could do both parts at once, then even better.
Looks like I'll stick to VBA modules for now.
Thanks again,
Russ
Go to the top of the page
 
BananaRepublic
post Sep 3 2013, 07:57 AM
Post#9


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Russ, without knowing more about what you are doing with data from query and why you have to use Hungarian Assignment algorithm, I wanted to least put out few points to consider:
) As was pointed out, there are usually easier way to optimize, such as writing efficient query and creating indices or revising the VBA code. I would do that first and only if it's still too slow, would I consider writing the code into C++ or whatever and compile a DLL to go with the Access application.
2) If you opt to write it in C++ (or other languages), you do need to be aware of how calling conventions works and use __stdcall for your publicly exposed function and furthermore use data types that are compatible with VBA data types -- that is, you can only pass in and out data types that both languages can handle. This seems obvious but I point that out because there are number of data types that are not directly compatible in VBA so you have to be aware of what you can pass in and how it will be represented in VBA.
3) If you can cut down the number of times you need to transverse the boundary between VBA and DLL, the better. That is, repeatably calling a DLL function to do a single step of calculation wouldn't be as efficient as passing in whole array, doing all calculation steps in the DLL then return the output. Of course, that is not always feasible especially if you need to modify inputs for each calculation step but whenever possible, cut it down. By the same token, if you are using a query to build resultset and want the calculation in the query, you could call the DLL function directly from the query, cutting down on the layers required to transverse. Of course, this also assumes you have good error handling (e.g. returning "#Error" or maybe Null in event of failure rather of a hard error which may cause problems.
All in all, it is possible to use a DLL to speed up some of calculations since you can take more control, but this typically takes much more work than optimizing queries and VBA.
HTH.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 02:07 PM