What is quicker Access connection or VBA

[mahmed1]

Hi All,

I have created a connection to an Access database using the query wizard

I have 6 tables that are connected to this same database but the parameters are different on each 1

now when I refresh the Connection for each 1 it takes all in all around 1 min to refresh all..

If I was to right some VBA code to retrieve data from access and paste into the relevant tables, which 1 is quicker as with VBA I am opening the connection just the once...where as refreshing all the connections I’m assuming connects several of times...

I know there is so much more flexibility with VBa but wanted to know if it is as quicker

hopefully someone can explain

thank you

 

[CSmith]

Really depends on how/what data is being accessed.

For example a query using VBA with a select statement to the database should be quicker than say a filter applied to ALL records retrieved.

 

[Micron]

I'd say it might depend on what you mean by "connection" - an ADODB connection? Your post reads like this is about linked tables, not a connection:

I have 6 tables that are connected to this same database

Once linked, there is no refresh needed as that happens when you open the db containing the linked tables.

If I was to right some VBA code to retrieve data from access

Why, when you can just link to Access data from Access? VBA should not be your 'go to' approach when simple queries will do the job much easier and more efficiently.

 

[CSmith]

Reads like Access to Excel connection...

 

[mahmed1]

I'd say it might depend on what you mean by "connection" - an ADODB connection? Your post reads like this is about linked tables, not a connection:

Once linked, there is no refresh needed as that happens when you open the db containing the linked tables.

Why, when you can just link to Access data from Access? VBA should not be your 'go to' approach when simple queries will do the job much easier and more efficiently.

Thank you for your feedback..i didnt know which was quicker hence why i asked.

other issue i am having tho is when i tried to set up a connection at work to accessand navigate to the folder, it threw up an error saying i cant get to that folder (Maybe ITdept blocked it) but i do have access to that folder so not sure why i wont be able to connect)

i had to map a new drive to allow me to connect which is not ideal..anyone know a better way to get around it?

 

[xenou]

Mapped drive will be fine. Probably you cannot connect using a UNC path ( such as \\server\directory\file.accdb )

 

[mahmed1]

Hi Xenou - how do i get around the UNC thing

 

[xenou]

Map the path to a drive letter.

 

[mahmed1]

Thank you