Looping, Close Connection to Access, Faster Processing?

[ilcaa]

i have a vba function within EXCEL that creates a connection to a Access database, and opens a query to retreive a value from Access and place it in a cell in Excel.

I now want to loop 300,000 rows in excel and run this function, so for each row, it will take 2 values and use this function to retreive data from Access. It will open and close the connection for each loop.

QUESTION: Would it be faster, more efficient, less memory usage, etc If I opened the connection within a Subroutine first and left it open during the entire 300,000 row loop then close it.

Or opening and closing the connection for each run is ok, any best practices for connecting to Access on large VBA excel loops? ** this function only retrieves 1 value not a list or array.

SUB LoopClosePrice

set rng = range("a2:a300000")

for each Cell in Rng
sym = cell.offset(0,-1)
date1 = cell.offset(0,-2)
cell.value = GetClosePrice(sym, date1)
next

END SUB

FUNCTION GetClosePrice (ticker as string, date1 as string)

dim rs as New ADODB.RecordSet
dim cn As New ADODB.Connection

cn.Open ConnectionString

Dim Query as String
query = ' query to retrieve '

rs.Open query, cn, adOpenForwardOnly, adLockReadOnly

GetClosePrice = rs.Fields(0)

set rs = Nothing
set cn = nothing
END FUNCTION

 

[Kyle123]

Yes, opening and closing connections is slow.

It would be much better if you re-wrote your query so it didn't need running 300,000 times

 

[xenou]

I think I would like to just pull all the data into Access at once. Then you can run it locally in Access. Plus (assuming you pull the data into a temp table or staging table) it can also be appropriately indexed in your staging table where you hold the data locally - another plus for faster searches.

 

[ilcaa]

my query is for a retrieval of the data in Access from Excel, the 300,000 is because I have a range in Excel thats looped, and uses the query in Excel VBA to get the price. there are 7,000 U.S. stock symbols but I have 300,000 rows because I need price of a specific date. 1 symbol could have 30+ different dates, etc, thats why there are 300,000 rows to loop.

Date, Symbol, ClosingPrice
4/15/2014, GM,
09/17/2014, GM,
12/03/2014, GM,
12/20/2014, GM,
etc
etc

So yes constantly opening and closing connections makes the loop in Excel much slower. And doing this process in Access would be more efficient.

Can I port the exact VBA code from Excel to Access? Or is there some syntax adjustments I need to make?

 

[xenou]

Well really you just need to pull the data into Access. Create a table. Done. Why loop at all? Once in Access, you access data using queries or DLookup functions.