SQL to export a file? (Access 2000)

[Egg 'n' Bacon]

I'm currently doing a night class which involves an Access project. The scenario, data & specifications are all supplied, so you'd think this'd be easy. NO. Let me explain, there's this one task , and I quote;

"...write SQL queries to mine the data as follows...extract data and produce a file suitable for export to another application.?

Now I know it makes sense to simply put a button on a form to export any table or query to an Excel file, but to do it with SQL has me really stumped.

Does anyone know how it can be done? I'm afraid I've only just started with SQL too, so if there is a solution, please be gentle with me [img]/forums/images/smilies/smile.gif[/img]

TIA

[HansV]

There is an SQL statement behind every query in Access, so in that sense every query is an SQL query. I suppose you must be able to create a query in code, based on specififications. One way to do this is through DAO. You need to set a reference to the Microsoft DAO 3.6 Object Library in Tools | References...

Example: you have a form with text boxes txtStartDate and txtEndDate, used to limit the records in a select query.

<img src=/w3timages/blueline.gif width=33% height=2>

Dim strQueryName As String
Dim strExportName As String
Dim strSQL As String
Dim dbs As DAO.Database

strQueryName = "qryTemp"
strExportName = "C:ExcelTest.xls"

strSQL = "SELECT * FROM tblOrders WHERE OrderDate Between #" & _
Format(Me.txtStartDate, "mm/dd/yyyy") & "# And #" & _
Format(Me.txtEndDate, "mm/dd/yyyy") & "#"

Set dbs = CurrentDb
dbs.CreateQueryDef strQueryName, strSQL

DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel9, _
TableName:=strQueryName, FileName:=strExportName, HasFieldNames:=True

dbs.QueryDefs.Delete strQueryName
Set dbs = Nothing

<img src=/w3timages/blueline.gif width=33% height=2>

Note: yes, I know that there are other, perhaps more efficient ways to do this; it's just meant as an illustration.

[Egg &#39;n&#39; Bacon]

(Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

Many thanks for the quick reply Hans, but I'm a little confused by your answer. What your example seemed to be was more akin to VBA (of which I'm even less accomplished than with SQL!).

The weird thing is, they've just given us a VERY basic SQL tutorial (paper) and told to get on with it. So what I believe they want us to do, is do fairly basic queries in the SQL view.

Here's a link to the actual project document (the problem bit is task 3 c) iii)
http://www.edexcel.org.uk/virtualcontent/7...ral_learner.pdf

The route I'm currently headed is to do a 'make table' for the basic data & give the 'user' some buttons to export each table to an Excel file. Here's what I've done so far (I split the main questionnaire table into TblVisitors & TblAnswers);

SELECT TblVisitor.*, TblAnswers.* INTO TblExport
FROM TblVisitor INNER JOIN TblAnswers ON TblVisitor.Visitor_ID=TblAnswers.Visitor_ID;

[HansV]

I'm sorry, I thought it was more complicated.

In a sense, you're in luck. Access is a good tool for learning SQL "hands-on". You can create queries in the design grid, then switch to SQL view to look at the SQL that corresponds to your design.

[Egg &#39;n&#39; Bacon]

In a way it is more complicated, the key part of the task is where it says;

"produce a file suitable for export to another application.?

Now I know in earlier versions, Access used files for the different components, but now... well I'm confused as to what is required. Any ideas?

[HansV]

You can export a query to a file: select the query in the database window, or open it, then select File | Export... You can export to a text file, to an Excel workbook, to RTF (Word compatible), to HTML, ...

[Egg &#39;n&#39; Bacon]

Many many thanks Hans, that is EXACTLY what I thought. But I must admit to feeling rather stupid when I read that particular spec'.