is VB+SQLserver FASTER than Access?

stonexu1984 · May 5th, 2009, 3:04 pm

i am exporting the data from Access to excel file but turns out pretty slow, since i have to put data in the spreadsheet cell by cell. But i know someone is doing the same way using VB+SQLserver, so the software really makes difference?

rmax · May 5th, 2009, 3:39 pm

It depends how they are doing it using VB and SQL - using a straight ADO pipe I would say yes it is quicker - Access is probably using DAO / Jet and also has a whole host of other bits going on I would guess.

stonexu1984 · May 5th, 2009, 5:58 pm

actually I am simply writing a ssql in Access VBA and execute it to extract the value I want from the database and put in the excel spreadsheet cell, and one by one... i think that's either DAO or ADO right?

DominicConnor · May 5th, 2009, 7:06 pm

Are you aware that Excel can read Access databases, which is typically much faster ?

stonexu1984 · May 5th, 2009, 9:15 pm

i create a Access form which has some listbox, buttons... to let me choose what data I want to load to the excel. So i have to write SQL in Access VBA first and then give the data to excel. i am thinking about ur idea, but that's same just like I build the form in Excel, and still write everything in VBA, and plus now I have to link the form to the Access database.

brbred · May 6th, 2009, 3:33 pm

In Excel, the method Range.CopyFromRecordset is pretty fast to copy data.