Append Query calling Funtion

[jeff]

Hi gurus!

I am running a simple append query to bulk add records into a booking table.
For each booking added I want to include a unique Booking reference
calculated from a Function.

The Function reads a Booking reference format and last number from an Access
table increments it, writes it back to the table and then returns the
formatted Booking reference.

The problem I am having is that the Booking reference function is only being
called once and all the appended bookings have the same Booking Ref.

SQL is :-

INSERT INTO tblMain_CourseBookings ( SchedCourseID, BookingRef, CompanyID,
BookingType, PONo )
SELECT DISTINCT tblMain_CoursesScheduled.SchedCourseID, getNextBookNo() AS
BookingRef, tblMain_CoursesScheduled.CompanyID, "Company" AS BookingType,
ImportedData.PONo
FROM (tblMain_CoursesScheduled INNER JOIN ImportedData ON
tblMain_CoursesScheduled.SchedCourseRef = ImportedData.TARCode) INNER JOIN
tblMain_Company ON ImportedData.CompanyRef = tblMain_Company.CompanyCode;

This is being run as an Access Query def.

Funtion is getNextBookNo() - nothing complex in it and it does the job when
the bookings are being made thru the forms interface.

Can anyone help me out?

cheers
Jeff

 

[Allen Browne]

Jeff, this kind of thing can probably be achieved more easily by using
OpenRecordset, with AddNew and Update to insert the new records. A major
advantage of this approach is that you don't need make many calls to
getNextBookNo(), which probably opens recordsets or does something even
slower such as DLookup(). Holding a couple of recordsets open (e.g. one to
read from and one to add records to), might be an order of magnitude faster.

If you want to use the append query anyway, the problems you will encounter
are:
a) Access may not call the function for every row;
b) The data the function is reading may not be up to date if it relies on
the data you are appending.

The first issue is that the query optimizer sees that it is not passing data
to the query, so it assumes the data will be the same in every row (similar
to calling the Date() function), and so it calls it once for the query only.
You can circumvent that by actually passing something to your query, e.g.:
SELECT DISTINCT tblMain_CoursesScheduled.SchedCourseID,
getNextBookNo(tblMain_CoursesScheduled.SchedCourseID) AS BookingRef,
tblMain_CoursesScheduled.CompanyID, ...
The optimizer then sees it is passing a value that changes, and figures it
had better call it for every row. Of course, you need to modify the function
declaration so it accepts an (optional) argument, but the function doesn't
have to do anything with the data passed in.

The second issue is more difficult. Particularly if the append is happening
in a transaction, there may be no way to get this up to date for your
function to read, so OpenRecordset and AddNew may be your only option.

HTH.

 

[jeff]

Allen

Thanks fellow Perthie!!

This is just for a client who needs to suck in some bulk data for their
system (last years data!!) so it will largely be a one off. I could of
course code it up and generate the BookingRef on adding each record via
recordsets.. yes. But the rest of the import mechanism just uses queries as
it was very easy to do it that way. The data is coming from a flat excel
file.

I had already tried the adding of the ScheduleID to the function call and
not doing anything with it in the function to no avial...thats when I came
here... hoping....

I guess I will just have to write the code...its easy of course...i do a lot
of that anyway ...

thanks for your help
cheers
jeff

BTW I am impressed with your website and background and philosphies...I have
been reading your posts for years!!

 

[Aussie Jeff]

Allen (et al)

I just wanted to add that I have retried the including of the
tblMain_CoursesScheduled.SchedCourseID in the function call and it works a
treat!! Data is now being imported correctly with new booking references for
each entry added by the query!!

CHeers
Jeff