Need help with updating with value from another database

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Right... I've neglected some basics (losing my mind). Should have
single quote delimiters around parameter VName. The d_DateTime subquery
should return a DateTime data type so it doesn't need any delimiters.

UPDATE VehDetail

SET LastOdometerDate = DMax("d_DateTime",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName = '" & VehDetail.VName & "'" ),

LastOdometerReading = DMax("d_OdometerTenths",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName = '" & VehDetail.VName & "'" &
" AND d_DateTime = (SELECT Max(d_DateTime)
FROM [Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA
WHERE d_RemoteName = '" & VehDetail.VName & "')" )

It might be possible that the use of concatenation and string delimiters
is not needed. E.g.:

... "d-RemoteName = VehDetail.VName")

I'm not sure - test & see.

Also, not sure if the subquery in the LastOdometerReading setting will
work. If not, you'll have to create another DMax() function inside the
DMax() function - fun!

... " AND d_DateTime = DMax("d_DateTime",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName = '" & VehDetail.VName & "'" )

Another solution is to dump the aggregated data into a temp table & use
that table's data as the right-side of the SET expressions. E.g.:

UPDATE VehDetail
SET LastOdometerDate = (SELECT MaxOfDateTime FROM temp
WHERE d_RemoteName = VehDetail.VName),
LastOdometerReading = (SELECT MaxOfOdometer FROM temp ...

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQy9IHIechKqOuFEgEQJg8gCfTHKiqjUyGbN5ag0mDYjbBP s9xTUAoPmk
OO5ho02bDuWymoR5Y0/N4UBM
=zS4j
-----END PGP SIGNATURE-----

Alpha wrote:
The "&", are they being used to concatenate a string?

"MGFoster" wrote:


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ah, yes. I forgot that Access is stupid about ANSI-92 UPDATE
statements. It won't allow aggregate subqueries in the SET clause.
Therefore, you have to use domain aggregate functions DMax(), DLookup(),
etc., which slows down UPDATES on large data sets.

Try the below (untested):

UPDATE VehDetail

SET LastOdometerDate = DMax("d_DateTime",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName = " & VehDetail.VName ),

LastOdometerReading = DMax("d_OdometerTenths",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName = " & VehDetail.VName &
" AND d_DateTime] = (SELECT Max(d_DateTime)
FROM [Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA
WHERE d_RemoteName = " & VehDetail.VName & ")" )

--
MGFoster:::mgf00 at earthlink decimal-point net

I 've modified the query according to the sytax for DMAX in the Access books
online which could accept 3 strings as paramenters. That doesn't work and
has syntax errors that I can't find. I also tried just setting first field
and that gives me a "Unknown" display in a messagebox without further
information.

I'm sorry to keep bugging you but I really need to get this working and
can't figure out how to do this at all.

Thanks, Alpha


UPDATE VehDetail

SET LastOdometerDate = DMax("d_DateTime",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName = VehDetail.VName" ),

LastOdometerReading = DMax("d_OdometerTenths",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName =VehDetail.VName
AND d_DateTime = DMax("d_DateTime",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName =VehDetail.VName")" );

"MGFoster" wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Right... I've neglected some basics (losing my mind). Should have
single quote delimiters around parameter VName. The d_DateTime subquery
should return a DateTime data type so it doesn't need any delimiters.

UPDATE VehDetail

SET LastOdometerDate = DMax("d_DateTime",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName = '" & VehDetail.VName & "'" ),

LastOdometerReading = DMax("d_OdometerTenths",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName = '" & VehDetail.VName & "'" &
" AND d_DateTime = (SELECT Max(d_DateTime)
FROM [Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA
WHERE d_RemoteName = '" & VehDetail.VName & "')" )

It might be possible that the use of concatenation and string delimiters
is not needed. E.g.:

... "d-RemoteName = VehDetail.VName")

I'm not sure - test & see.

Also, not sure if the subquery in the LastOdometerReading setting will
work. If not, you'll have to create another DMax() function inside the
DMax() function - fun!

... " AND d_DateTime = DMax("d_DateTime",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName = '" & VehDetail.VName & "'" )

Another solution is to dump the aggregated data into a temp table & use
that table's data as the right-side of the SET expressions. E.g.:

UPDATE VehDetail
SET LastOdometerDate = (SELECT MaxOfDateTime FROM temp
WHERE d_RemoteName = VehDetail.VName),
LastOdometerReading = (SELECT MaxOfOdometer FROM temp ...

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQy9IHIechKqOuFEgEQJg8gCfTHKiqjUyGbN5ag0mDYjbBP s9xTUAoPmk
OO5ho02bDuWymoR5Y0/N4UBM
=zS4j
-----END PGP SIGNATURE-----

Alpha wrote:
The "&", are they being used to concatenate a string?

"MGFoster" wrote:


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ah, yes. I forgot that Access is stupid about ANSI-92 UPDATE
statements. It won't allow aggregate subqueries in the SET clause.
Therefore, you have to use domain aggregate functions DMax(), DLookup(),
etc., which slows down UPDATES on large data sets.

Try the below (untested):

UPDATE VehDetail

SET LastOdometerDate = DMax("d_DateTime",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName = " & VehDetail.VName ),

LastOdometerReading = DMax("d_OdometerTenths",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName = " & VehDetail.VName &
" AND d_DateTime] = (SELECT Max(d_DateTime)
FROM [Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA
WHERE d_RemoteName = " & VehDetail.VName & ")" )

--
MGFoster:::mgf00 at earthlink decimal-point net

I tried the following and it also gives me the "Unknown" message box.

UPDATE VehDetail

SET LastOdometerDate = DMax("d_DateTime",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"DATA.d_RemoteName = VehDetail.VName" ),

LastOdometerReading = DMax("d_OdometerTenths",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"DATA.d_RemoteName =VehDetail.VName");


"MGFoster" wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Right... I've neglected some basics (losing my mind). Should have
single quote delimiters around parameter VName. The d_DateTime subquery
should return a DateTime data type so it doesn't need any delimiters.

UPDATE VehDetail

SET LastOdometerDate = DMax("d_DateTime",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName = '" & VehDetail.VName & "'" ),

LastOdometerReading = DMax("d_OdometerTenths",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName = '" & VehDetail.VName & "'" &
" AND d_DateTime = (SELECT Max(d_DateTime)
FROM [Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA
WHERE d_RemoteName = '" & VehDetail.VName & "')" )

It might be possible that the use of concatenation and string delimiters
is not needed. E.g.:

... "d-RemoteName = VehDetail.VName")

I'm not sure - test & see.

Also, not sure if the subquery in the LastOdometerReading setting will
work. If not, you'll have to create another DMax() function inside the
DMax() function - fun!

... " AND d_DateTime = DMax("d_DateTime",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName = '" & VehDetail.VName & "'" )

Another solution is to dump the aggregated data into a temp table & use
that table's data as the right-side of the SET expressions. E.g.:

UPDATE VehDetail
SET LastOdometerDate = (SELECT MaxOfDateTime FROM temp
WHERE d_RemoteName = VehDetail.VName),
LastOdometerReading = (SELECT MaxOfOdometer FROM temp ...

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQy9IHIechKqOuFEgEQJg8gCfTHKiqjUyGbN5ag0mDYjbBP s9xTUAoPmk
OO5ho02bDuWymoR5Y0/N4UBM
=zS4j
-----END PGP SIGNATURE-----

Alpha wrote:
The "&", are they being used to concatenate a string?

"MGFoster" wrote:


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ah, yes. I forgot that Access is stupid about ANSI-92 UPDATE
statements. It won't allow aggregate subqueries in the SET clause.
Therefore, you have to use domain aggregate functions DMax(), DLookup(),
etc., which slows down UPDATES on large data sets.

Try the below (untested):

UPDATE VehDetail

SET LastOdometerDate = DMax("d_DateTime",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName = " & VehDetail.VName ),

LastOdometerReading = DMax("d_OdometerTenths",
"[Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA",
"d_RemoteName = " & VehDetail.VName &
" AND d_DateTime] = (SELECT Max(d_DateTime)
FROM [Database=C:\VMS\VMSDB\Ats20050907Db.mdb;].DATA
WHERE d_RemoteName = " & VehDetail.VName & ")" )

--
MGFoster:::mgf00 at earthlink decimal-point net