Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    123

    Unanswered: Export backup files

    I want to create a macro, or VBA procedure, to export four tables on a nightly basis. Is there a way to mimic the Save As/Export function while a table is open in datasheet view? I want to:
    1. export each table separately into the same directory as a text file
    2. use a semicolon ( as the delimiter
    3. include field names as first row
    4. make the text file name dynamic based on the table name and date, such as "TABLE_NAME_20040421.txt", or prompt me to enter a name.

    I will run this procedure from a form, so I don't really want to get into schedulers or anything like that.

    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8

    here some VB

    Here some code that will Export a Table for you


    Written it in access97


    Code:
    Sub Create_File(FileName As String, TableName As String)
        Dim rst As Recordset
        Dim db As DataBase
        Dim Recount As Long
        Dim Xline As String
        Dim Feildloop As Long
        Dim FeildMax As Long
        
        Set db = CurrentDb
        Set rst = db.OpenRecordset(TableName)
        rst.MoveFirst
        Recount = 0
        FeildMax = rst.Fields.Count - 1
        Open FileName For Output As #1
        Do Until rst.EOF
        Xline = ""
        If Recount = 0 Then
        
        For Feildloop = 0 To FeildMax
        Xline = Xline & Chr$(34) & rst.Fields(Feildloop).Name & Chr$(34) & ","
        Next
        Else
        For Feildloop = 0 To FeildMax
        Xline = Xline & Chr$(34) & IIf(rst.Fields(Feildloop).Type = dbDate, Format(rst.Fields(Feildloop).VALUE, "dd mmm yyyy"), rst.Fields(Feildloop).VALUE) & Chr$(34) & ","
        Next
        
        End If
        Xline = mid$(Xline, 1, Len(Xline) - 1)
        'Debug.Print Xline
        Print #1, Xline
        rst.MoveNext
        Recount = Recount + 1
        Loop
        rst.Close
        Close (1)
    End Sub
    to use it just
    Call Create_File("C:\FileName.txt","TableName")

    hope this help
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Aug 2003
    Posts
    123

    Re: Export backup files

    This is good, thanks, except that it turned all the fields into text with quotes. I would prefer to retain each fields existing data type, i.e. dates stay date/time, doubles & integers stay number, only text gets quotes, etc.

    Where should I modify what you sent with those changes?

  4. #4
    Join Date
    Aug 2003
    Posts
    123

    Re: Export backup files

    OK, it worked the first couple of times, but now I'm getting a "Type mismatch error" at the following line:

    Xline = Xline & Chr$(34) & IIf(rcd.Fields(Feildloop).Type = dbDate, Format(rcd.Fields(Feildloop).Value, "dd mmm yyyy"), rcd.Fields(Feildloop).Value) & Chr$(34) & ","

    After stopping the procedure, it leaves either an empty text file or a text file with just the header row. What gives? Is it because I made FileName and TableName variants rather than Strings?

    Sub Create_File(FileName, TableName)
    Dim rcd As Recordset
    Dim dbs As Database
    Dim Recount As Long
    Dim Xline As String
    Dim Feildloop As Long
    Dim FeildMax As Long

  5. #5
    Join Date
    Aug 2003
    Posts
    123

    Re: Export backup files

    By the way, the export is getting stuck at the following row on one of the tables with the above type mismatch error:

    See the attached Excel file with row 210 (fine) and row 211 (error).

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    Quote Originally Posted by jrn0074
    This is good, thanks, except that it turned all the fields into text with quotes. I would prefer to retain each fields existing data type, i.e. dates stay date/time, doubles & integers stay number, only text gets quotes, etc.

    Where should I modify what you sent with those changes?
    Sorry about the time getting back to you

    in the for loop

    look @ the

    rst.Fields(Feildloop).Type
    so
    What I would do is

    For Feildloop = 0 To FeildMax ' loop 2

    Select Case rst.Fields(Feildloop).Type

    case dbText Or dbMemo
    Xline = Xline & Chr$(34) & rst.Fields(Feildloop).VALUE & Chr$(34) & ","
    case dbdate
    Xline = Xline & "#" & rst.Fields(Feildloop).VALUE & "#" & ","
    case dbNumeric Or dbdouble Or dbDecimal
    Xline = Xline & rst.Fields(Feildloop).VALUE & ","
    End Select

    Next

    You will have to write a case for each type of

    thinking .................
    the Error could be

    rst.Fields(Feildloop).VALUE has a " in it will have to take out out of the text.

    hope this help
    Last edited by myle; 04-25-04 at 22:30.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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