SQL syntax question

[deko]

I'm trying to export to Excel with this:

SELECT [somedata] INTO [Excel 8.0;Database=" + GetXlsPath +
"].WorksheetName;

The problem is with

[Excel 8.0;Database=" + GetXlsPath + "]

I've tried this:

[Excel 8.0;Database=GetXlsPath]

and this:

[Excel 8.0;Database=GetXlsPath()]

and a bunch of other iterations, but no luck.

GetXlsPath is a function that works fine, but how do I insert the return
string into a query?
(a complied query, not VBA code)

Thanks in advance.

[deko]

> Use & not +

If I were using vba code, yes. But I don't think that works in a complied
query. I tried it and the query create a file:

[Excel 8.0;Database= & GetXlsPath &]
outputs
"& GetXlsPath &.XLS"

using quotes did not help...

[Alan Webb]

Deko,
Things must have changed since Access 2002 or you are trying something I
didn't know could be done. With what I know about Access I'd do this in
VBA.

--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS
"deko" <de**@deko.com> wrote in message
news:tI****************@newssvr21.news.prodigy.com ...

I'm trying to export to Excel with this:

SELECT [somedata] INTO [Excel 8.0;Database=" + GetXlsPath +
"].WorksheetName;

The problem is with

[Excel 8.0;Database=" + GetXlsPath + "]

I've tried this:

[Excel 8.0;Database=GetXlsPath]

and this:

[Excel 8.0;Database=GetXlsPath()]

and a bunch of other iterations, but no luck.

GetXlsPath is a function that works fine, but how do I insert the return
string into a query?
(a complied query, not VBA code)

Thanks in advance.

[deko]

> Deko,

Things must have changed since Access 2002 or you are trying something I
didn't know could be done. With what I know about Access I'd do this in
VBA.

I'm using Jet (AC2000) to dump a series of tables into Excel. Because it's
a big loop I wanted to use a complied query to try and speed things up.
It's currently being done with code: 'db.Execute strSql' where strSql is
concatenated with '&'. It works, but 100+ worksheets takes a few minutes.
Perhaps if I made everything inside the brackets one string:

[ + cnxStr + ]

instead of

[Excel 8.0;Database= + GetXlsPath +]

[deko]

I tried this:

Set db = CurrentDb
Set qdfs = db.QueryDefs
Set qdf = qdfs("qryExcelData")
strSql = "SELECT * INTO [Excel 8.0;Database=" & _
strXlsPath & "].[" & strSheetName & _
"] FROM tblExcelData;"
qdf.SQL = strSql
qdf.Execute (dbFailOnError)

Processing time dropped by a whopping 3 seconds (6:25 to 6:22) when looping
through 119 worksheets.

oh well...

[Alan Webb]

Deko,
SQL is intended to work with sets of data. Why the loop? My computer is a
slow dinosaur by today's standards--PIII, 733Mhz, 256MB Ram, 9GB HD. I get
subsecond response on most if not all of my code. Even when I was
developing commerically I was able to get Access 2000 and Access 97 working
together on a million plus row table with a wireless connection where one of
the machines was a P1 166Mhz. laptop and process the whole mess in under 20
minutes using really stupid & inefficient coding techniques. Six minutes to
run something is way too long. Whatever you are doing you are going about
it the hard way.

--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"deko" <de**@deko.com> wrote in message
news:ju*****************@newssvr21.news.prodigy.co m...

I tried this:

Set db = CurrentDb
Set qdfs = db.QueryDefs
Set qdf = qdfs("qryExcelData")
strSql = "SELECT * INTO [Excel 8.0;Database=" & _
strXlsPath & "].[" & strSheetName & _
"] FROM tblExcelData;"
qdf.SQL = strSql
qdf.Execute (dbFailOnError)

Processing time dropped by a whopping 3 seconds (6:25 to 6:22) when
looping
through 119 worksheets.

oh well...

[deko]

> SQL is intended to work with sets of data. Why the loop? My computer is
a

slow dinosaur by today's standards--PIII, 733Mhz, 256MB Ram, 9GB HD. I get subsecond response on most if not all of my code. Even when I was
developing commerically I was able to get Access 2000 and Access 97 working together on a million plus row table with a wireless connection where one of the machines was a P1 166Mhz. laptop and process the whole mess in under 20 minutes using really stupid & inefficient coding techniques. Six minutes to run something is way too long. Whatever you are doing you are going about
it the hard way.

And that 6 minutes is on a P4 3.4Ghz with 1Gb RAM and a SATA drive. No
question I'm doing things the hard way - but I'm constrained by what I've
been given. I have any number of (100 or so) mdbs that contain a bunch of
very non-normalized tables containing all kinds of stuff (including big long
binary fields that need to be extracted - this takes a lot of time). Each
mdb yields from 0 to half a dozen or so worksheets. First I have to locate
each mdb (which could be anywhere beneath the directory the users browses
to), then, for each mdb, link the tables I need, suck in, massage, extract,
nerp, and format the data... then dump it out to Excel (this is where I
tried the compiled query) and create a bunch of charts with 30 or so series
and munga formatting. All this brings about any processor to it's knees,
but the Excel automation bit is the real time suck.

[Alan Webb]

Deko,
Oh. Stupid user problem. Yah. Those can be very hard to manage. What you
have is a federated data warehouse. It's the usual extract, transform,
validate, load process that software sales reps like to call middleware so
they can explain it to pointy-haired-bosses with check signing authority.
Imposing some formal structure on the process would improve performance but
may prove to be difficult because it means asking users to do things
differently. Good luck.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"deko" <de**@deko.com> wrote in message
news:b9****************@newssvr13.news.prodigy.com ...

And that 6 minutes is on a P4 3.4Ghz with 1Gb RAM and a SATA drive. No
question I'm doing things the hard way - but I'm constrained by what I've
been given. I have any number of (100 or so) mdbs that contain a bunch of
very non-normalized tables containing all kinds of stuff (including big
long
binary fields that need to be extracted - this takes a lot of time). Each
mdb yields from 0 to half a dozen or so worksheets. First I have to
locate
each mdb (which could be anywhere beneath the directory the users browses
to), then, for each mdb, link the tables I need, suck in, massage,
extract,
nerp, and format the data... then dump it out to Excel (this is where I
tried the compiled query) and create a bunch of charts with 30 or so
series
and munga formatting. All this brings about any processor to it's knees,
but the Excel automation bit is the real time suck.

[deko]

> Actually, the use of the & operator rather then the + operator has

different effects, and has had for quite some time now. You may want
to read up on them in Help. And they apply equally in VB code as well
as Access' Query builder, and SQL strings. Again, I strongly
encourage you to read up on the differences!
Thanks for the tip. Apparently the main difference is that is that & will
force string concatenation while + will add or concatonate, depending on the
expressions involved. But I seem to have trouble with & in compiled queries
for some reason.
Personally, I've given up on doing what you are doing using that
methodology. There's a routine I use called "ExportToExcel" that I've
been using for the last decade or so and it seems to work very well.
I've posted it in this newsgroup a few times. You may want to do a
google search on that name and give it a shot.

I looked for that routine and found different code titled ExportToExcel, but
nothing that looked like your official version. I'd be interested to see
it, but I've come up with my own favorite ExportToExcel function:

Public Function ExportToExcel(strXlsPath As String, _
strSheetName As String, _
strSource As String) As Boolean
On Error GoTo HandleErr
Dim db As DAO.Database
Dim qdfs As DAO.QueryDefs
Dim qdf As DAO.QueryDef
Dim strSql As String
Set db = CurrentDb
Set qdfs = db.QueryDefs
Set qdf = qdfs("qryExportToExcel")
strSql = "SELECT * INTO [Excel 8.0;Database=" & _
strXlsPath & "].[" & _
strSheetName & "] FROM [" & _
strSource & "];"
qdf.SQL = strSql
qdf.Execute (dbFailOnError)
ExportToExcel = True
Exit_Here:
On Error Resume Next
Set qdf = Nothing
Set qdfs = Nothing
Set db = Nothing
Exit Function
HandleErr:
Select Case Err.Number
Case Else
Debug.Print Err.Description
End Select
ExportToExcel = False
Resume Exit_Here
End Function

Here's why I like this (correct me where I'm wrong - I'm sure you know more
about this that I do):

1. It utilizes the JET engine so it's fast.
2. It's versatile because the string variables can be set to anything.
3. It's easy to use because it provides a WYSIWYG view of the export - just
look at the strSource table or query.

I'm using this in a loop where I never know what I'm going to have - data
returned by Query1 and strSheetName are created on the fly. I use the
Scripting Runtime to create a new strXlsPath when I run out of room in
(>255) in the workbook.

[Rick Brandt]

deko wrote:

Actually, the use of the & operator rather then the + operator has
different effects, and has had for quite some time now. You may
want to read up on them in Help. And they apply equally in VB code
as well as Access' Query builder, and SQL strings. Again, I
strongly encourage you to read up on the differences!

Thanks for the tip. Apparently the main difference is that is that &
will force string concatenation while + will add or concatonate,
depending on the expressions involved. But I seem to have trouble
with & in compiled queries for some reason.

The *Main* difference is how the two handle Nulls.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com