Results 1 to 5 of 5
  1. #1
    lamore48 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    40

    Rename a table

    Hi All I have a form which allows me to navigate to a excel file and import it to my access database and it names the table to whatever the file name is. How can I add to my code to rename my table to "ExportData"

    Code:
    Public Sub btnImportSpreadsheet_Click()    
        Dim FSO As New FileSystemObject
            
        
        If Nz(Me.txtFileName, "") = "" Then
            MsgBox "Please select a file!"
            Exit Sub
        End If
         
       
         If FSO.FileExists(Nz(Me.txtFileName, "")) Then
            ExcelImport.ImportExcelSpreadsheet Me.txtFileName, FSO.GetFileName(Me.txtFileName)
         Else
            MsgBox "File not found!"
        End If
        
    End Sub




    Thanks in advance

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Do you mean:

    docmd.TransferSpreadsheet acImport ,acSpreadsheetTypeExcel12 ,"ExportData", Me.txtFileName ,true,"sheet1"

  3. #3
    lamore48 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    40
    That works! Thanks, now problem is that ExportData is an existing table and when I run this routine it adds a copy of the data to the existing table, so I guess I need to add code to delete the table first and then add the new one after. Any thoughts on how to do it?


    Thanks,
    Lenny

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by lamore48 View Post
    ...now problem is that ExportData is an existing table and when I run this routine it adds a copy of the data to the existing table, so I guess I need to add code to delete the table first and then add the new one after. Any thoughts on how to do it?
    Constantly deleting, then recreating table is a sure path to corruption.

    Delete the RECORDS, then do the import.

    Something like this:
    Code:
    'delete the records in the table
    CurrentdB.Execute "DELETE * From ExportData", dbfailonerror
    
    'import new records
    DoCmd.TransferSpreadsheet acImport ,acSpreadsheetTypeExcel12 ,"ExportData", Me.txtFileName ,True

    Another method is to link to the Excel workbook, then treat the workbook as a standard table.
    Or, use automation to open the workbook and read through the workbook, appending the data to the table.

  5. #5
    lamore48 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    40
    Thank You for your help

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Copy Table and rename to "newTable"
    By MadTom in forum Programming
    Replies: 12
    Last Post: 06-13-2018, 08:05 AM
  2. ALTER TABLE rename a column
    By rockell333 in forum Queries
    Replies: 8
    Last Post: 09-18-2015, 02:59 PM
  3. rename twice
    By slimjen in forum Programming
    Replies: 2
    Last Post: 09-22-2014, 08:26 AM
  4. Replies: 4
    Last Post: 11-05-2010, 04:56 AM
  5. How to rename a linked table which link to ODBC
    By Connie1122 in forum Database Design
    Replies: 6
    Last Post: 08-02-2010, 02:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums