Access MDB data into OWC Spreadsheet - HELP?

[BarryMVS]

Hi,

I am trying to use an SQL lookup query to extract data from an Access MDB and place it into the Excel OWC Spreadsheet object using ASP.

I have managed to produce the ASP script and create the Spreadsheet object. I have writen my SQL and I know that it works OK. The problem I have now is trying to get the SQL results into the spreadsheet object.

I know you can import data into the object using various routes including XML, but I don't know how to convert the SQL results into XML or directly in to the Spreadsheet.

Any ideas would be most welcomed.

Thanks,

Barry

ICT Network Administrator
IT Services Manager

 

[PHV]

Have you tried the CopyFromRecordset method of the Range object ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BarryMVS]

PHV,

No. I don't have any help info on how to use OWC some I'm kinda guessing and trying to see what works.

I can not find any practical info on Microsoft's KB or MSDN.

If you could elaborate on that method or point me to a web site I would be greatful.

Thanks,

Barry

ICT Network Administrator
IT Services Manager

 

[PHV]

Take a look at the Excel VBA Object Browser and Help file.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BarryMVS]

PHV,

Thanks for the advice.

I've locaited the VB help and looked up various OWC VB commands, including the CopyFromRecordset method.

I have put it into play but I am not getting any data into my sheet.

In fact the only thing being returned is the first field header!

The code that I put in has come from the VB help regarding the CopyFromRecordset method.

StrSql = "SELECT Customers.[Their Branch Ref], Customers.[Address Line 3], [Service Records].[Date Call Placed], [Service Records].[Problem Description], [Service Records].[Service Record ID], Employees.Initials, [Service Records].[Last Action], Customers.[Last Site Visit], [Status Codes].Description AS Status FROM ((Customers RIGHT JOIN [Service Records] ON Customers.[Customer ID] = [Service Records].[Customer ID]) LEFT JOIN Employees ON [Service Records].[Employee ID] = Employees.[Employee ID]) LEFT JOIN [Status Codes] ON [Service Records].Status = [Status Codes].Status WHERE ((([Service Records].[Date Call Placed])>= #" & startdate & "# And ([Service Records].[Date Call Placed])<= #" & enddate &"#) AND ((Customers.Group)=" & groupid &")) ORDER BY [Service Records].[Service Record ID];" ' copy SQL from SAMS mdb for report look up.
    Set rsExport = conn.Execute (StrSql) 
	
	For iCols = 0 to rsExport.Fields.Count - 1
		oOrdersSheet.Cells(1, iCols + 1).Value = rsExport.Fields(iCols).Name
	Next
		oOrdersSheet.Range(oOrdersSheet.Cells(1, 1), _
	oOrdersSheet.Cells(1, rsExport.Fields.Count)).Font.Bold = True
	oOrdersSheet.Range("A2").CopyFromRecordset rsExport

The variables startdate, enddate & groupid are going to be supplied by a form once I have finnished all this first, but for the moment I have hard coded the variable to be equivilant to a data range that I know exists.

The only response I get is the first field name in A2. Is this because the Range is set to only A2?
If so, how can I work out the range from the Recordset as it will change every time.

If you can give me any advice on my mistake, I would be greatful.

Thanks,

Barry

ICT Network Administrator
IT Services Manager

 

[BarryMVS]

PHV,

I resolved the above problem. It would seem that the example script that Microsoft gives you trys to put the whole of your recordset into just cell A2!!

I do, however, have a new problem.

I have now got all of my recordset appearing in my OWC object. I have tried to add some addititonal information to the top of my spreadsheet by using the following code:

For iCols = 0 to rsExport.Fields.Count - 1
		oOrdersSheet.Cells(5, iCols + 1).Value = rsExport.Fields(iCols).Name
	Next
		oOrdersSheet.Range(oOrdersSheet.Cells(5, 1), _
	oOrdersSheet.Cells(5, rsExport.Fields.Count)).Font.Bold = True
	oOrdersSheet.Range("A5:I" & x & "").CopyFromRecordset rsExport
	oOrdersSheet.Range("A2").Value = "Call-out Status Report"
	oOrdersSheet.Range("A3").Value = rsName.Fields("Company Name").Value
	oOrdersSheet.Range("D2").Value = "From:"
	oOrdersSheet.Range("E2").Value = startdate
	oOrdersSheet.Range("D3").Value = "To:"
	oOrdersSheet.Range("E3").Value = enddate

Now if I run my spreadsheet construction page with out specifying the OWC object, I can open my spreadsheet straight into Excel. If I do this then the additional information appears as I want it to above line 5.

If I run the script with the OWC object then the additional information gets missed and the top line of my spreadsheet is line 5 containing the field names!

I am somewhat stumpped by this and would be most greatful for any advice on how I might be able to correct this.

Thanks,

Barry

ICT Network Administrator
IT Services Manager