UtterAccess Discussion Forums > export table to another db

Full Version: export table to another db

UtterAccess Discussion Forums > Microsoft® Access > Access Automation

tinkythomas

Jul 17 2008, 12:50 PM

Hi,

I want to export and rename a table with the current date to another access db, is this possible?

I have tried the following but this errors saying it doesn't follow access naming rules.

Here is the code

CODE

'rename table to include date
    DoCmd.Rename "EAGLE_BACKUP" & "_" & Format(Date, "dd/mmm/yy"), acTable, "EAGLE_BACKUP"

    'transfer the data to the eagle backup directory
    DoCmd.TransferDatabase acExport, "Microsoft Access", srcfile, acTable, _
    "EAGLE_BACKUP" & "_" & Format(Date, "dd/mmm/yy")

I would be gratefuil if someone could tell me where I'm going wrong?

Thank-you

Doug Steele

Jul 17 2008, 01:01 PM

Have you assigned the appropriate value to srcfile? (it needs to be the full path to the database file)

You need to repeat the name of the table twice: the first time's the name of the table in the current database, the second time is what you want to call the table in the other database (the database pointed to by srcfile)

fkegley

Jul 17 2008, 01:03 PM

I think / won't work in a file name. Try just the digits without any separators.

tinkythomas

Jul 17 2008, 02:06 PM

Thanks for the replies and suggestions. I tried renaming without separators but receive the same error. Here is the full function. The majority of the queries have been commented out for testing purposes.

CODE

Function Eagle_backup()
    On Error GoTo Backup_Err

    'declare variables
    Dim user As String, srcfile As String
    Dim db As Database
    Set db = CurrentDb
    user = Environ("userprofile")

    'specify destination file
    srcfile = user & "\Desktop\Eagle backup\Eagle backup.mdb"

    'check if the prevoiusly created table exists, if it does delete it
    If TableExists("EAGLE_BACKUP") Then
        db.TableDefs.Delete "EAGLE_BACKUP"
    End If

    'turn off message pop ups
    DoCmd.SetWarnings False

    'run all the queries
    DoCmd.OpenQuery "BA_BACKUP"
    'DoCmd.OpenQuery "BB_BACKUP"
    'DoCmd.OpenQuery "BD_BACKUP"
    'DoCmd.OpenQuery "BG_BACKUP"
    'DoCmd.OpenQuery "BH_BACKUP"
    'DoCmd.OpenQuery "BI_BACKUP"
    'DoCmd.OpenQuery "BJ_BACKUP"
    'DoCmd.OpenQuery "BK_BACKUP"
    'DoCmd.OpenQuery "CA_BACKUP"
    'DoCmd.OpenQuery "CB_BACKUP"
    'DoCmd.OpenQuery "CC_BACKUP"
    'DoCmd.OpenQuery "CD_BACKUP"
    'DoCmd.OpenQuery "CG_BACKUP"
    'DoCmd.OpenQuery "CH_BACKUP"
    'DoCmd.OpenQuery "CI_BACKUP"
    'DoCmd.OpenQuery "EA_BACKUP"
    'DoCmd.OpenQuery "EF_BACKUP"
    'DoCmd.OpenQuery "HA_BACKUP"
    'DoCmd.OpenQuery "HB_BACKUP"
    'DoCmd.OpenQuery "HD_BACKUP"
    'DoCmd.OpenQuery "HF_BACKUP"
    'DoCmd.OpenQuery "HG_BACKUP"
    'DoCmd.OpenQuery "HK_BACKUP"
    'DoCmd.OpenQuery "HL_BACKUP"
    'DoCmd.OpenQuery "HO_BACKUP"
    'DoCmd.OpenQuery "HP_BACKUP"
    'DoCmd.OpenQuery "HI_BACKUP"
    'DoCmd.OpenQuery "HJ_BACKUP"
    'DoCmd.OpenQuery "XB_BACKUP"
    'DoCmd.OpenQuery "XF_BACKUP"
    DoCmd.OpenQuery "TXB_BACKUP"
    'DoCmd.OpenQuery "TREV_STATUS_BACKUP"
    'DoCmd.OpenQuery "TREV_COMMENTS_BACKUP"
    'DoCmd.OpenQuery "TREV_RESPONSE_BACKUP"
    'DoCmd.OpenQuery "TCA_BACKUP"
    'DoCmd.OpenQuery "TVALID_BACKUP"
    'DoCmd.OpenQuery "TVALID_TPM_BACKUP"
    'DoCmd.OpenQuery "TDM_BACKUP"
    'DoCmd.OpenQuery "TDMR_BACKUP"
    'DoCmd.OpenQuery "TSOURCE_BACKUP"
    'DoCmd.OpenQuery "TDRAW_ISSUE_BACKUP"
    'DoCmd.OpenQuery "ZBCB_BACKUP"
    'DoCmd.OpenQuery "ZCHANGE_BACKUP"
    'DoCmd.OpenQuery "ZD_BACKUP"

    'rename table to include date
    DoCmd.Rename "EAGLE_BACKUP" & Format(Date, "ddmmyy"), acTable, "EAGLE_BACKUP"

    'transfer the data to the eagle backup directory
    DoCmd.TransferDatabase acExport, "Microsoft Access", srcfile, acTable, _
    "EAGLE_BACKUP" & Format(Date, "ddmmyy")

    'delete the eagle backup table to reduce database bloat
    db.TableDefs.Delete "EAGLE_BACKUP" & Format(Date, "ddmmyy")

    'turn on message pop ups
    DoCmd.SetWarnings True

Backup_Exit:
    Exit Function

Backup_Err:
    MsgBox Error$
    Resume Backup_Exit

End Function

Any other ideas?

tinkythomas

Jul 18 2008, 12:26 PM

Doug, thanks for your reply. You were right I had not specified the destination in the transferdatabase method. I added that with the date function and now all is well.

Thanks for your help and thanks to Frank also.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.