Access [2000] to Excel

  • Thread starter Thread starter Jim Bunton
  • Start date Start date
J

Jim Bunton

the following query works fine to export a set of data into an excel
spreadsheet
SELECT * INTO [Excel 8.0;DATABASE=C:\temp\MySpreadSheet.xls].WorkSheet1
FROM RestaurantCustomerInformation;


BUT when put into code:-
objDB.Execute "SELECT * INTO [Excel
8.0;DATABASE=C:\temp\MySpreadSheet.xls].[WorkSheet1] FROM
[RestaurantCustomerInformation]"

ERROR > "Too few paramaters - expected 1"

objDB.name
objDB.Containers.Count
etc
work fine so the database object looks ok.
 
Jim Try something like this:

Sub MoveToExcel()
Dim strSQL
strSQL = "SELECT * INTO [Excel 8.0;Database=" _
& "c:\MySpreadSheet.xls].[WorkSheet1]" _
& " FROM RestaurantCustomerInformation;"
CurrentDb.Execute strSQL
End Sub

Regards

Kevin
 
Dear Kevin,
Thanks for your reply to my posting.
I had, in the mean time, stumbled upon an almost identical approach
DoCmd.RunSQL sql
rather than CurrentDb.Execute sql.

using the DoCmd approach works
using CurrentDb.Execute sql gives the same error as before! (too few
parameters)
..
My current theory as to the error message:-
The query I am using takes a value from a Form
WHERE OnLineOrders.RestaurantId=[FORMS]![Main]![FindCompany]
I guess this is the 'parameter' that dbobject.execute sql is throwing an
error on
Why DoCmd.RunSql does not throw this error?
No idea - but useful to know I guess.

Jim


kc-mass said:
Jim Try something like this:

Sub MoveToExcel()
Dim strSQL
strSQL = "SELECT * INTO [Excel 8.0;Database=" _
& "c:\MySpreadSheet.xls].[WorkSheet1]" _
& " FROM RestaurantCustomerInformation;"
CurrentDb.Execute strSQL
End Sub

Regards

Kevin

Jim Bunton said:
the following query works fine to export a set of data into an excel
spreadsheet
SELECT * INTO [Excel 8.0;DATABASE=C:\temp\MySpreadSheet.xls].WorkSheet1
FROM RestaurantCustomerInformation;


BUT when put into code:-
objDB.Execute "SELECT * INTO [Excel
8.0;DATABASE=C:\temp\MySpreadSheet.xls].[WorkSheet1] FROM
[RestaurantCustomerInformation]"

ERROR > "Too few paramaters - expected 1"

objDB.name
objDB.Containers.Count
etc
work fine so the database object looks ok.
Click to expand...
Click to expand...
 
Hi Jim

It is a fairly common practice in the use of docmd.runsql to brackett that
line with
docmd.setwarnings false and docmd.setwarnings true. If you have those in
your program
the query will still fail, you just won't know it.

If your query is expecting a value from a form, the form has to be open and
the control populated in order to successfull run the query

Regards

Kevin


Jim Bunton said:
Dear Kevin,
Thanks for your reply to my posting.
I had, in the mean time, stumbled upon an almost identical approach
DoCmd.RunSQL sql
rather than CurrentDb.Execute sql.

using the DoCmd approach works
using CurrentDb.Execute sql gives the same error as before! (too few
parameters)
.
My current theory as to the error message:-
The query I am using takes a value from a Form
WHERE OnLineOrders.RestaurantId=[FORMS]![Main]![FindCompany]
I guess this is the 'parameter' that dbobject.execute sql is throwing an
error on
Why DoCmd.RunSql does not throw this error?
No idea - but useful to know I guess.

Jim


kc-mass said:
Jim Try something like this:

Sub MoveToExcel()
Dim strSQL
strSQL = "SELECT * INTO [Excel 8.0;Database=" _
& "c:\MySpreadSheet.xls].[WorkSheet1]" _
& " FROM RestaurantCustomerInformation;"
CurrentDb.Execute strSQL
End Sub

Regards

Kevin

Jim Bunton said:
the following query works fine to export a set of data into an excel
spreadsheet
SELECT * INTO [Excel 8.0;DATABASE=C:\temp\MySpreadSheet.xls].WorkSheet1
FROM RestaurantCustomerInformation;


BUT when put into code:-
objDB.Execute "SELECT * INTO [Excel
8.0;DATABASE=C:\temp\MySpreadSheet.xls].[WorkSheet1] FROM
[RestaurantCustomerInformation]"

ERROR > "Too few paramaters - expected 1"

objDB.name
objDB.Containers.Count
etc
work fine so the database object looks ok.
Click to expand...
Click to expand...
Click to expand...
 
There's a more fundamental difference between RunSQL and Execute.

RunSQL will resolve references to parameters, Execute will not.

To be able to use a saved query that uses parameters with the Execute
method, you have to explicit resolve each parameter, using code like

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim prmCurr As DAO.Parameter

Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("NameOfQuery")
For Each prmCurr In qdfCurr.Parameters
prmCurr.Value = Eval(prmCurr.Name)
Next prmCurr
qdfCurr.Execute

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


kc-mass said:
Hi Jim

It is a fairly common practice in the use of docmd.runsql to brackett that
line with
docmd.setwarnings false and docmd.setwarnings true. If you have those in
your program
the query will still fail, you just won't know it.

If your query is expecting a value from a form, the form has to be open
and the control populated in order to successfull run the query

Regards

Kevin


Jim Bunton said:
Dear Kevin,
Thanks for your reply to my posting.
I had, in the mean time, stumbled upon an almost identical approach
DoCmd.RunSQL sql
rather than CurrentDb.Execute sql.

using the DoCmd approach works
using CurrentDb.Execute sql gives the same error as before! (too few
parameters)
.
My current theory as to the error message:-
The query I am using takes a value from a Form
WHERE OnLineOrders.RestaurantId=[FORMS]![Main]![FindCompany]
I guess this is the 'parameter' that dbobject.execute sql is throwing an
error on
Why DoCmd.RunSql does not throw this error?
No idea - but useful to know I guess.

Jim


kc-mass said:
Jim Try something like this:

Sub MoveToExcel()
Dim strSQL
strSQL = "SELECT * INTO [Excel 8.0;Database=" _
& "c:\MySpreadSheet.xls].[WorkSheet1]" _
& " FROM RestaurantCustomerInformation;"
CurrentDb.Execute strSQL
End Sub

Regards

Kevin

the following query works fine to export a set of data into an excel
spreadsheet
SELECT * INTO [Excel 8.0;DATABASE=C:\temp\MySpreadSheet.xls].WorkSheet1
FROM RestaurantCustomerInformation;


BUT when put into code:-
objDB.Execute "SELECT * INTO [Excel
8.0;DATABASE=C:\temp\MySpreadSheet.xls].[WorkSheet1] FROM
[RestaurantCustomerInformation]"

ERROR > "Too few paramaters - expected 1"

objDB.name
objDB.Containers.Count
etc
work fine so the database object looks ok.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top