DAO vs ADO for running sql from external DB (1 Viewer)

Inspired · Feb 13, 2011

Hi All,

As I am manipulating huge volumes of data using VBA and SQL, the Access database I’m using (the master database) becomes prone to exceeding it’s 2gb limit. In order to solve this problem, I am trying to do most of the manipulations/calculations in an external (secondary) database, and then periodically compact and repair this secondary database. Some of the manipulations I have to do involve using numerous make table SQL queries and multiplying/adding etc large matrices of data (e.g. multiplying a 1000x50 matrix by another 1000x50 matrix). Given the nature of my task, what would be the most efficient/quickest way to connect to the secondary database and run the SQL statements/queries?

Moreover, can anyone provide an example of the syntax I would have to use with each method (ADO or DAO).

Thanks for your help.

P.S. As far as I’m aware, using DAO, I would have to use something along the following lines.

Code:

[FONT=Verdana]Dim strSecondaryDB As String[/FONT]
[FONT=Verdana]Dim strMasterDB As String[/FONT]
[FONT=Verdana]Dim strSQL As String[/FONT]
[FONT=Verdana]Dim appAccess As Access.Application[/FONT]
 
[FONT=Verdana]Set appAccess = CreateObject("Access.Application")[/FONT]
 
[FONT=Verdana]strMasterDB = CurrentDb[/FONT]
[FONT=Verdana]strSecondaryDB = "full path to secondary db"[/FONT]
[FONT=Verdana]appAccess.OpenCurrentDatabase (strSecondaryDB)[/FONT]
 
[FONT=Verdana]strSQL = "a make table query etc"[/FONT]
 
[FONT=Verdana]appAccess.DoCmd.RunSQL strSQL[/FONT]

Banana · Feb 13, 2011

Wouldn't it be simpler to use a different backend such as SQL Server Express, MySQL, PostgreSQL or several other free RDBMS instead of wrangling with two databases? Note that assuming that if your application is well-designed, the move usually makes no difference from the client's POV, though there are definitely additional consideration when you use such backend.

Wherever I've used MySQL or SQL Server as the backend, I'd say the proportion was roughly 95% DAO and 5% ADO (yes, I use both in same project).

Inspired · Feb 13, 2011

Thanks for your reply. Unfortunately, this is at work and I'm restricted in that I have to use Access. However, they are working on getting SQL Server, but in the mean time I'm pretty much stuck in this respect....

Banana · Feb 13, 2011

Okay, that's good to know.

Just to be clear that I understand your problem here - the issue is basically the need to retrieve a set of data from both databases for further processing, correct? We're not concerned with users needing to update or modify data from either database, correct? (I hope not, as that would be a nightmare!

)

If so, here's my proposal - don't do any work in either database. Rather, create a new temporary file and use it as your workspace, pulling in all data you need into that temporary file and do what you need to do then when you're done, delete that file. That removes any needs to do compact on either database and because you only need to pull data that is requested by user once (per request) into that work database, you don't have to worry so much about contention on either database.

BTW, since it's pure Access solution, I'd suggest that there's very little benefit, if any, of using ADO in that scenario. ADO really is a wonderful tool to have when working with non-Access backend but even so, DAO can do many things already and usually just as efficiently.

Does that help?

Galaxiom · Feb 13, 2011

They are "working on getting SQL Server"? How hard can it be?

The Express versions are free. They can start with that and easily upgrade to the full version if they ultimately need it.

SQL Server 2005 Express will handle 4GB while 2008 can do 8GB.

But either way, as Banana said, just use a throwaway database for the temporary stuff. This database can be local so it can potentially save quite a lot of network traffic.

Temporary tables should never be stored in either the Back End or the Front End.

Banana · Feb 13, 2011

Galaxiom -

FWIW, a client I worked with before took about 2-3 months before a SQL Server could be installed. The only reason it took that long was because they had to submit a request, add it to the budget, prove that they had the resources to support, and whether the forecast allows for increased expenses due to new installation. A lot of red tapes for nothing, that's for sure and it wasn't an option for them to install themselves - they have no permissions to install a SSEE anywhere.

One nice thing about not working in a corporate environment is that the employees are free to fly by the seat of the pants. On the same token, it's usually what get them in trouble in first place.

Galaxiom · Feb 13, 2011

I guess I am lucky being one of the two Admins on our network.

We still have to get things that cost approved but otherwise we do pretty much what we like.

Inspired · Feb 14, 2011

Banana said:
Okay, that's good to know.

Just to be clear that I understand your problem here - the issue is basically the need to retrieve a set of data from both databases for further processing, correct? We're not concerned with users needing to update or modify data from either database, correct? (I hope not, as that would be a nightmare! )

If so, here's my proposal - don't do any work in either database. Rather, create a new temporary file and use it as your workspace, pulling in all data you need into that temporary file and do what you need to do then when you're done, delete that file. That removes any needs to do compact on either database and because you only need to pull data that is requested by user once (per request) into that work database, you don't have to worry so much about contention on either database.

BTW, since it's pure Access solution, I'd suggest that there's very little benefit, if any, of using ADO in that scenario. ADO really is a wonderful tool to have when working with non-Access backend but even so, DAO can do many things already and usually just as efficiently.

Does that help?

Essentially, I’m only really using the secondary database for doing calculations, creating, using and deleting temporary tables. I need to do the same series/steps of calculations 1000’s of times, and if Access didn’t have a 2gb limit, I could probably do it all in one database. So far, with what I’ve tried, it appears to be working by utilising a secondary database and periodically compacting and repairing it. I do like your idea of creating a file and deleting it, but in this scenario where I’d have to repeat that step 1000’s of times, I’ll save it as an idea for next time instead.

I think I’ll be sticking to DAO as from further reading and what you mentioned, it does appear that using DAO is perfectly sufficient in my scenario.

Thanks

Inspired · Feb 14, 2011

GalaxiomAtHome said:
They are "working on getting SQL Server"? How hard can it be?

It’s similar but worse than what Banana mentioned. You’d actually have to see it to believe it - it’s an absolute nightmare.

As I work in a very large company, the bureaucracy involved in submitting a request and justifying a business case (in the cost cutting environment we’re in) has turned a seemingly simple task, into a lengthy arduous process.

Moreover, as we are constrained in that we HAVE to use our sister IT company for anything IT related, they are able to charge us extortionate fees (there is no free market relationship), and are able to dictate to us exactly what solution they will allow us to have (how much control over the front/back end etc).

When you put everything together, you now understand why we haven’t got SQL Server yet! In fact, even if we wanted the free version of SQL Server Express, we’d have to go through the red tape of having our sister IT company approve it.

Banana · Feb 14, 2011

OT: You know, probably the biggest irony here is that the measures implemented to determine whether one would save costs by doing X instead of Y can easily end up costing more than doing either X or Y! It'd be much simpler if the corporation simply allowed a portion of IT infrastructure to be, for lack of better terms, "unmanaged" so SSEE or other software can be deployed but with the understanding that there's no official IT support. At least, people has resources they can serve themselves with and when there's provided need (and history to support it), it's easier to move an unmanaged resource to managed. In fact, that is exactly the proposition that Access brings to the table - nobody has to get approval to build an Access database and yet many IT people don't get why there's so many Access databases.

But what do I know? I'm just a lowly code monkey.

Inspired · Feb 14, 2011

That is very true, the amount of “waste” I have seen (not just with our IT policies) due to extra bureaucracy and unnecessary measures in place, is staggering. I suppose in a very large organisation there is a need to have consistency and rules in place vis-à-vis anything IT related, as not all individuals/teams will have the knowledge/capability to manage their own IT needs. However, when it comes to enforcing these rules there should be room for compromise/manoeuvre, which there very clearly isn’t where I work.

DAO vs ADO for running sql from external DB (1 Viewer)

Inspired

Registered User.

Banana

split with a cherry atop.

Inspired

Registered User.

Banana

split with a cherry atop.

Galaxiom

Super Moderator

Banana

split with a cherry atop.

Galaxiom

Super Moderator

Inspired

Registered User.

Inspired

Registered User.

Banana

split with a cherry atop.

Inspired

Registered User.

Similar threads

Users who are viewing this thread