Can this be doen. I get an ISAM Error.

Any ideas?

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.

"Rocky" wrote:

> Can this be doen. I get an ISAM Error.
>
> Any ideas?
>
>
>

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.

"Klatuu" <(E-Mail Removed)> wrote in message
news:50195FE6-FCBB-4F52-96B4-(E-Mail Removed)...
> 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.
>
> "Rocky" wrote:
>
>> Can this be doen. I get an ISAM Error.
>>
>> Any ideas?
>>
>>
>>

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.

"John Nurick" wrote:

> 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.
>
> "Klatuu" <(E-Mail Removed)> wrote in message
> news:50195FE6-FCBB-4F52-96B4-(E-Mail Removed)...
> > 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.
> >
> > "Rocky" wrote:
> >
> >> Can this be doen. I get an ISAM Error.
> >>
> >> Any ideas?
> >>
> >>
> >>
>
>
>

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?



"Klatuu" <(E-Mail Removed)> wrote in message
news:CAA92B12-01EF-4A59-B250-(E-Mail Removed)...
> 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.
>
> "John Nurick" wrote:
>
> > 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.
> >
> > "Klatuu" <(E-Mail Removed)> wrote in message
> > news:50195FE6-FCBB-4F52-96B4-(E-Mail Removed)...
> > > 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.
> > >
> > > "Rocky" wrote:
> > >
> > >> Can this be doen. I get an ISAM Error.
> > >>
> > >> Any ideas?
> > >>
> > >>
> > >>
> >
> >
> >

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" wrote:

> 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?
>
>
>
> "Klatuu" <(E-Mail Removed)> wrote in message
> news:CAA92B12-01EF-4A59-B250-(E-Mail Removed)...
> > 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.
> >
> > "John Nurick" wrote:
> >
> > > 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.
> > >
> > > "Klatuu" <(E-Mail Removed)> wrote in message
> > > news:50195FE6-FCBB-4F52-96B4-(E-Mail Removed)...
> > > > 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.
> > > >
> > > > "Rocky" wrote:
> > > >
> > > >> Can this be doen. I get an ISAM Error.
> > > >>
> > > >> Any ideas?
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
>
>

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" <(E-Mail Removed)> wrote in message
news:A9F071F6-D0E1-4848-9A0B-(E-Mail Removed)...
> 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" wrote:
>
> > 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?
> >
> >
> >
> > "Klatuu" <(E-Mail Removed)> wrote in message
> > news:CAA92B12-01EF-4A59-B250-(E-Mail Removed)...
> > > 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.
> > >
> > > "John Nurick" wrote:
> > >
> > > > 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.
> > > >
> > > > "Klatuu" <(E-Mail Removed)> wrote in message
> > > > news:50195FE6-FCBB-4F52-96B4-(E-Mail Removed)...
> > > > > 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.
> > > > >
> > > > > "Rocky" wrote:
> > > > >
> > > > >> Can this be doen. I get an ISAM Error.
> > > > >>
> > > > >> Any ideas?
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > >
> > > >
> >
> >
> >

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:A9F071F6-D0E1-4848-9A0B-(E-Mail Removed)...
> > 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" wrote:
> >
> > > 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?
> > >
> > >
> > >
> > > "Klatuu" <(E-Mail Removed)> wrote in message
> > > news:CAA92B12-01EF-4A59-B250-(E-Mail Removed)...
> > > > 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.
> > > >
> > > > "John Nurick" wrote:
> > > >
> > > > > 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.
> > > > >
> > > > > "Klatuu" <(E-Mail Removed)> wrote in message
> > > > > news:50195FE6-FCBB-4F52-96B4-(E-Mail Removed)...
> > > > > > 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.
> > > > > >
> > > > > > "Rocky" wrote:
> > > > > >
> > > > > >> Can this be doen. I get an ISAM Error.
> > > > > >>
> > > > > >> Any ideas?
> > > > > >>
> > > > > >>
> > > > > >>
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

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)


"Klatuu" <(E-Mail Removed)> wrote in message
news:4F9B80F0-9A76-4DCE-B5AB-(E-Mail Removed)...
> 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" wrote:
>
> > 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" <(E-Mail Removed)> wrote in message
> > news:A9F071F6-D0E1-4848-9A0B-(E-Mail Removed)...
> > > 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" wrote:
> > >
> > > > 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?
> > > >
> > > >
> > > >
> > > > "Klatuu" <(E-Mail Removed)> wrote in message
> > > > news:CAA92B12-01EF-4A59-B250-(E-Mail Removed)...
> > > > > 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.
> > > > >
> > > > > "John Nurick" wrote:
> > > > >
> > > > > > 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.
> > > > > >
> > > > > > "Klatuu" <(E-Mail Removed)> wrote in message
> > > > > > news:50195FE6-FCBB-4F52-96B4-(E-Mail Removed)...
> > > > > > > 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.
> > > > > > >
> > > > > > > "Rocky" wrote:
> > > > > > >
> > > > > > >> Can this be doen. I get an ISAM Error.
> > > > > > >>
> > > > > > >> Any ideas?
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

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