I've created the below code to process imported data into the required format, currently it's taking just over 2 seconds per record, has anyone any idea's how I can speed it up - I have around 30,000 records to process.

Public Function CombineFields(UniqueCompositeKey)

Dim recTopics As Recordset, i As Integer

CombineFields = UniqueCompositeKey
Set recTopics = CurrentDb().OpenRecordset("SELECT Topics.Topic FROM Topics WHERE (((Topics.RecID) = '" & UniqueCompositeKey & "'));")

    For i = 1 To 8
         If recTopics.EOF Then GoTo NextStage:
         CombineFields = CombineFields & "," & recTopics(0).Value
         recTopics.MoveNext
    Next

NextStage:
recTopics.Close

    For i = i To 8
         CombineFields = CombineFields & ","
    Next

End Function

Thanks Ben

I can think of a few things that might help. What database is it and can you provide an example of what UniqueCompositeKey looks like and an example of the desired output?

--

Joe

Hi Joe,

Thanks for the reply, sample data is below, with desired output below that.

Unique Key Topic
01/01/2002,07:33:00,3730603,jglixxun Referred to MD team
01/01/2002,08:02:00,3780400,nveggehh Exceeded Credit Limit
01/01/2002,08:02:00,3780400,nveggehh OTS Discon/Canx query
01/01/2002,08:03:00,3760007,zejibodu Explaining # Button
01/01/2002,08:03:00,3730803,ddaarkal Ledger Balance

01/01/2002,07:33:00,3730603,jglixxun,Referred to MD team,,,,,,,
01/01/2002,08:02:00,3780400,nveggehh,Exceeded Credit Limit,OTS Discon/Canx query,,,,,,
01/01/2002,08:03:00,3760007,zejibodu,,,,,,,
01/01/2002,08:03:00,3730803,ddaarkal,,,,,,,

or

a table containing the following fields; date,time,[u]RecID</u>,reference,f1,f2,f3,f4,f5,f6,f7,f8

I'll have a go, which version of Access are you using?
As a matter of interest your Table structure seems very strange and why do you want a non-normalised output like this anyway?



--

Joe

Try this, I'm a bit uncertain about the final part of your function, you may want to add that bit back in. --

Joe

Hi Joe

Thanks for that, I will give it a go and post the time saving.

I am using Access 2000, in this case I'm not using it as a relational database, I'm merely using it to parse some data I'm sent in Excel Format, I am cleansing the data and making a CSV file to import into my Main DB (which is relational).

The original method took 19.768 seconds to process 8 records
The method you proposed took 19.896 seconds to process 8 records

Not much in it really, but looking at the two methods, I do think your method is tidier and more scalable.

Strange - I was posting the results to the debug window to ensure they were correct and both methods seem to process the first record twice?????

DAO has its speed advantages against an mdb backend. If you are calling this function from an external looping procedure to process the records, you would do well to create a database variable one time outside the loop in the calling function and pass it in as a parameter instead of using a static variable. I've used a static database variable in this example because calling CurrentDb 30,000 times over a LAN is going to be a killer. You can consider an optional parameter to kill the static variable, especially in an environment where there are many users and you will want to release the static pointer. Explicit type declarations help where possible, but GetRows returns a variant array (which is what a recordset is anyway) but it is strongly typed here for clarity. I would consider writing this procedure in line with a caller that is iterating 30,000 records. Unioning a literal apostrophe apostrophe empty string guarantees at least 8 records are returned and you avoid the loop concatenation and the EOF test. Let us know how this fares:

Public Function CombineFields(UniqueCompositeKey As String) As String

Dim recTopics As DAO.Recordset
Dim arr as Variant
Static db as DAO.Database

    If db Is Nothing Then Set db = CurrentDb

Set recTopics = db.OpenRecordset("SELECT TOP 8 Topics.Topic FROM Topics WHERE Topics.RecID = '" & UniqueCompositeKey & "' Union All Select '' From Topics Union All Select '' From Topics " & _
  "Union All Select '' From Topics Union All Select '' From Topics Union All Select " & _
  "'' From Topics Union All Select '' From Topics Union All Select Union All Select '' " & _
  "From Topics Union All Select '' From Topics ")

     arr = recTopics.GetRows(8)
     CombineFields = UniqueCompositeKey & "," & arr(0, 0) & "," & arr(0, 1) & "," & arr(0, 2) & _
            "," & arr(0, 3) & "," & arr(0, 4) & "," & arr(0, 5) & "," & arr(0, 6) & "," & arr(0, 7)

recTopics.Close
End Function



Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

I've messed up the recordset string a bit by leaving out a table name in one place. It's a bit more presentable below:

Set recTopics = db.OpenRecordset("SELECT TOP 8 Topics.Topic FROM Topics WHERE " & _
  "Topics.RecID = '" & UniqueCompositeKey & "' " & _
  "Union All Select '' From Topics Union All Select '' From Topics " & _
  "Union All Select '' From Topics Union All Select '' From Topics " & _
  "Union All Select '' From Topics Union All Select '' From Topics " & _
  "Union All Select '' From Topics Union All Select '' From Topics")

The intent was to Union All the empty string 8 times.


Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]