DAO vs ADO for running sql from external DB

[Marco123]

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.

Dim strSecondaryDB As String
Dim strMasterDB As String
Dim strSQL As String
Dim appAccess As Access.Application

Set appAccess = CreateObject("Access.Application")

strMasterDB = CurrentDb
strSecondaryDB = "full path to secondary db"

appAccess.OpenCurrentDatabase (strSecondaryDB)

strSQL = "a make table query etc"

appAccess.DoCmd.RunSQL strSQL

 

[dhookom]

I would use DAO. You don't have to create a reference to Access.Application since I assume your code is already running in Access. You could

Dim db As DAO.Database
Set db = OpenDatabase(strSecondaryDB )
' you can now use db.Execute to run queries or SQL statements.

If you are accessing Access tables only, I would stick with DAO.

Duane
Hook'D on Access
MS Access MVP

 

[vbajock]

Make table" queries should be avoided if you want to avoid database bloat. Why not just put these tables into an external mdb, link the tables into your primary mdb, and do a DELETE query followed by an append instead of "make table"? You could also then add a simple routine to compact the secondary mdb from the first.

 

[MajP]

Along with what VBAJock said, you can also then add the second db as a reference in the the first db. Now you can run all the code in the second db as if it was in the first.

 

[Marco123]

Thanks for the ideas but I think for what I’m doing, this method is sufficient. 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.

 

[TheAceMan1]

How are ya Marco123 . . .

[blue] Although the maximum size for a single database file is 2GB, you can work around this limitation by using a [purple]split database[/purple]. A front-end database file can point to thousands of back-end database files, [purple]each of which could be as large as 2GB.[/purple][/blue]

Don't put all your eggs in one basket. Put your tables in more than one backend and link!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Marco123]

It's actually not that I've got that many tables, it's that I need to create and delete temporary tables 1000's of times. Thankfully, it's worked so far.