Results 1 to 3 of 3
  1. #1
    deldridge is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    2

    Import help, I have a working access database but needs tweaks

    Hi,

    I have a working database for a while and haven't been able to work this out, but I need to be able to import and delete tables if the tables already exist.

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub btnBrowse_Click()
        Dim diag As Office.FileDialog
        Dim item As Variant
        
        Set diag = Application.FileDialog(msoFileDialogFilePicker)
        diag.AllowMultiSelect = False
        diag.Title = "Please select an Excel Spreadsheet"
        diag.Filters.Clear
        diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, *.xlsm"
        
        If diag.Show Then
            For Each item In diag.SelectedItems
                Me.txtFileName = item
            Next
        End If
        
    End Sub
    
    
    Private Sub btnImportSpreadsheet_Click()
    
    
    Dim FSO As New FileSystemObject
        
    If FSO.FileExists(Nz(Me.txtFileName, "")) Then
        ImportExcelSpreadsheet Me.txtFileName, FSO.GetFileName(Me.txtFileName)
    ElseIf Nz(Me.txtFileName, "") = "" Then
        MsgBox "Please select a file!", vbExclamation
    Else
        MsgBox "File not found!", vbExclamation
    End If
        
    End Sub
    
    
    Public Sub ImportExcelSpreadsheet(Filename As String, TableName As String)
    On Error Resume Next
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, TableName, Filename, True
    
    
    If Err.Number = 3125 Then
        If vbOK = MsgBox(Err.Description & vbNewLine & vbNewLine & "Skip column header and continue?", vbExclamation + vbOKCancel, "Error with Excel Column header") Then
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, TableName, Filename, False
            MsgBox "Done", vbInformation
        End If
        Exit Sub
    ElseIf Err.Number <> 0 Then
        MsgBox Err.Number & ":" & Err.Description, vbCritical
        Exit Sub
    End If
    
    
    MsgBox "Upload Complete", vbInformation
    
    
    End Sub
    I have tried but failed quite a bit trying to add this into the code.

    If anyone has an already existed database that imports Excels and deletes tables if they match the excel file names that would be absolutely amazing.

    Below are the screen shots of my excel file name and the database I currently have.

    Click image for larger version. 

Name:	sddds.PNG 
Views:	18 
Size:	13.7 KB 
ID:	42957

    Click image for larger version. 

Name:	wewe.PNG 
Views:	17 
Size:	5.8 KB 
ID:	42958

    Thanks



    Dan

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you shouldnt delete tables, you should empty a table , then load it with more data.

    but to delete a table: currentdb.TableDefs.Delete "tablename"

  3. #3
    deldridge is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    2
    Hi,

    Thank you, Can i ask how you would implement this into the current code? to clear the tble that matches the file name, not all files will be called PRS200 they could be anything really but once the excel has been submitted that excel and table name the would never change.

    Also why wouldnt you delete the table?

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

Similar Threads

  1. Import Excel into Access database
    By njx in forum Access
    Replies: 1
    Last Post: 06-30-2017, 06:54 PM
  2. Form Tweaks
    By Mandody in forum Access
    Replies: 4
    Last Post: 04-24-2015, 01:08 PM
  3. Import data from one Access database to another
    By wvinton in forum Import/Export Data
    Replies: 4
    Last Post: 10-17-2014, 09:21 PM
  4. Import data from PLC to MS Access database
    By lcv99 in forum Import/Export Data
    Replies: 5
    Last Post: 10-11-2012, 01:05 PM
  5. Can I import data from one Access database to another?
    By jimneal in forum Import/Export Data
    Replies: 3
    Last Post: 09-25-2012, 07:22 PM

Tags for this Thread

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