Perry Harold
pharold at proftesting.com
Mon Dec 15 09:43:50 CST 2003
List of reserved words for ODBC
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:
80/support/kb/articles/Q125/9/48.ASP&NoWebContent=1
Like Gustav said - desc is one of them.
Perry Harold
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gina Hoopes
Sent: Sunday, December 14, 2003 12:22 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Export to multiple Excel sheets
I feel like I'm so, so close, but I'm getting another error message. This
time it's in the Execute strSQL statement:
Do Until rs.EOF = True
strSQL = "SELECT Area, CenterNo, AcctNo, Desc, Actual03, Budget04,
ActNov03, AcctGroup, Forecast05, " & _
"Cont05, NewExpPgm," & _
"INTO rs.Fields(0).Value & " & _
"FROM qryGroupRpt IN 'H:\Gouldnan\HSL\mcobudget_fe_NG.mdb'" & _
"WHERE Area=""" & rs.Fields(0).Value & """"
'Note, I'm selecting fields from the Access table, pushing it INTO a table
named for the Requestor (Field 0 of
'the recordset object), and I'm telling to pull the records FROM tblRequests
IN an external Access .mdb. 'Since the next line uses a connection object
connected to an Excel Worksheet, it will create that 'table' as 'a new sheet
in the Excel Workbook
cnn.Execute strSQL
rs.MoveNext
Loop
rs.Clo
When it reaches cnn.Execute strSQL, I get an error that says "The SELECT
statement contains a reserved word or an argument name that is misspelled or
missing, or the punctuation is incorrect."
Drew, if you can figure out this one I'd appreciate your help. I'm also
going to try the solutions offered by Steve, Alun and Shaun. Thank you all
for your ideas, I hope this project can be put in the can tomorrow.
From: Drew Wutka <DWUTKA at marlow.com>
Reply-To: Access Developers discussion and problem
solving<accessd at databaseadvisors.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Subject: RE: [AccessD] Export to multiple Excel sheets
Date: Fri, 12 Dec 2003 19:39:38 -0600
Aha, found the problem. Here's the strSQL statement:
strSQL = "SELECT Area, CenterNo, AcctNo, Desc, Actual03, Budget04,
ActNov03, AcctGroup, Forecast05, " & _
"Cont05, NewExpPgm," & _
"INTO rs.Fields(0).Value & " & _
"FROM qryGroupRpt" & _
"WHERE Area=""" & rs.Fields(0).Value & """"
The problem is the 'FROM qryGroupRpt' part. You are going to run this SQL
against EXCEL, not the Access database, so you need to add an IN statement
to the FROM clause, so it would read : 'FROM qryGroupRpt IN
"C:\PathToDB\MyDatabase.mdb"'
Make sense?
Drew
-----Original Message-----
From: Gina Hoopes [mailto:hoopesg at hotmail.com]
Sent: Friday, December 12, 2003 4:18 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Export to multiple Excel sheets
Here's the full procedure:
Sub CenterGroupSheets()
Dim cnn As ADODB.Connection
Dim cnnAcc As ADODB.Connection
Dim strSQL As String
Dim rs As ADODB.Recordset
Set cnnAcc = CurrentProject.Connection
With cnnAcc
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "e:\entprs\billina\budget fy05\mcoBudget_fe.mdb"
End With
ExcelConnection cnn, "e:\entprs\billina\budget fy05\MCOBudget_Macro.xls" Set
rs = New ADODB.Recordset strSQL = "SELECT Area FROM qryGroupRpt GROUP BY
Area" 'Now going to open a recordset to the table, to get a list of the
requestors..... rs.Open strSQL, cnnAcc, adOpenKeyset, adLockReadOnly
rs.MoveFirst 'Now, I'm going to loop through the requestors, and push the
data out to Excel Do Until rs.EOF = True
strSQL = "SELECT Area, CenterNo, AcctNo, Desc, Actual03, Budget04,
ActNov03, AcctGroup, Forecast05, " & _
"Cont05, NewExpPgm," & _
"INTO rs.Fields(0).Value & " & _
"FROM qryGroupRpt" & _
"WHERE Area=""" & rs.Fields(0).Value & """"
'Note, I'm selecting fields from the Access table, pushing it INTO a table
named for the Requestor (Field 0 of 'the recordset object), and I'm telling
to pull the records FROM tblRequests
IN an external Access .mdb. 'Since the next line uses a connection object
connected to an Excel Worksheet, it will create that 'table' as 'a new sheet
in the Excel Workbook
cnn.Execute strSQL
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
cnnAcc.Close
Set cnnAcc = Nothing
MsgBox "Done"
End Sub
From: Drew Wutka <DWUTKA at marlow.com>
Reply-To: Access Developers discussion and problem
solving<accessd at databaseadvisors.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Subject: RE: [AccessD] Export to multiple Excel sheets
Date: Fri, 12 Dec 2003 14:36:06 -0600
What's the strSQL? (I know it's your SQL string, but what is it's value?)
Drew
-----Original Message-----
From: Gina Hoopes [mailto:hoopesg at hotmail.com]
Sent: Wednesday, December 10, 2003 4:19 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Export to multiple Excel sheets
Drew,
I've been working with your code and I'm stumped at the following line:
cnn.Execute strSQL
- it crashes there and I get a run-time error saying "Query input must
contain at least one table or query".
Since you indicated the "trick" was setting the Extended Properties in
ExlCnn, I wonder if I'm getting tripped up by not having an object library
or something.
Any ideas?
Thanks,
Gina
From: Drew Wutka <DWUTKA at marlow.com>
Reply-To: Access Developers discussion and problem
solving<accessd at databaseadvisors.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Subject: RE: [AccessD] Export to multiple Excel sheets
Date: Wed, 10 Dec 2003 13:58:52 -0600
Hehehe, I must be bored today, cause I decided to try it out. This works
PERFECTLY!
Okay, I dumped a table into a blank database. The table is called
tblRequests. It's the IS Request table that stores user requests for our
company. To see if it worked, I was going to push the records out to an
Excel file. I made a blank excel file, and removed all the sheets but one
(wouldn't let me remove all of them....). Then I wrote the following
code...technically, this code can be run from anything (with either VBA or
VB).
Function ExcelConnection(ExlCnn As ADODB.Connection, strFile As String)
'This function just makes an ADO connection to an excel file....the extended
properties is the only trick Set ExlCnn = New ADODB.Connection With ExlCnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0"
.Open strFile
End With
End Function
Private Sub Command0_Click()
Dim cnn As ADODB.Connection
Dim cnnAcc As ADODB.Connection
Dim strSQL As String
Dim rs As ADODB.Recordset
Set cnnAcc = New ADODB.Connection
With cnnAcc
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "D:\requests.mdb"
End With
ExcelConnection cnn, "D:\Book1.xls"
Set rs = New ADODB.Recordset
strSQL = "SELECT Requestor FROM tblRequests GROUP BY Requestor" 'Now going
to open a recordset to the table, to get a list of the requestors.....
rs.Open strSQL, cnnAcc, adOpenKeyset, adLockReadOnly rs.MoveFirst 'Now, I'm
going to loop through the requestors, and push the data out to Excel Do
Until rs.EOF = True
strSQL = "SELECT TicketNumber, RequestDate, RequestType,
RequestSubType, FullDescription INTO " & _
rs.Fields(0).Value & " " & _
"FROM tblRequests IN ""D:\requests.mdb"" " & _
"WHERE Requestor=""" & rs.Fields(0).Value & """"
'Note, I'm selecting fields from the Access table, pushing it INTO a table
named for the Requestor (Field 0 of 'the recordset object), and I'm telling
to pull the records FROM tblRequests IN an external Access .mdb. 'Since the
next line uses a connection object connected to an Excel Worksheet, it will
create that 'table' as 'a new sheet in the Excel Workbook
cnn.Execute strSQL
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
cnnAcc.Close
Set cnnAcc = Nothing
MsgBox "Done"
End Sub
Worked like a charm! The only thing you will need to add, if you want,
would be a little code to 'copy' a blank 'master' copy of an Excel Workbook
to the location and name of the final .xls file you want. Pretty simple
stuff, can write that if you want to.
The real beauty of this method, is since it is using ADO, it is MUCH faster
then using automation, and doesn't even require Access OR Excel to be on the
machine you run this from (it just needs MDAC, for ADO).
Drew
-----Original Message-----
From: Gina Hoopes [mailto:hoopesg at hotmail.com]
Sent: Wednesday, December 10, 2003 1:35 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Export to multiple Excel sheets
I'm willing to try anything. At the moment I'm trying to implement
Lambert's suggestion but I'm afraid I don't know enough to fill in the
blanks.
Gina
From: Drew Wutka <DWUTKA at marlow.com>
Reply-To: Access Developers discussion and problem
solving<accessd at databaseadvisors.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
Subject: RE: [AccessD] Export to multiple Excel sheets
Date: Wed, 10 Dec 2003 13:30:19 -0600
If you're going the code route, you may be able to pull this off with ADO. I
have never tried making a new sheet from ADO, in Excel. Technically it
should work, by just using a Make Table query. If you want to go this
route, I'll see if it works or not.
Drew
-----Original Message-----
From: Gina Hoopes [mailto:hoopesg at hotmail.com]
Sent: Wednesday, December 10, 2003 11:54 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Export to multiple Excel sheets
I would like to avoid hard-coding the centers in each dept. since they can
change from year to year, and one of the depts has 64 centers which even by
itself would be a lot of queries.
I'm thinking the solution may be to create a recordset of each dept and loop
through that, creating a recordset within that set for each center. If
that's do-able, I don't know how to properly code the loops or how to export
as it loops through.
Thanks again,
Gina
From: "Heenan, Lambert" <Lambert.Heenan at AIG.com>
To: "'Access Developers discussion and problem solving'"
<accessd at databaseadvisors.com>
CC: "'hoopesg at hotmail.com'" <hoopesg at hotmail.com>
Subject: RE: [AccessD] Export to multiple Excel sheets
Date: Wed, 10 Dec 2003 12:35:59 -0500
If you have a different query for each Center then the export will create
different sheets for each one.
Don't want multiple queries, but would rather use different parameters for
each Center? Ok. Just use CopyObject to create a temporary copy of the
query, export it and then delete the copy.
Lambert
> -----Original Message-----
> From: Gina Hoopes [SMTP:hoopesg at hotmail.com]
> Sent: Wednesday, December 10, 2003 12:14 PM
> To: AccessD at databaseadvisors.com
> Subject: [AccessD] Export to multiple Excel sheets
>
> You all have been so helpful to me on my projects - I hope one day I
can
> be
> on the helping end instead of always asking for help.
>
> I've got a table with about 10 fields, 2 of which are Dept and
Center.
> Any
> given Dept might have multiple Centers. I'm exporting query results
to
> Excel spreadsheets, but I haven't been able to figure out how to have
a
> Dept
> with multiple Centers export to one Excel file with a separate sheet
for
> each Center. Can anyone give me some ideas on how to accomplish
this?
Thanks,
Gina
_________________________________________________________________
Tired of slow downloads and busy signals? Get a high-speed Internet
connection! Comparison-shop your local high-speed providers here.
https://broadband.msn.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com