R
Rocky
Can this be doen. I get an ISAM Error.
Any ideas?
Any ideas?
Rocky said: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.
Klatuu said: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.
Klatuu said: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.
Rocky said: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.
specifiedKlatuu said: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
byIfthe 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.
youlineleave 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
stil,puts a header in the excel spread sheet. regardless whether I select
Truedesignator, 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
forseethe
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
thethiscode.
:
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
ISAMnot
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?