best practice in locating QueryDefs 1

[VickyC]

hi to all

I have to keep this question in general terms - to go into detail would just drown the central issue.


Imagine VBScript that is used to run code in a CONTROL.mdb file. This CONTROL.mdb code creates a different QueryDef to UPDATE tables in hundreds of EXTERNAL mdb files, each containing many tables, all identical in structure. The VBScript has a loop that keeps feeding the CONTROL.mdb Paths/Names of the external mdb files. I need help with the following - is it generally better to...

a) Use the CONTROL.mdb's code to create a QueryDef IN EACH external mdb file, then use the VBScript code to open these external mdb files and run the queries, or...

b) Use the CONTROL.mdb's code to create a QueryDef IN EACH external mdb file, then use the CONTROL.mdb's code to run these external queries, or...

c) Use the CONTROL.mdb's code to create each QueryDef in CONTROL.mdb, then use the CONTROL.mdb's code to run these queries to UPDATE the external tables.

I already have made good progress in option a), where I can generate the QueryDefs in each of the external mdb files. But, I'm not sure I'm on the best path.

Any help from the gurus out there is really appreciated. I'm really stuggling with the best ways to co-ordinate my CONTROL.mdb, my VBScript, and the External mdb files.

Much thanks in advance
Vicky

 

[SkipVought]

Hi,

This statement intrigues me:

"hundreds of EXTERNAL mdb files, each containing many tables, all identical in structure."

Hundreds of data bases each containing many tables of identical structure. Really?

Could you explain this?

 

[VickyC]

hi SkipVought
It's part of a math research project involving a large amount of data (~ 24 TB)
Because of size limitations in Access (~2GB) and leaving headroom, I'm using many mdb files, each holding 12 tables, each with ~10^6 records.
I'm pretty sure that this type of work might be better handled in, say, SQL Server, but I'm relatively inexperienced on the SQL side of things.

In any case, I went for option c), and it seems to be working well. (relatively fast, no bloating problem, etc)
Vicky

 

[lameid]

I think you went with the right solution as to open each file as a new application has enormous system resources compared to just linking to a file. However it is also true that Queries in the same physical file as the data run faster than queries on linked tables. Typically there are design considerations like keeping all data (tables) and application objects in separate files.

Having said that... if you have a SQL Server avaialble, tables upsize (upsizing wizard) to SQL Server relatively easily and you can link to the tables and use them exactly the same way although there are performance reasons to do more SQL Server native things but it would work. If you are stuck using free/what you already have, SQL Express will not get you to 1 TB let alone 24 and then your solution is better than using SQL Express.

The one thing I would watch out for is Floating Point error if not working with integers.

 

[VickyC]

lameid - thanks for your insights, especially re SQL Server.

I don't have access to Server, but I still wonder if there would be advantages to using SQL Express tables (several instances, but far fewer than the mdb tables I now use).

Thanks again
Vicky

 

[lameid]

My expectation is that the SQL Server Express economies of scale used this way would be much steeper than Access.

Basically each instance is a SQL Server service or database program... If you had enough cores / Boxes and you were doing something simple like a native aggregate function like sum and then adding the responses after the fact AND running the SQL native in SQL Express, that might be Faster... But enough to justify reworking it and the overhead of migrating data? I dunno but likely not. On the other hand, if the results are not readily combined and you have to read each record, any database that is running on the same box on a local disk is faster. MySQL is another possibility... I don't have any experience with it but I suspect it does not have a database size limit like SQL Express. The ideal would to put everything in one database product as it is simpler to manage.

As with any solution, the devil is in the details. There may be nuances to steer one way or the other.

 

[VickyC]

Lameid - lots to think about here. I appreciate your advice when considering whether or not to upgrade. As you've pointed out, there are several factors to consider, and no simple answer.

Vicky