Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    exporting a table from access (2003)

    Hi,

    I am trying to export a part of a table as a text file using vba in access. I am stuck with two things.

    Firstly I only want to export the part of the table that matches certain criteria.
    Secondly I can't get the delimiter to be

    Heres the code I have so far

    Thank

    Private Sub Command41_Click()
    Dim dbs As DAO.Database
    Dim strSQL As String
    Dim qry As DAO.QueryDef

    Set dbs = CurrentDb

    strSQL = "SELECT ID, FIND, DESCRIPTION FROM TblTextFileData WHERE TemplateCode = " & """" & Me.TemplateCode & """"
    Set qry = dbs.CreateQueryDef("")
    qry.SQL = strSQL

    DoCmd.TransferText acExportDelim, "", qry.SQL, "H:test.txt", False, ""

    strSQL = "UPDATE TblSLSTextFiles SET LastUpdate = Now() WHERE TxtCode = " & """" & Me.Text44 & """"
    dbs.Execute (strSQL)
    End Sub
    Regards
    Gerbil (AKA Kevin)

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

    Re: exporting a table from access (2003)

    First, start the text export wizard, select the table, and specify the details but instead of clicking Finish, click Advanced..., then Save As...
    Give the export specification a name (or accept the proposed one), then click OK.
    You don't have to finish the export now, you can Cancel your way out.
    You must use the export specification in the code.

    You have to export a saved query, you can't export an SQL string.

    Example:

    Dim dbs As DAO.Database
    Dim strSQL As String
    Dim qry As DAO.QueryDef

    Set dbs = CurrentDb
    strSQL = "SELECT ID, FIND, DESCRIPTION FROM TblTextFileData WHERE TemplateCode = """ & Me.TemplateCode & """"
    Set qry = dbs.CreateQueryDef("qryTemp", strSQL)

    DoCmd.TransferText acExportDelim, "TblTextFileData Export Specification", "qryTemp", "H:test.txt", False

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exporting a table from access (2003)

    Thanks Hans,

    Works great. Is there away to store the query virually, or is it best to just delete the query using DoCmd.DeleteObject when Im done with it.

    Thanks
    Regards
    Gerbil (AKA Kevin)

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

    Re: exporting a table from access (2003)

    From <post:=736,741>post 736,741</post:>: "You have to export a saved query (...)"

    You can't "store the query virtually". You can delete it if you wish. Another option would be to leave it behind, and simply change its SQL property as needed, instead of creating a new query each time.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exporting a table from access (2003)

    Many thanks
    Regards
    Gerbil (AKA Kevin)

Posting Permissions

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