Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL to export a file? (Access 2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL to export a file? (Access 2000)

    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.

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL to export a file? (Access 2000)

    (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;

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL to export a file? (Access 2000)

    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.

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL to export a file? (Access 2000)

    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?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL to export a file? (Access 2000)

    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, ...

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL to export a file? (Access 2000)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •