CODEWITHSUNDEEP
ms-accessvba
GeoffDS
GeoffDS

Reputation: 1271

Write large amounts of data at one time with VBA in Access

I have quite a bit of experience with VBA in Excel and have learned that the interaction between VBA and Excel is slow. Thus, you should read and write large chunks of data at one time instead of one cell at a time.

With VBA in Access, I have very little knowledge/experience, almost none. Is the same sort of thing possible, reading and writing large chunks of data at once? Is it as important, i.e., is it possible that maybe it's not as important because the interaction between Access and VBA isn't as slow? I have no idea. Any ideas would be great.

Thanks

Upvotes: 3

Views: 600

Answers (2)

Stephen Turner
Stephen Turner

Reputation: 7314

It depends on where your data is coming from, if you have it in a format such as CSV or Excel then you can load it as one block and this is quite quick. The same applies to different database formats and ODBC connections, MS Access can process these quite quickly.

In fact there isn't usually much call for reading and writing large chunks of data using VBA at all. Even with some of the most powerful database servers RBAR (Row By Agonising Row) processing can cause a system to grind to a halt. VBA in Access is no exception to this, it's probably very similar to VBA in excel in this respect.

VBA in MS Access is usually used to improve user experience with automation, to speed up complex calculations and repetitive user tasks.

Upvotes: 3

Fionnuala
Fionnuala

Reputation: 91336

Excel is a spreadsheet, Access is a database management application. You should do a little reading:

Fundamentals of Relational Database Design, Paul Litwin, 2003

Fundamental Microsoft Jet SQL for Access 2000
Intermediate Microsoft Jet SQL for Access 2000
Advanced Microsoft Jet SQL for Access 2000

Upvotes: 2

Related Questions