OPERATION MUST USE AN UPDATEABLE QUERY

[neb]

INSERT INTO [Excel 8.0;].[Report$] ( Fixed, Unfixed, Total ) IN 'C:\Documents
and Settings\Administrator\Desktop\test.xls'[Excel 8.0;]
SELECT preb_count1.Fixed, preb_count1.Fixed, preb_count1.Fixed
FROM preb_count1;


Can you pls help me what's wrong with this code?
Any help will be much appreciated..

//neb

 

[neb]

SORRY the code is:

INSERT INTO [Excel 8.0;].[Report$] ( Fixed, Unfixed, Total ) IN 'C:\Documents
and Settings\Administrator\Desktop\test.xls'[Excel 8.0;]
SELECT preb_count1.Fixed, preb_count1.Unfixed, preb_count1.Total
FROM preb_count1;

Can you pls help me what's wrong with this code?
Any help will be much appreciated..

//neb

 

[Douglas J. Steele]

The ability to update linked Excel spreadsheets has been removed from
Access, due to a lawsuit they lost earlier this year.

 

[neb]

Is there no more solution?

 

[aaron.kempf]

yeah Access MDB is for lepers and faggots and anyone stupid enough to
use it for anything shoudl be fired on the spot.

Access MDB is flaky; unstable; unpredictable.

you can't STACK queries on top of queries.

Anyone that uses MDB should be ASS-RAPED BY A PINK SLIP.

With an Access Data Project I could do this blindfolded.. scheduled
move / copy of data from A to Z without writing a single line of code.

Try _THAT_ in an MDB.

-Aaron
Realist

INSERT INTO [Excel 8.0;].[Report$] ( Fixed, Unfixed, Total ) IN
'C:\Documents
and Settings\Administrator\Desktop\test.xls'[Excel 8.0;]
SELECT preb_count1.Fixed, preb_count1.Unfixed, preb_count1.Total
FROM preb_count1;

Try

INSERT INTO [Excel 8.0;Database=C:\Documents
and Settings\Administrator\Desktop\test.xls;].[Report$] (Fixed,
Unfixed, Total)
SELECT preb_count1.Fixed, preb_count1.Unfixed, preb_count1.Total
FROM preb_count1;

The ability to update linked Excel spreadsheets has been removed from
Access, due to a lawsuit they lost earlier this year.

The functionality hasn't been removed from Jet, which is what the OP
appears to be using.

Anyhow, from the article I read I was left with the impression MSFT
replaced the code rather than removed the functionality from Access. Do
you have any further details, please?

TIA,
Jamie.

--

 

[aaron.kempf]

you should be living under a german boot you fucking pussy

eat shit and learn how to evaluate technology and make a decision based
on LOGIC.

MDB isn't reliable; scalable; it's not free; it's not easier to develop
in.

Anyone that uses MDB for anything should be fired on the spot.

-Aaron

 

[Douglas J. Steele]

Sorry, Jamie, I haven't seen any details (and Microsoft was very tight
lipped about it when we asked)

 

[aaron.kempf]

it's a fucking bug

microsoft always treats bugs like the plague
meanwhile we get stuck reacting to random bugs in the MDB engine.

I urge all of you to give up on MDB and give up on ACCDB and carry on
with Access Data Projects.

-Aaron

 

[neb]

THANKS SO MUCH JAMIE(MVP)

INSERT INTO [Excel 8.0;].[Report$] ( Fixed, Unfixed, Total ) IN
'C:\Documents
and Settings\Administrator\Desktop\test.xls'[Excel 8.0;]
SELECT preb_count1.Fixed, preb_count1.Unfixed, preb_count1.Total
FROM preb_count1;

Try

INSERT INTO [Excel 8.0;Database=C:\Documents
and Settings\Administrator\Desktop\test.xls;].[Report$] (Fixed,
Unfixed, Total)
SELECT preb_count1.Fixed, preb_count1.Unfixed, preb_count1.Total
FROM preb_count1;

The ability to update linked Excel spreadsheets has been removed from
Access, due to a lawsuit they lost earlier this year.

The functionality hasn't been removed from Jet, which is what the OP
appears to be using.

Anyhow, from the article I read I was left with the impression MSFT
replaced the code rather than removed the functionality from Access. Do
you have any further details, please?

TIA,
Jamie.

 

[neb]

el clima aqui es muy calor. Pilipinas tiene un clima tropical.

 

[neb]

Jamie I have a problem. It works with Excel 2000 but with Excel 2003 it
doesnt.
Any comment? Or do we need to change the code?
thanks

 

[neb]

I still have a problem Jamie. It worked with Excel 2000 but with Excel 2003
it doesnt. Do we need to change the code or this is what Mr. Douglas said
regarding the lawsuit that MSFT lost. Any comment...Thanks

 

[Carlos Educardo Bordon G.]

Hola

Quería saber si como esta el tiempo por alla.

Carlos.

 

[Douglas J. Steele]

I still have a problem

It worked with Excel 2000 but with Excel 2003
it doesnt. Do we need to change the code or this is what Mr. Douglas said
regarding the lawsuit that MSFT lost. Any comment...Thanks

Are you executing the SQL from a Access Query object? MSFT may have
crippled that I suppose, I really don't know.

The syntax works for me using an ADO connection in VBA code. At first I
*did* get the error but realized I'd used IMEX=1 (import/export mode)
in the connection by mistake i.e.

the following works fine:

INSERT INTO
[Excel 8.0;HDR=YES;DATABASE=C:\Tempo\db.xls;].[Blah$]
(MyKeyColumn, MyDataColumn) VALUES (1, 2);

but the following generates the 'updatable query' error:

INSERT INTO
[Excel 8.0;HDR=YES;IMEX=1;DATABASE=C:\Tempo\db.xls;].[Blah$]
(MyKeyColumn, MyDataColumn) VALUES (1, 2);

So check you haven't got IMEX=1 in your connection string!

Jamie: Some of the MVPs have been trying to follow up on this. From where
are you running the SQL? The experience we've seen is that if you run it
from Access 2003, SP2, it still fails, but it works fine from VBS or from
other Office products (with the appropriate reference set)

I wasn't the one doing the testing, though: if I recall correctly, John was
trying to run it using the Execute method of the Database object, not with
ADO.

 

[neb]

Jamie,

Im using Access 2003 and now it works when i add IMEX=0. pls try it out.

Now I have a new problem: "Cannot expand named range. (Error 3434)" when I
try to execute the command the second time.

//// neb

 

[neb]

the code:

INSERT INTO [Excel 8.0; IMEX=0;Database=C:\Documents and
Settings\Administrator\Desktop\test.xls;HDR=Yes].[Report$b2:d2] (Fixed,
Unfixed, Total)
SELECT preb_count1.Fixed, preb_count1.Unfixed, preb_count1.Total
FROM preb_count1;

////