running access 2k; mdb w/ linked tables to another mdb
(front/back-end);
trying to run a query that updates a table FROM information from a
crosstab query.
I AM NOTT trying to update the crosstab query itself - only use
information FROM it to update a perfectly updatable table!
Why the ---- would I get this error, when there are clearly NO
permission issues, or issues in updating the table I'm trying to
update!?!?!?!? (I can clearly update it without the XT)
What the ---- good is a crosstab query, if I can't USE the information
within it for something other than reporting?!?!?
Here's my query (the "XT" table is my crosstab):
I'm guessing that maybe I need to use a sub-query to work around this,
but am not sure how, nor why I should have to go this route....
UPDATE Q_answers_XT LEFT JOIN T_final_answers ON
Q_answers_XT.id = T_final_answers.id
SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a =
[Q_answers_XT]![a];
any help appreciated...
TIA
27  18362 
On 31 May 2006 06:55:25 -0700, "Bob" <bo*************@gmail.com>
wrote:
Yes, that's very annoying. Access apparently is too stupid to
understand this. We'll see about Access 12, but my guess is that 98%
of the effort went into a new UI, and very little into improvements
like this one. My workaround is to write the results of the crosstab
to a "temporary" table, and then use that table to complete the rest
of the update.
-Tom. running access 2k; mdb w/ linked tables to another mdb
(front/back-end);
trying to run a query that updates a table FROM information from a
crosstab query.
I AM NOTT trying to update the crosstab query itself - only use
information FROM it to update a perfectly updatable table!
Why the ---- would I get this error, when there are clearly NO
permission issues, or issues in updating the table I'm trying to
update!?!?!?!? (I can clearly update it without the XT)
What the ---- good is a crosstab query, if I can't USE the information
within it for something other than reporting?!?!?
Here's my query (the "XT" table is my crosstab):
I'm guessing that maybe I need to use a sub-query to work around this,
but am not sure how, nor why I should have to go this route....
UPDATE Q_answers_XT LEFT JOIN T_final_answers ON
Q_answers_XT.id = T_final_answers.id
SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a =
[Q_answers_XT]![a];
any help appreciated...
TIA
By design you can't use a crosstab or any aggregate query in an update query
(except as a subquery in the where clause).
You may have to resort to using the cross-tab to create (or populate) a
temporary work table. Then you can use the work table in a query to update
your fields in T_Final_Answers.
You might be able to use the VBA function - DLookup - to find the value in
the crosstab and use that in your update query. Something like the
following UNTESTED query.
UPDATE T_Final_Answers
SET q = DLookup("q","q_answers_xt","id=" & T_final_answers.Id)
WHERE ID in (SELECT ID FROM q_answers_Xt)
"Bob" <bo*************@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com... running access 2k; mdb w/ linked tables to another mdb
(front/back-end);
trying to run a query that updates a table FROM information from a
crosstab query.
I AM NOTT trying to update the crosstab query itself - only use
information FROM it to update a perfectly updatable table!
Why the ---- would I get this error, when there are clearly NO
permission issues, or issues in updating the table I'm trying to
update!?!?!?!? (I can clearly update it without the XT)
What the ---- good is a crosstab query, if I can't USE the information
within it for something other than reporting?!?!?
Here's my query (the "XT" table is my crosstab):
I'm guessing that maybe I need to use a sub-query to work around this,
but am not sure how, nor why I should have to go this route....
UPDATE Q_answers_XT LEFT JOIN T_final_answers ON
Q_answers_XT.id = T_final_answers.id
SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a =
[Q_answers_XT]![a];
any help appreciated...
TIA
Tom, & John;
I'm very appreciative for your replies.
John; I'll try your suggestion, and see how it goes - I REALLY don't
want to have to create a temp table everytime this needs to happen -
just becomes a compaction nightmare.
This is about as brain-dead, as anything microsoft has ever done; and
they've sure done plenty. Can't tell you how many times their idiosy
has ticked me off.
When the ---- are people going to STOP paying microsoft for BS
marketing improvements, and force them to write good code? (that is
retorical, of course, because I know this will never happen - other
companies follow microsoft's example because people are stupid, or
ignorant enough to let it happen).
ok, ok - close the vents, I'm done blowing steam into the empty vacuum
of microsoft customer awareness...... :-)
T & J - again, I'm very appreciative for your suggestions; clearly, if
John's suggestion fails, I have no choice but to create a temporary
table - again & again & again & again, and compact again & again &
again .....;-O
TX - Bob
John Spencer wrote: By design you can't use a crosstab or any aggregate query in an update query
(except as a subquery in the where clause).
You may have to resort to using the cross-tab to create (or populate) a
temporary work table. Then you can use the work table in a query to update
your fields in T_Final_Answers.
You might be able to use the VBA function - DLookup - to find the value in
the crosstab and use that in your update query. Something like the
following UNTESTED query.
UPDATE T_Final_Answers
SET q = DLookup("q","q_answers_xt","id=" & T_final_answers.Id)
WHERE ID in (SELECT ID FROM q_answers_Xt)
"Bob" <bo*************@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com... running access 2k; mdb w/ linked tables to another mdb
(front/back-end);
trying to run a query that updates a table FROM information from a
crosstab query.
I AM NOTT trying to update the crosstab query itself - only use
information FROM it to update a perfectly updatable table!
Why the ---- would I get this error, when there are clearly NO
permission issues, or issues in updating the table I'm trying to
update!?!?!?!? (I can clearly update it without the XT)
What the ---- good is a crosstab query, if I can't USE the information
within it for something other than reporting?!?!?
Here's my query (the "XT" table is my crosstab):
I'm guessing that maybe I need to use a sub-query to work around this,
but am not sure how, nor why I should have to go this route....
UPDATE Q_answers_XT LEFT JOIN T_final_answers ON
Q_answers_XT.id = T_final_answers.id
SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a =
[Q_answers_XT]![a];
any help appreciated...
TIA
On 31 May 2006 09:52:12 -0700, "Bob" <bo*************@gmail.com>
wrote:
If DB bloat is a serious concern, you could create the "temp" table in
a separate MDB you create on the fly, use for your query, and delete.
Or upsize to SQL Server Express Edition?
-Tom. Tom, & John;
I'm very appreciative for your replies.
John; I'll try your suggestion, and see how it goes - I REALLY don't
want to have to create a temp table everytime this needs to happen -
just becomes a compaction nightmare.
This is about as brain-dead, as anything microsoft has ever done; and
they've sure done plenty. Can't tell you how many times their idiosy
has ticked me off.
When the ---- are people going to STOP paying microsoft for BS
marketing improvements, and force them to write good code? (that is
retorical, of course, because I know this will never happen - other
companies follow microsoft's example because people are stupid, or
ignorant enough to let it happen).
ok, ok - close the vents, I'm done blowing steam into the empty vacuum
of microsoft customer awareness...... :-)
T & J - again, I'm very appreciative for your suggestions; clearly, if
John's suggestion fails, I have no choice but to create a temporary
table - again & again & again & again, and compact again & again &
again .....;-O
TX - Bob
John Spencer wrote: By design you can't use a crosstab or any aggregate query in an update query
(except as a subquery in the where clause).
You may have to resort to using the cross-tab to create (or populate) a
temporary work table. Then you can use the work table in a query to update
your fields in T_Final_Answers.
You might be able to use the VBA function - DLookup - to find the value in
the crosstab and use that in your update query. Something like the
following UNTESTED query.
UPDATE T_Final_Answers
SET q = DLookup("q","q_answers_xt","id=" & T_final_answers.Id)
WHERE ID in (SELECT ID FROM q_answers_Xt)
"Bob" <bo*************@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com... > running access 2k; mdb w/ linked tables to another mdb
> (front/back-end);
>
> trying to run a query that updates a table FROM information from a
> crosstab query.
> I AM NOTT trying to update the crosstab query itself - only use
> information FROM it to update a perfectly updatable table!
>
> Why the ---- would I get this error, when there are clearly NO
> permission issues, or issues in updating the table I'm trying to
> update!?!?!?!? (I can clearly update it without the XT)
>
> What the ---- good is a crosstab query, if I can't USE the information
> within it for something other than reporting?!?!?
>
> Here's my query (the "XT" table is my crosstab):
> I'm guessing that maybe I need to use a sub-query to work around this,
> but am not sure how, nor why I should have to go this route....
>
> UPDATE Q_answers_XT LEFT JOIN T_final_answers ON
> Q_answers_XT.id = T_final_answers.id
> SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a =
> [Q_answers_XT]![a];
>
>
> any help appreciated...
> TIA
>
Don't forget that you can also use a pair of recordsets and use one for the
crosstab and one for the update.
BTW, John;
Just wanted to let you know, that your suggestion did work...
Although dlookup is about as cycle intensive as it gets, I think it's
still better than constantly re-creating, and compacting huge tables...
TX again! -
Bob
John Spencer wrote: By design you can't use a crosstab or any aggregate query in an update query
(except as a subquery in the where clause).
You may have to resort to using the cross-tab to create (or populate) a
temporary work table. Then you can use the work table in a query to update
your fields in T_Final_Answers.
You might be able to use the VBA function - DLookup - to find the value in
the crosstab and use that in your update query. Something like the
following UNTESTED query.
UPDATE T_Final_Answers
SET q = DLookup("q","q_answers_xt","id=" & T_final_answers.Id)
WHERE ID in (SELECT ID FROM q_answers_Xt)
"Bob" <bo*************@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com... running access 2k; mdb w/ linked tables to another mdb
(front/back-end);
trying to run a query that updates a table FROM information from a
crosstab query.
I AM NOTT trying to update the crosstab query itself - only use
information FROM it to update a perfectly updatable table!
Why the ---- would I get this error, when there are clearly NO
permission issues, or issues in updating the table I'm trying to
update!?!?!?!? (I can clearly update it without the XT)
What the ---- good is a crosstab query, if I can't USE the information
within it for something other than reporting?!?!?
Here's my query (the "XT" table is my crosstab):
I'm guessing that maybe I need to use a sub-query to work around this,
but am not sure how, nor why I should have to go this route....
UPDATE Q_answers_XT LEFT JOIN T_final_answers ON
Q_answers_XT.id = T_final_answers.id
SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a =
[Q_answers_XT]![a];
any help appreciated...
TIA
Tom-
That's an excellent (and obvious - don't know why I didn't think of it)
suggestion!
TX! - Bob
Tom van Stiphout wrote: On 31 May 2006 09:52:12 -0700, "Bob" <bo*************@gmail.com>
wrote:
If DB bloat is a serious concern, you could create the "temp" table in
a separate MDB you create on the fly, use for your query, and delete.
Or upsize to SQL Server Express Edition?
-Tom.
Tom, & John;
I'm very appreciative for your replies.
John; I'll try your suggestion, and see how it goes - I REALLY don't
want to have to create a temp table everytime this needs to happen -
just becomes a compaction nightmare.
This is about as brain-dead, as anything microsoft has ever done; and
they've sure done plenty. Can't tell you how many times their idiosy
has ticked me off.
When the ---- are people going to STOP paying microsoft for BS
marketing improvements, and force them to write good code? (that is
retorical, of course, because I know this will never happen - other
companies follow microsoft's example because people are stupid, or
ignorant enough to let it happen).
ok, ok - close the vents, I'm done blowing steam into the empty vacuum
of microsoft customer awareness...... :-)
T & J - again, I'm very appreciative for your suggestions; clearly, if
John's suggestion fails, I have no choice but to create a temporary
table - again & again & again & again, and compact again & again &
again .....;-O
TX - Bob
John Spencer wrote: By design you can't use a crosstab or any aggregate query in an update query
(except as a subquery in the where clause).
You may have to resort to using the cross-tab to create (or populate) a
temporary work table. Then you can use the work table in a query to update
your fields in T_Final_Answers.
You might be able to use the VBA function - DLookup - to find the value in
the crosstab and use that in your update query. Something like the
following UNTESTED query.
UPDATE T_Final_Answers
SET q = DLookup("q","q_answers_xt","id=" & T_final_answers.Id)
WHERE ID in (SELECT ID FROM q_answers_Xt)
"Bob" <bo*************@gmail.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...
> running access 2k; mdb w/ linked tables to another mdb
> (front/back-end);
>
> trying to run a query that updates a table FROM information from a
> crosstab query.
> I AM NOTT trying to update the crosstab query itself - only use
> information FROM it to update a perfectly updatable table!
>
> Why the ---- would I get this error, when there are clearly NO
> permission issues, or issues in updating the table I'm trying to
> update!?!?!?!? (I can clearly update it without the XT)
>
> What the ---- good is a crosstab query, if I can't USE the information
> within it for something other than reporting?!?!?
>
> Here's my query (the "XT" table is my crosstab):
> I'm guessing that maybe I need to use a sub-query to work around this,
> but am not sure how, nor why I should have to go this route....
>
> UPDATE Q_answers_XT LEFT JOIN T_final_answers ON
> Q_answers_XT.id = T_final_answers.id
> SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a =
> [Q_answers_XT]![a];
>
>
> any help appreciated...
> TIA
>
Rick;
TX also, for your reply -
when you talk about a pair of recordsets, you don't mean a sub-query -
right?
Could you ellaborate / illustrate please?
TIA! - Bob
Rick Wannall wrote: Don't forget that you can also use a pair of recordsets and use one for the
crosstab and one for the update.
Sure. This example assumes that you want to open the xtab and then go find
rows in the other recordset (query) to update. Reversing this is no real
challenge.
dim strWhere as string
dim rXT as ado.recordset
dim rUpdate as ado.recordset
.... (get connection(s) ...
set rXT = new ado.recordset
set rUpdate = new ado.recordset
rXT.open "Select xt.* From MyCrosstabQuery As xt" , myconnection1...
if rXT .bof=true and rXT.eof = true endif
'put cleanup code here and exit. There are no records in the xtab query.
endif
rXT.MoveFirst
do until rxt.eof = true
strWhere = "criterionfield1 = " & rXT.Fields("xtabcriterionfield1")
rUpdate.Open "Select updatefield1, updatefield2,... from MyOtherQuery
Where " & strWhere, myconnection2
if rUPdate.bof = true and rupdate.eof = true then
'close rupdate and continue to the next row of xtab
rupdate.close
else
rupdate.movefirst
do until rupdate.eof = true
rupdate.fields("updatefield1") = rxt.fields("xtabvaluefield1")
rupdate.fields("updatefield2") = rxt.fields("xtabvaluefield2")
rupdate.update
rupdate.movenext
loop
endif
rxt.MoveNext
loop
rxt.close
set rxt = nothing
set rupdate = nothing
set myconnection1 = nothing
set myconnection2=nothing
This is of course pseudocode, not based on an actual table, but it should
give you the structure you need for the task.
criterionfield1 is the name of a field in your table to update, a field you
would use to locate rows to update.
xtabcriterionfield1 would be the field in your crosstab query that you would
use to locate a row in the table to update.
updatefield and xtabvalue field are respectively the destination and source
fields for the udpate.
Rick-
I understand now, and am ever-appreciative for your time!!
TX again - Bob
Rick Wannall wrote: Sure. This example assumes that you want to open the xtab and then go find
rows in the other recordset (query) to update. Reversing this is no real
challenge.
dim strWhere as string
dim rXT as ado.recordset
dim rUpdate as ado.recordset
... (get connection(s) ...
set rXT = new ado.recordset
set rUpdate = new ado.recordset
rXT.open "Select xt.* From MyCrosstabQuery As xt" , myconnection1...
if rXT .bof=true and rXT.eof = true endif
'put cleanup code here and exit. There are no records in the xtab query.
endif
rXT.MoveFirst
do until rxt.eof = true
strWhere = "criterionfield1 = " & rXT.Fields("xtabcriterionfield1")
rUpdate.Open "Select updatefield1, updatefield2,... from MyOtherQuery
Where " & strWhere, myconnection2
if rUPdate.bof = true and rupdate.eof = true then
'close rupdate and continue to the next row of xtab
rupdate.close
else
rupdate.movefirst
do until rupdate.eof = true
rupdate.fields("updatefield1") = rxt.fields("xtabvaluefield1")
rupdate.fields("updatefield2") = rxt.fields("xtabvaluefield2")
rupdate.update
rupdate.movenext
loop
endif
rxt.MoveNext
loop
rxt.close
set rxt = nothing
set rupdate = nothing
set myconnection1 = nothing
set myconnection2=nothing
This is of course pseudocode, not based on an actual table, but it should
give you the structure you need for the task.
criterionfield1 is the name of a field in your table to update, a field you
would use to locate rows to update.
xtabcriterionfield1 would be the field in your crosstab query that you would
use to locate a row in the table to update.
updatefield and xtabvalue field are respectively the destination and source
fields for the udpate.
"Bob" <bo*************@gmail.com> wrote in
news:11**********************@f6g2000cwb.googlegro ups.com: This is about as brain-dead, as anything microsoft has ever done;
and they've sure done plenty. Can't tell you how many times their
idiosy has ticked me off.
Well, pre-Access 97, a lot of these things worked, but were
completely inconsistent with every other SQL dialect. With A97/Jet
3.5, Microsoft tightened up the join and updatability rules to be
more consistent with other flavors of SQL, which is probably
something that was required in the push to make Access interoperable
with SQL Server.
Then there's SQL 92 support in A2K and later. I doubt it would solve
the problem easily, but it might provide other solutions to the
problem of unupdatability.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"Bob" <bo*************@gmail.com> wrote in
news:11**********************@j55g2000cwa.googlegr oups.com: Although dlookup is about as cycle intensive as it gets, I think
it's still better than constantly re-creating, and compacting huge
tables...
It's not.
Temp tables are going to be *much* faster. If it's a regular
operation, just keep a copy of the empty temp database and copy over
it when your app exits. That way you have a clean copy of the temp
database every time you start your app, and never have to compact
it.
Others say it's quick to recreate the temp table in code, once the
code is written. Me, I've never taken the time to write such code,
so find it easier to run a MakeTable query, then edit the resulting
table to have appropriate indexing, then empty it out, copy it into
the temp database and compact it.
I was surprised to discover that the time it takes to write to the
temp table is not greater than the alternatives that use all memory.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
don't use MDB for anything.
use SQL Server.
you can display data in whatever format you want; and you don't have to
worry about random problems like 'operation must use an updatable
query'
MDB is just too flaky for anything; and anyone that uses it anywhere
for anything should be shot.
-Aaron
Bob wrote: running access 2k; mdb w/ linked tables to another mdb
(front/back-end);
trying to run a query that updates a table FROM information from a
crosstab query.
I AM NOTT trying to update the crosstab query itself - only use
information FROM it to update a perfectly updatable table!
Why the ---- would I get this error, when there are clearly NO
permission issues, or issues in updating the table I'm trying to
update!?!?!?!? (I can clearly update it without the XT)
What the ---- good is a crosstab query, if I can't USE the information
within it for something other than reporting?!?!?
Here's my query (the "XT" table is my crosstab):
I'm guessing that maybe I need to use a sub-query to work around this,
but am not sure how, nor why I should have to go this route....
UPDATE Q_answers_XT LEFT JOIN T_final_answers ON
Q_answers_XT.id = T_final_answers.id
SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a =
[Q_answers_XT]![a];
any help appreciated...
TIA
David;
many TX for your reply - I was unaware of an SQL support updates for
access...
I'll have to check that out - even though I doubt it'll fix this issue,
as you say, it may help elseware....
Bob
David W. Fenton wrote: "Bob" <bo*************@gmail.com> wrote in
news:11**********************@f6g2000cwb.googlegro ups.com:
This is about as brain-dead, as anything microsoft has ever done;
and they've sure done plenty. Can't tell you how many times their
idiosy has ticked me off.
Well, pre-Access 97, a lot of these things worked, but were
completely inconsistent with every other SQL dialect. With A97/Jet
3.5, Microsoft tightened up the join and updatability rules to be
more consistent with other flavors of SQL, which is probably
something that was required in the push to make Access interoperable
with SQL Server.
Then there's SQL 92 support in A2K and later. I doubt it would solve
the problem easily, but it might provide other solutions to the
problem of unupdatability.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
TX again, David;
I agree with your suggestion; and taken with someone else's idea to
have the temp table in a totally separate db, resolves all my
compacting concerns....
Bob
David W. Fenton wrote: "Bob" <bo*************@gmail.com> wrote in
news:11**********************@j55g2000cwa.googlegr oups.com:
Although dlookup is about as cycle intensive as it gets, I think
it's still better than constantly re-creating, and compacting huge
tables...
It's not.
Temp tables are going to be *much* faster. If it's a regular
operation, just keep a copy of the empty temp database and copy over
it when your app exits. That way you have a clean copy of the temp
database every time you start your app, and never have to compact
it.
Others say it's quick to recreate the temp table in code, once the
code is written. Me, I've never taken the time to write such code,
so find it easier to run a MakeTable query, then edit the resulting
table to have appropriate indexing, then empty it out, copy it into
the temp database and compact it.
I was surprised to discover that the time it takes to write to the
temp table is not greater than the alternatives that use all memory.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aaron-
tx for your input; I happen to agree with you, to a great degree. I do
believe that mdb's have their place; but in my situation, I'm dealing
with legacy software, and upgrading to SQL just hasn't been a priority.
Although, if I took the time I've wasted trying to make my
crosstab-update work, and used it to migrate..... :o)
MDB's are the preferred solution when dealing with end user's that want
to get their fingers a little dirty; also easy to support. I have alot
of users that do many varying ad-hoc queries, and it's much better to
let them do it all within an mdb.
Bob db*******@hotmail.com wrote: don't use MDB for anything.
use SQL Server.
you can display data in whatever format you want; and you don't have to
worry about random problems like 'operation must use an updatable
query'
MDB is just too flaky for anything; and anyone that uses it anywhere
for anything should be shot.
-Aaron
Bob wrote: running access 2k; mdb w/ linked tables to another mdb
(front/back-end);
trying to run a query that updates a table FROM information from a
crosstab query.
I AM NOTT trying to update the crosstab query itself - only use
information FROM it to update a perfectly updatable table!
Why the ---- would I get this error, when there are clearly NO
permission issues, or issues in updating the table I'm trying to
update!?!?!?!? (I can clearly update it without the XT)
What the ---- good is a crosstab query, if I can't USE the information
within it for something other than reporting?!?!?
Here's my query (the "XT" table is my crosstab):
I'm guessing that maybe I need to use a sub-query to work around this,
but am not sure how, nor why I should have to go this route....
UPDATE Q_answers_XT LEFT JOIN T_final_answers ON
Q_answers_XT.id = T_final_answers.id
SET T_final_answers.q = [Q_answers_XT]![q], T_final_answers.a =
[Q_answers_XT]![a];
any help appreciated...
TIA
Users who want to get their fingers a little dirty can do so in an MDE, if
that means creating queries, which is about all users should be turned loose
to do. Even then, you have the issue of preserving queries created by users
if you replace the MDE with a new release.
Do your users want to create forms and reports?
db*******@hotmail.com wrote in
news:11**********************@c74g2000cwc.googlegr oups.com: don't use MDB for anything.
use SQL Server.
you can display data in whatever format you want; and you don't
have to worry about random problems like 'operation must use an
updatable query'
MDB is just too flaky for anything; and anyone that uses it
anywhere for anything should be shot.
This is a completely idiotic post, which reflects no actual
knowledge or understanding of how to use and maintain Jet databases.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Rick-
they do create reports, and rarely forms; also this isn't mission
critical data, so I don't need the protection / restrictions of the mde
- although I didn't know that you could create/save queries in an mde -
I thought it didn't let you do anything [of a development nature]....
Bob
Rick Wannall wrote: Users who want to get their fingers a little dirty can do so in an MDE, if
that means creating queries, which is about all users should be turned loose
to do. Even then, you have the issue of preserving queries created by users
if you replace the MDE with a new release.
Do your users want to create forms and reports?
(LOL)
David - I'll grant you that - I was trying to be nice :)
although, I will partially defend dbahooker (certainly not the way he
communicates it), in that on SEVERAL occasions, I have had an mdb
database go corrupt on me in some very strange ways - beyond the point
of repair (in fact, I couldn't even import my work into a clean
database), and I lost DAYS of work in 1 such case. Whenever I work with
mdb's I ALWAYS make a new copy of the database before I open it because
of this experience.
I have NEVER had such an experience with SQL. Granted, that access can
both be the front end AND back end; and SQL only serves as a back-end;
so my comparison isn't 100% pure..... but you get the idea....
Bob
David W. Fenton wrote: db*******@hotmail.com wrote in
news:11**********************@c74g2000cwc.googlegr oups.com:
don't use MDB for anything.
use SQL Server.
you can display data in whatever format you want; and you don't
have to worry about random problems like 'operation must use an
updatable query'
MDB is just too flaky for anything; and anyone that uses it
anywhere for anything should be shot.
This is a completely idiotic post, which reflects no actual
knowledge or understanding of how to use and maintain Jet databases.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
"Bob" <bo*************@gmail.com> wrote in
news:11*********************@h76g2000cwa.googlegro ups.com: although, I will partially defend dbahooker (certainly not the
way he communicates it), in that on SEVERAL occasions, I have had
an mdb database go corrupt on me in some very strange ways -
beyond the point of repair (in fact, I couldn't even import my
work into a clean database), and I lost DAYS of work in 1 such
case. Whenever I work with mdb's I ALWAYS make a new copy of the
database before I open it because of this experience.
I have never seen an MDB corrupted beyond the ability of Peter
Miller to retrieve it. Of course, I've only once had an MDB that *I*
couldn't recover, so that's not many times.
If you encounter corruption on a regular basis, then I'd say you're
doing something wrong.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
On 1 Jun 2006 15:35:24 -0700, "Bob" <bo*************@gmail.com> wrote:
If I had adventurous users like that, I would tell them: create your
own MDB, and attach to the backend on the server, and create all you
want. I would not encourage them to use the production frontend for
it, especially because I want to reserve the right to replace it with
a new version any time I see fit.
-Tom. Rick-
they do create reports, and rarely forms; also this isn't mission
critical data, so I don't need the protection / restrictions of the mde
- although I didn't know that you could create/save queries in an mde -
I thought it didn't let you do anything [of a development nature]....
Bob
Rick Wannall wrote: Users who want to get their fingers a little dirty can do so in an MDE, if
that means creating queries, which is about all users should be turned loose
to do. Even then, you have the issue of preserving queries created by users
if you replace the MDE with a new release.
Do your users want to create forms and reports?
I wouldn't say it happens on a regular basis, considering how often I
work in the environment, it's very rare. But it only takes 1 bad
experience to sour / caution you forever-more .... :)
Only that 1 time was I unable to repair/recover it; and it that 1 case,
PM wouldn't have been able to either.... it was VERY strange. Not even
sure if I didn't have a momentary [hardware] issue with memory....
David W. Fenton wrote: "Bob" <bo*************@gmail.com> wrote in
news:11*********************@h76g2000cwa.googlegro ups.com:
although, I will partially defend dbahooker (certainly not the
way he communicates it), in that on SEVERAL occasions, I have had
an mdb database go corrupt on me in some very strange ways -
beyond the point of repair (in fact, I couldn't even import my
work into a clean database), and I lost DAYS of work in 1 such
case. Whenever I work with mdb's I ALWAYS make a new copy of the
database before I open it because of this experience.
I have never seen an MDB corrupted beyond the ability of Peter
Miller to retrieve it. Of course, I've only once had an MDB that *I*
couldn't recover, so that's not many times.
If you encounter corruption on a regular basis, then I'd say you're
doing something wrong.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
I'm right there with you, Tom;
These people only get to play with an mdb COPY of data exported from
the REAL db.
I'M not that adventurous (or trusting) .... even if the data isn't
mission crit... ;-O
Bob
Tom van Stiphout wrote: On 1 Jun 2006 15:35:24 -0700, "Bob" <bo*************@gmail.com> wrote:
If I had adventurous users like that, I would tell them: create your
own MDB, and attach to the backend on the server, and create all you
want. I would not encourage them to use the production frontend for
it, especially because I want to reserve the right to replace it with
a new version any time I see fit.
-Tom.
Rick-
they do create reports, and rarely forms; also this isn't mission
critical data, so I don't need the protection / restrictions of the mde
- although I didn't know that you could create/save queries in an mde -
I thought it didn't let you do anything [of a development nature]....
Bob
Rick Wannall wrote: Users who want to get their fingers a little dirty can do so in an MDE, if
that means creating queries, which is about all users should be turned loose
to do. Even then, you have the issue of preserving queries created by users
if you replace the MDE with a new release.
Do your users want to create forms and reports?
"Bob" <bo*************@gmail.com> wrote in
news:11**********************@y43g2000cwc.googlegr oups.com: Only that 1 time was I unable to repair/recover it; and it that 1
case, PM wouldn't have been able to either.... it was VERY
strange. Not even sure if I didn't have a momentary [hardware]
issue with memory....
How do you know he couldn't? Do you have the tools to explore the
internal Jet file structure and the knowledge of it to be able to
say that data was not recoverable? Peter does have those tools and
that knowledge, having developed them himself over the years. I
would never presume to guess whether or not a corrupted MDB is
recoverable or not, given that I have no evidence or knowledge that
gives me a chance of making any kind of judgment of just how corrupt
it is internally.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
David;
Of course, you are correct; I cannot say that definitively. Though I
can say it with a great deal of confidence. I examinded it with a hex
editor, and found huge area's of severe corruption. It was as though
the "framework" was still in place, but the innerd's with all the
important stuff was gone. I would analogize it with having a hard disk
with partition table, and directory structure all intact, and you could
view the directory; but None of the files had any data what-so-ever (if
you've ever done intimate work with fat32, you know what I mean).
In any case, my comment wasn't meant as a criticism of Peter in any
way, just a relative gauge of the state of the mdb.
Bob
David W. Fenton wrote: "Bob" <bo*************@gmail.com> wrote in
news:11**********************@y43g2000cwc.googlegr oups.com:
Only that 1 time was I unable to repair/recover it; and it that 1
case, PM wouldn't have been able to either.... it was VERY
strange. Not even sure if I didn't have a momentary [hardware]
issue with memory....
How do you know he couldn't? Do you have the tools to explore the
internal Jet file structure and the knowledge of it to be able to
say that data was not recoverable? Peter does have those tools and
that knowledge, having developed them himself over the years. I
would never presume to guess whether or not a corrupted MDB is
recoverable or not, given that I have no evidence or knowledge that
gives me a chance of making any kind of judgment of just how corrupt
it is internally.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: jason |
last post by:
I am picking up an error message on a straightforward INSERT - do I need an
optimistic-type to get this working....here is is the error:
Microsoft JET Database Engine error '80004005' Operation...
|
by: Tom wilson |
last post by:
This is driving me nuts. I'm trying to update an Excel spreadsheet
using ADO.Net and Oledb in VB.Net. The connection is open, the
adapter is connected and the dataset is loaded. Here's the code...
|
by: Neil Zanella |
last post by:
Hello,
I am trying to update an MS access database from ASP.NET. I am using IIS on
Windows XP Pro. I can issue SELECT statements from ASP.NET using ADO.NET but
I cannot seem to be able to carry...
|
by: Derek Van Cuyk |
last post by:
Hi everyone!
I'm trying to write a web application in school that utilizes a MS
Access database. I can read and everything fine from it but when I try
to add a new record I get an exception that...
|
by: Jim in Arizona |
last post by:
Continuing my lessons out of a book, I ran into a problem when trying for
the first time to update a datastore (access database in this case).
My Code:
Private Sub Page_Load(ByVal sender As...
|
by: SheryMich |
last post by:
Hi -
I am having a bit of a problem with the insert into a database. When I go
to insert a record into an un-keyed, single table Access database, I get the
aforementioned ''Operation Must Use an...
|
by: Web Search Store |
last post by:
I'm getting this error on my web page:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
Operation must use an updateable
query.
/searchweb33.asp, line 5014
Here is the...
|
by: Jim in Arizona |
last post by:
I've been using an example out of a book to be able to edit the rows in a
database. I am getting the following error:
========================================================...
|
by: Arpan |
last post by:
I have always been working with SQL Server 2005 for ASP.NET apps but
due to some reasons, had to revert back to MS-Access 2000.
When I try to insert/update a MS-Access DB table (MDB), ASP.NET...
|
by: Mike D |
last post by:
OS: Windows XP Professional
Microsoft Access 2003
I am trying to update a table in my DB from a tempory table. I need to ensure that if records in the main table match records in the temp...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |
