Need a quicker way to run this code

[Demarc]

Hi all,



I have this function which is called in excel (see code below) that is called in a larger sub while creating a spreadsheet, yhe larger sub takes 13 seconds to complete. The function below is called 135 times during this sub and takes 0.06 seconds to complete each time which is 8.1 seconds of the total 13 seconds the larger sub takes to complete.

Is there a more efficient way to write this code?

The table that the function is interrogating has about 6000 records with 17 individual sets of data per record, can I place all of this table into an array for the duration of the larger sub and then take information from it 135 times, this way I only establish a connection once? Will holding this much data in memory slow the sub down?

Not sure if I have used the correct terminology here but I hope this makes sense.

Function LocDB(Name As Variant, Col As Integer) As Variant

Dim strRec As Variant

'## Returns data from the table based on the Location Name and the column i.e. col 3 returns Lat

'get the number of records
Call openConnection

Select Case Col

      Case 2
          strSql = "SELECT Type FROM tblLocation WHERE [Location Name] = """ & Name & """"
      Case 3
          strSql = "SELECT Latitude FROM tblLocation WHERE [Location Name] = """ & Name & """"
      Case 4
          strSql = "SELECT Longitude FROM tblLocation WHERE [Location Name] = """ & Name & """"
      Case 5
          strSql = "SELECT Elevation FROM tblLocation WHERE [Location Name] = """ & Name & """"
      Case 6
          strSql = "SELECT Variation FROM tblLocation WHERE [Location Name] = """ & Name & """"
      Case 7
          strSql = "SELECT Lat FROM tblLocation WHERE [Location Name] = """ & Name & """"
      Case 8
          strSql = "SELECT Long FROM tblLocation WHERE [Location Name] = """ & Name & """"
      Case 9
          strSql = "SELECT VarCorr FROM tblLocation WHERE [Location Name] = """ & Name & """"
      Case 10
          strSql = "SELECT FIA FROM tblLocation WHERE [Location Name] = """ & Name & """"
      Case 11
          strSql = "SELECT Fuel FROM tblLocation WHERE [Location Name] = """ & Name & """"
      Case 12
          strSql = "SELECT User FROM tblLocation WHERE [Location Name] = """ & Name & """"
      Case 13
          strSql = "SELECT Lock FROM tblLocation WHERE [Location Name] = """ & Name & """"
      Case 14
          strSql = "SELECT PerfClass FROM tblLocation WHERE [Location Name] = """ & Name & """"
      Case 15
          strSql = "SELECT [Rig Type] FROM tblLocation WHERE [Location Name] = """ & Name & """"
     
End Select


cn.Open strConnection
Set rs = cn.Execute(strSql)
strRec = rs.Fields(0)

If IsNull(strRec) Then strRec = ""

Call closeConnection
    
LocDB = strRec
  
End Function

Thanks for your help,
Marcus

[June7]

I suggest you build the alternate procedure and test.

[CJ_London]

why not just run a query for all fields once then loop through the recordset?

[ssanfu]

I'm not sure where the function is being executed from (Access or Excel), but in Access "Name", "Type" and "Long" are reserved words.

I'd like to see the sub that is calling the function, but I'm with Ajax - I would try a query to get the data.

[Demarc]

Thanks all for the replies,

June7 - Yes I could do this but it is a big job and takes a lot of changing my current code, just want to get alternate ideas for what works faster.

Ajax - do you mean place the whole table in a record set at the start of the procedure and and then each subsequent time I need data loop through the record set to extract the info I need? Another way I was thinking of getting the job done was to open a connection once and leave the connection open for the duration of the run time instead of opening a connection and closing it 135 times? Can this be done? Would this be quicker than looping through a record set each time?

ssanfu - it is being executed from excel, I'm very much a novice in Access but good to know.

Cheers,
Marcus

[ssanfu]

Any chance of seeing the calling sub or maybe you would post the SS?

[Demarc]

Hi ssanfu,

The calling sub is quite large and contains many other subs that also call the function above to extract data from the Access table. The table used to be in the excel WB and I simply used a vlookup to extract the data I wanted. As the table was growing quite large I decided to take the leap into Access but this has doubled the time taken to get the same result. I counted about 35 individual times the above function is called from unique places within the overall code and some of it is in a loop so called multiple times.

I have tried leaving an open connection but found this does not work. I am interested in placing the whole table in an array in access for the duration of the main sub and looping through it each time I need data extracted. My only concern is that looping though 6000 odd records 135 times will also be quite slow.

Cheers,
Marcus

[ssanfu]

OK, it doesn't sound like you want to shou/post the code. Without knowing/seeing WHAT you are doing makes it impossible to suggest methods to speed up the code (if possible).

Good luck with your project....

[Demarc]

Ok after a day of work this is what I have come up with:

Function AllLocDB() As Variant

'get the maximum number of records in tblCrew
Call openConnection

strSQL = "SELECT Count(*) FROM tblLocation;"
cn.Open strConnection
Set rs = cn.Execute(strSQL)
nRecords = rs.Fields(0)

Call closeConnection

'get all Surnames
Call openConnection

strSQL = "SELECT * FROM tblLocation;"
cn.Open strConnection
Set rs = cn.Execute(strSQL)

AllLocDB = rs.GetRows

Call closeConnection

End Function


Sub testfunction()
  
Dim TestArray As Variant 'will be declared as global
Dim i As Integer

TestArray = AllLocDB
 
For i = 0 To nRecords - 1
    If TestArray(0, i) = "YPDN" Then MsgBox i
Next i
  
End Sub

I place the for loop into all the positions the previous function was called and it seems to work.

Haven't quite finished updating all the code but I have reduced the amount of function calls from 135 to 75 and time to complete from 13 to 8 sec.

Essentially I call the AllLocDB function once and assign it to a global array in the excel code and search it each time I need information from it using similar code the i loop in the testfucntion.

Seem to have solved my own problem.

Thanks all for helping.

Marcus