Re: INSERT INTO Excel file, from an Access source
From: Programmer Dude (Chris_at_Sonnack.com)
Date: 01/15/04
- Next message: dan michaels: "Re: What IS Intelligence"
- Previous message: Alan Balmer: "Re: Flame Bait! Windows vs: The Unices II"
- In reply to: Patrick Lioi: "INSERT INTO Excel file, from an Access source"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Thu, 15 Jan 2004 15:38:19 -0600
Patrick Lioi wrote:
> An existing application, which works, and was written by someone
> else, executes this bit of SQL:
>
> strSQL = "INSERT INTO [Shee1$] IN '' [Excel 8.0;Database=" & XLS_PATH
> & "] SELECT a, b, c FROM tablename"
Don't know if this is relevant, but I tried this (in Access), and
it didn't work at all. I changed it to this, and it worked fine:
INSERT INTO [Excel 8.0;Database=C:\path\Test.xls].[Sheet1$]
(a, b, c)
SELECT a, b, c FROM tablename ORDER BY a;
SQL syntax does vary among products, so this may not apply.
> This WORKS on the production computer, but fails *quietly* on my
> development computer. The only source code change is the value of
> XLS_PATH, which now points to a file on my development computer.
>
> Rarely, the excel file actually will be updated. Most of the time,
> it avoids updating without any error messages.
>
> Any idea what could be different between the production and dev
> environments to cause this behavior?
Same versions of Excel? Same versions of the underlying DB and
OLE drivers?
I found that--in my test--"Sheet1" had to exist, and it had to
have the field names in the first row. There's probably a way
around that last, but I don't have the time to pursue it.
ONE SUGGESTION: it may be (much) easier to create a query IN
Excel that pulls the data from the database rather than trying
to extract it and stuff it into Excel from the outside.
-- |_ CJSonnack <Chris@Sonnack.com> _____________| How's my programming? | |_ http://www.Sonnack.com/ ___________________| Call: 1-800-DEV-NULL | |_____________________________________________|_______________________|
- Next message: dan michaels: "Re: What IS Intelligence"
- Previous message: Alan Balmer: "Re: Flame Bait! Windows vs: The Unices II"
- In reply to: Patrick Lioi: "INSERT INTO Excel file, from an Access source"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
