Discuss "Slow Concat Code" in the Microsoft Access forum.

AOG123 · #11 (**permalink**) May 2nd, 2008, 08:09 AM

Hi,.. again,...

have a look at this attachment,.. its fast very fast even accross the network,.. but at the moment its creating a table,.. i must use this in a query as the data must be real time,

Anyone have any ideas on how to convert this to be called direct in a query?

jmurrayhead · #12 (**permalink**) May 2nd, 2008, 08:27 AM

A temp table is a very good idea. I should have suggested it yesterday, but I didn't think of it at the time.

Anyway, I can't think of anyway of calling that routine within the actual query. Why do you want to? What are you trying to achieve by calling it directly from the query?

I'm also thinking this could be simplified if more tables were used with some relationships instead of using comma delimited fields. However, I'm so used to the power of SQL Server and the powerful queries that can be used there, I'm not sure if it would work the same with Access.

AOG123 · #13 (**permalink**) May 2nd, 2008, 10:37 AM

To explain more,.. this is what i have and works fine,..."except having to wait a minute for the concat

When a job is created item required to be purchased are put in the Requests Table

Code:

Tbl_03_Requests
ID #	Part Number	Description	            Status	Qty Req	UOM	Priority	Req #	Work Order
1	114N4082-6	Leading Edge Skin	Stock Required	1	ea	Priority 1		WO07-421

Often the same part is Required on a different job,.

Code:

Tbl_03_Requests
ID #	Part Number	Description	            Status	Qty Req	UOM	Priority	Req #	Work Order
50	114N4082-6	Leading Edge Skin	Stock Required	1	ea	Priority 1		WO07-690

A query then creates a list to purchase

Code:

ID #	Part Number	Description	            Status	Qty Req	UOM	Work Order
1-50	114N4082-6	Leading Edge Skin	Stock Required	2	ea	WO07-421 - WO07-690

The ID is then purchased against on the Order Screen

The Id goes into the purchase order table

The Id is then Split back down to its original numbers

The original numbers then look back at the Request table and update the status to Ordered

Code:

Tbl_03_Requests
ID #	Part Number	Description	        Status	Qty Req	UOM	Priority	Req #	Work Order
1	114N4082-6	Leading Edge Skin	Ordered	1	ea	Priority 1		WO07-421

Code:

Tbl_03_Requests
ID #	Part Number	Description	        Status	Qty Req	UOM	Priority	Req #	Work Order
50	114N4082-6	Leading Edge Skin	Ordered	1	ea	Priority 1		WO07-690

And so on,. When the unit is received it changes the status to received.

Sorry about the Table Formatting,... Can anyone think of another way to do this?

AOG123 · #14 (**permalink**) May 2nd, 2008, 11:25 AM

Found another example,.. but can get it to work,...

error undefined function,...

Can someone try this using my first sample,... thanks

Code:

Public Function fConcatenateRecords(strField As String, strRecordset As String, strFieldSeparator As String) As String
'USAGE:
'       fContatenateRecords(FieldName to concatenate, Table; Query; or SQL SELECT recordset, Separator character)
   Dim curDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim strTemp As String

   Set curDB = CurrentDb
   Set rst = curDB.OpenRecordset(strRecordset)
       With rst
           If .EOF And .BOF Then
           fConcatenateRecords = "" 'no records returned
           Exit Function
       End If

       .MoveFirst
       While Not .EOF
           strTemp = strTemp & .Fields(strField) & strFieldSeparator & " "
           .MoveNext
       Wend
       .Close
   End With

   strTemp = Left(strTemp, Len(strTemp) - (Len(strFieldSeparator) + 1))
   fConcatenateRecords = strTemp

End Function

jmurrayhead · #15 (**permalink**) May 2nd, 2008, 12:57 PM

How are you trying to call this function?

AOG123 · #16 (**permalink**) May 2nd, 2008, 01:30 PM

fContatenateRecords("Table", "fieldName", "Separator character")

fContatenateRecords("FieldName", "Table", "Separator character")

I didn't write it so not sure

if it even works

jmurrayhead · #17 (**permalink**) May 2nd, 2008, 01:31 PM

Quote:

Originally Posted by AOG123

fContatenateRecords("Table", "fieldName", "Separator character")

fContatenateRecords("FieldName", "Table", "Separator character")

I didn't write it so not sure

if it even works

Where are you calling it from? The test form?

AOG123 · #18 (**permalink**) May 2nd, 2008, 01:38 PM

just in the query,. i guess it should work like the other example,.. but maybe not,....

jmurrayhead · #19 (**permalink**) May 2nd, 2008, 01:46 PM

Quote:

Originally Posted by AOG123

just in the query,. i guess it should work like the other example,.. but maybe not,....

Paste what you have here

don94403 · #20 (**permalink**) May 2nd, 2008, 08:13 PM

I posted my contribution on "the other" forum

but will duplicate it here, for the convenience of those who are already here and interested in your problem.

As I said over there, in my opinion, the issue is your schema. It doesn't meet 3NF normalization criteria. The reason that your approach is so slow is that you are calling a user-defined function 3 times in your SQL, and each time it is called, it has to read every record in the database, thus if you have 1,000 records, it will have to read 3 * 1,000 * 1,000, or 3 million records. It gets worse if you have even more records.

I normalized your data into 5 tables and now you don't need any functions or VBA code, just simple queries will do all the work. Access queries are pretty efficient and get optimized by Jet, so you should always let Jet do as much of the job as you can. You paid MS for Jet, so make it pay you back!