Delete data in a linked Excel sheet using Access code or seql delete

[Rocky]

Can this be doen. I get an ISAM Error.

Any ideas?

 

[Guest]

Rocky,
Please post the code you are trying to do this with. This can be done, but
lets see what you are trying, then we can probably fix it.

 

[John Nurick]

AFAIK this has to be done by automating Excel to delete the relevant rows.
Any time I try to do it with a query I get the message about this ISAM not
supporting deletes.

 

[Guest]

I am thinking that when he defines that database, he is trying to use
something that is not available. As I recall, there is a designator for the
Excel version and trying anthing more than 8.0 creates that error. For
example,

SELECT * FROM Foo IN "F:\GoofyData.xls" "Excel 8.0;"

Although newer version of Excel are available, the ISAM connection still
only recognizes up to 8.0

I don't know if this is the problem, but that is why I wanted to see the code.

 

[Rocky]

DoCmd.RunSQL ("delete * From SpreadSheet")

DoCmd.OutputTo acOutputTable, "Select Combo Field", acFormatXLS,
"C:\Excel\Test.xls", False


The first complains about ISAM not allowing delete, and the second line stil
puts a header in the excel spread sheet. regardless whether I select , True
, or False.

Any Ideas?

Thanks

BTW is AFAIK what I think it is?

 

[Guest]

The true or false has nothing to do with whether or not to include headers.
It is the autostart option:
(From Access Help)
AutoStart Optional Variant. Use True (–1) to start the appropriate
Microsoft Windows–based application immediately, with the file specified by
the OutputFile argument loaded. Use False (0) if you don't want to start
the application. This argument is ignored for Microsoft Internet Information
Server (.htx, .idc) files and Microsoft ActiveX Server (*.asp) files. If you
leave this argument blank, the default (False) is assumed.

What is Spreadsheet? Are you linked to the spreadsheet as a table and
calling it spreadsheet? I think that is what you are doing and it is not
possible.
(Also from Access Help)
Deleting data in a linked table is not supported by this ISAM. (Error 3617)
The installable ISAM you are using does not allow you to delete records in
external tables. You can only add new records.

Your only recourse here would be to import rather than link.

 

[Rocky]

I can call it spreadsheet1 or what ever it is a linked excel spread sheet
that I was trying to see about using an update query on and treat it as a
table to do what iam trying to do in code.

My code works fine,

I would like to export data or append it to the excel spread sheet. I either
want to clear the old data first or delete the whole excel spread sheet and
creat a new one on Click event or so.


INSERT INTO Spreadsheet1
"[Excel 8.0;HDR=No;Database=C:\Excel\Workbook.xls;].[Sheet1$A1:A1]"
SELECT [PActive Jobs].[Office_Project] AS Office_Project
FROM [PActive Jobs];

this code give me a syntax error.

 

[Guest]

Rocky,

You have a whole lot of problems with this. First, you can't delete from an
external database. Second, the INSERT statement has no fields identified for
the target. As you don't want to have field names, this might be a problem,
you could try:

INSERT INTO Spreadsheet1 * SELECT [PActive Jobs].[Office_Project] AS
Office_Project
FROM [PActive Jobs];

But I don't think it will work. Since the spreadsheet is linked, you don't
need to specify the database. Spreadsheet1 being linked table, the syntax is
like any other table.

But, because you can't delete the old data, you might be better off to
programmatically delete the old spreadsheet file, and just use
TransferSpreadsheet method to create a new one. It will allow you to export
the data without field names.

I can call it spreadsheet1 or what ever it is a linked excel spread sheet
that I was trying to see about using an update query on and treat it as a
table to do what iam trying to do in code.

My code works fine,

I would like to export data or append it to the excel spread sheet. I either
want to clear the old data first or delete the whole excel spread sheet and
creat a new one on Click event or so.


INSERT INTO Spreadsheet1
"[Excel 8.0;HDR=No;Database=C:\Excel\Workbook.xls;].[Sheet1$A1:A1]"
SELECT [PActive Jobs].[Office_Project] AS Office_Project
FROM [PActive Jobs];

this code give me a syntax error.

The true or false has nothing to do with whether or not to include headers.
It is the autostart option:
(From Access Help)
AutoStart Optional Variant. Use True (-1) to start the appropriate
Microsoft Windows-based application immediately, with the file specified by
the OutputFile argument loaded. Use False (0) if you don't want to start
the application. This argument is ignored for Microsoft Internet Information
Server (.htx, .idc) files and Microsoft ActiveX Server (*.asp) files. If you
leave this argument blank, the default (False) is assumed.

What is Spreadsheet? Are you linked to the spreadsheet as a table and
calling it spreadsheet? I think that is what you are doing and it is not
possible.
(Also from Access Help)
Deleting data in a linked table is not supported by this ISAM. (Error 3617)
The installable ISAM you are using does not allow you to delete records in
external tables. You can only add new records.

Your only recourse here would be to import rather than link.

 

[Mario Madani]

Okay..


What is the command to delete a spread sheet?

Kill C:\excel\spreadsheet1.xls ???

Could you give me a syntax for the transfer (TransferSpreadsheet)

Rocky,

You have a whole lot of problems with this. First, you can't delete from an
external database. Second, the INSERT statement has no fields identified for
the target. As you don't want to have field names, this might be a problem,
you could try:

INSERT INTO Spreadsheet1 * SELECT [PActive Jobs].[Office_Project] AS
Office_Project
FROM [PActive Jobs];

But I don't think it will work. Since the spreadsheet is linked, you don't
need to specify the database. Spreadsheet1 being linked table, the syntax is
like any other table.

But, because you can't delete the old data, you might be better off to
programmatically delete the old spreadsheet file, and just use
TransferSpreadsheet method to create a new one. It will allow you to export
the data without field names.

I can call it spreadsheet1 or what ever it is a linked excel spread sheet
that I was trying to see about using an update query on and treat it as a
table to do what iam trying to do in code.

My code works fine,

I would like to export data or append it to the excel spread sheet. I either
want to clear the old data first or delete the whole excel spread sheet and
creat a new one on Click event or so.


INSERT INTO Spreadsheet1
"[Excel 8.0;HDR=No;Database=C:\Excel\Workbook.xls;].[Sheet1$A1:A1]"
SELECT [PActive Jobs].[Office_Project] AS Office_Project
FROM [PActive Jobs];

this code give me a syntax error.

The true or false has nothing to do with whether or not to include headers.
It is the autostart option:
(From Access Help)
AutoStart Optional Variant. Use True (-1) to start the appropriate
Microsoft Windows-based application immediately, with the file

specified
by

the OutputFile argument loaded. Use False (0) if you don't want to start
the application. This argument is ignored for Microsoft Internet Information
Server (.htx, .idc) files and Microsoft ActiveX Server (*.asp) files.

If
you

leave this argument blank, the default (False) is assumed.

What is Spreadsheet? Are you linked to the spreadsheet as a table and
calling it spreadsheet? I think that is what you are doing and it is not
possible.
(Also from Access Help)
Deleting data in a linked table is not supported by this ISAM. (Error 3617)
The installable ISAM you are using does not allow you to delete records in
external tables. You can only add new records.

Your only recourse here would be to import rather than link.


:

DoCmd.RunSQL ("delete * From SpreadSheet")

DoCmd.OutputTo acOutputTable, "Select Combo Field", acFormatXLS,
"C:\Excel\Test.xls", False


The first complains about ISAM not allowing delete, and the second

line
stil

puts a header in the excel spread sheet. regardless whether I select

,
True

, or False.

Any Ideas?

Thanks

BTW is AFAIK what I think it is?



I am thinking that when he defines that database, he is trying to use
something that is not available. As I recall, there is a

designator
for

the
Excel version and trying anthing more than 8.0 creates that error. For
example,

SELECT * FROM Foo IN "F:\GoofyData.xls" "Excel 8.0;"

Although newer version of Excel are available, the ISAM connection still
only recognizes up to 8.0

I don't know if this is the problem, but that is why I wanted to

see
the

code.

:

AFAIK this has to be done by automating Excel to delete the relevant
rows.
Any time I try to do it with a query I get the message about

this
ISAM

not
supporting deletes.

Rocky,
Please post the code you are trying to do this with. This can be
done,
but
lets see what you are trying, then we can probably fix it.

:

Can this be doen. I get an ISAM Error.

Any ideas?

 

[aaron.kempf]

Shell "DEL c:\myspreadsheetpath.xls"

Docmd.TransferSpreadsheet-- gosh.. if you just start with a MACRO--
these are multiple choice ways to automate things in Access..

so you can have a macro-- if you want it to run when you open the MDB;
you can name the macro 'autoexec'

so then you have a macro choose the option named TransferSpreadsheet..
and then you can check out all the options for that command in a
multiple choice format.

it's a BRILLIANT way to automate things in a database-- because you can
always take the macro and you can save it as a module-- and then you
can run with it

it's an awesome way to learn Access-- macros are a lot of fun.
I just tink that Microsoft should RENAME macros so that macros in excel
and macros in Access are similiar things..

They should call them macros (VBA) and automators or something..

That's the root of the marketing problem facing Excel dorks that can't
get into Access.. they're too scared to code; and they just dont
understand that MACROS in Access are the most wondrous thing ever
invented-- but in Excel; it's just a complete pain in the ass.

good luck hope i helped some