I usually convert the non-updateable query to a 'make table' query
into a temporary d/b that I create in c:\temp
then I create a link to the external table..
the only problem I have is in creating an index for the primary key,
ie.
CurrentDb.Execute "CREATE INDEX primaryKey ON tblRegion(item);"
gives me error - 3611 Can't execute data definition statements on
linked
data sources.
does anyone know how to create an index for a table in an external mdb
?
Steve Jorgensen <nospam@nospam.nospam> wrote in message news:<b28lrvoj3gq3bfn416g39cnocnhvtcngeu@4ax.com>. ..[color=blue]
> On Tue, 18 Nov 2003 20:45:40 GMT,
dXXXfenton@bway.net.invalid (David W.
> Fenton) wrote:
>
> ...[color=green]
> >
> >Can't you use a subquery to return a field in the resultset? That
> >certainly doesn't make the query unupdatable, does it? I mean "$365
> >As Total" for a field in a recordset is not, in fact, updatable,
> >but it doesn't make the whole query updatable. It's only when you
> >have joins that the non-updatability cascades.[/color]
>
> If only it were true - it is in every other SQL implementation I've ever heard
> of, and it's used as an example in pretty much every beginning book on SQL
> I've ever seen. In Access, however, if I execute the following...
>
> UPDATE tblUpdateMaster SET tblUpdateMaster.UpdateMasterTot = (SELECT
> Sum(tblUpdateDetail.DetailValue) FROM tblUpdateDetail WHERE
> tblUpdateDetail.UpdateMasterID=tblUpdateMaster.Upd ateMasterID);
>
>
> I get "Operation must use an updateable query."
>
> I tried this too, but it didn't help...
>
> UPDATE DISTINCTROW tblUpdateMaster SET tblUpdateMaster.UpdateMasterTot =
> (SELECT Sum(tblUpdateDetail.DetailValue) FROM tblUpdateDetail WHERE
> tblUpdateDetail.UpdateMasterID=tblUpdateMaster.Upd ateMasterID);
>[color=green]
> >[color=darkred]
> >>>I've hardly ever needed this kind of thing for an ongoing
> >>>purpose, and since I work only with Jet data, I have the
> >>>DISTINCTROW predicate available to me, which takes care of quite
> >>>a few of these circumstances.
> >>
> >>Hmm, I thought that was no longer a meaningful option. I'll see
> >>if it has any impact.[/color]
> >
> >Well, if your data are stored in something other than Jet, it
> >probably isn't.[/color]
>
> Well, I'm usually talking about JET.
>[color=green][color=darkred]
> >>>I go the temp table route, which isn't that hard to maintain, as
> >>>I just put the temp table in a tmp.mdb permanently living in the
> >>>same folder as the front end, and never bother to compact it,
> >>>ever. It has not been an issue in any app I've ever built like
> >>>this and I've been doing this for at least 5 years.
> >>
> >>The compacting may not be necessary, but if I'm going to go to the
> >>trouble of a temporary database, I usually go all the way and
> >>create it from a template each time using a random name in the
> >>system Temp folder. This way, if the user opens 2 copies or if a
> >>sysadmin installs it incorrectly on a terminal server, it won't
> >>barf.[/color]
> >
> >But it's a black box solution, right? Once implemented, it's not
> >hard to maintain, and you can re-use it in multiple applications.[/color]
>
> That's true, but I still try find solutions that don't require that kind of
> thing if I can. Also, there are so many cases where I get brought in to
> implement a simple feature into someone else's existing app, and it feels like
> overengineering to leave whole new subsystems in an existing app that I'm only
> working on for a week.
>[color=green]
> >I was thinking about this while dropping off to sleep last night
> >(so the thinking may be kind of fuzzy) but is there any way at all
> >to do this with transactions? I believe there isn't. I was thinking
> >in terms of doing the calculations in the inner transaction, using
> >that result to update the outer transaction and then rolling back
> >only the inner transaction. But I'm not sure this is doable or that
> >it would get you anything beyond the temp table route, as all you
> >really avoid is doing the temp table creation on your own, leaving
> >it up to Jet (which does precisely the same thing).[/color]
>
> I'm not certain, but I don't believe that CREATE TABLE is something that can
> be done transactionally. Also, if the temporary table was in the transaction,
> then the update would be, too, so rolling back the transaction would mean that
> nothing was accomplished. As for having JET do temporary tables instead of
> me, if it could be done, that's a good thing because it's me not duplicating
> functionality that's already in JET.[/color]
