Error When trying to transfer records to excel.

[sna1maa14]

I keep getting this error 2147217900 (80040e14) Invalid Sql statement; Expected Delete,Insert,Procedure,Select or Update when trying to transfer records to Excel workbook. Here’s my code run from a command button.

Dim strMonth As String
Dim rst As ADODB.Recordset
Dim xlApp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet

Set xlApp = New Excel.Application
Set xlbook = xlApp.Workbooks.Open("d:\cams\CAMSWeeklyTop10.xls") '.Add
Set xlsheet = xlbook.Worksheets("Summary")
Set rst = New ADODB.Recordset

strWeekEnding = Format(Date - Weekday(Date, vbSunday), "DD/MM")
strMonth = Format(Date, "mmmm")

rst.Open _
Source:="WeeklyTop10", _
ActiveConnection:=CurrentProject.Connection

xlApp.DisplayAlerts = False

With xlsheet
With .Cells(3, 1)
.Value = rst.Fields(0).Name
.Font.Bold = True
End With
With .Cells(3, 2)
.Value = rst.Fields(1).Name
.Font.Bold = True
End With
With .Cells(3, 3)
.Value = rst.Fields(2).Name
.Font.Bold = True
End With
.Range("A4").CopyFromRecordset rst
.Columns.AutoFit
.SaveAs "d:\cams\CAMSWeeklyTop10.xls"
End With

xlApp.Workbooks.Close
xlApp.DisplayAlerts = True

Set rst = Nothing
Set xlsheet = Nothing
Set xlbook = Nothing
Set xlApp = Nothing
MsgBox "Done"

The query I'm trying to use uses two other queries that get a parameter passed to them from the Form with the command button on it. I pass what week of the year is based on a date selection. If I run the queries by them selves they work but if I use
rst.Open _
Source:="WeeklyTop10", _
ActiveConnection:=CurrentProject.Connection
it won’t work. Oh and if I go to the queries and put in a week in the criteria in Desing view it will work but I need to be able to change the week.Any suggestions or ideas would be greatly appreciated.

Sna1maa14

 

[SkipVought]

Hi,

You opend the TABLE WeeklyTop10

You NEVER executed a QUERY.

You might rather do something like...

rst.Open _
    Source:="Select * From WeeklyTop10", _
    ActiveConnection:=CurrentProject.Connection

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[sna1maa14]

Ok tried that and now it says No value given for required parameter??? Some reason it's passing the week when it runs.

Sna1maa14

 

[sna1maa14]

Opps It's not passing.

 

[RoyVidar]

1 - it is often recommended to tell what kind of object you're trying to open - so for a query

[tt]rs.open "WeeklyTop10", currentproject.connection,,,adcmdstoredproc[/tt]

(adCmdStoredProc - for SkipVought's suggestion using dynamic SQL, adCmdText)

2 - paramters from forms - they need to be resolved - you could try using dynamic SQL - i e concatenate the whole query string, else, here's a couple of threads showing how to resolve parameters with ADO recordset thread709-1019057, thread709-819033.

For the latter thread, the first suggestion works 2000+ versions, further down there's a simplified version working on 2002+ versions.

Roy-Vidar

 

[sna1maa14]

Thanks for your help guys. I ended up changing the queries so that i pass the parameter in the last query and i used a SQL select in the rst.open. Worked like a chram. I think I need a beer after a long day of staring at code.

Sna1maa14