Results 1 to 2 of 2

Need help with table replace module for FE database

  1. #1
    aSHLEYBECK is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019

    Unhappy Need help with table replace module for FE database

    I'm a terrible self-taught access administrator.

    I developed a DB that works off imported data from an excel worksheet that is actively wrote to - so it cannot be linked otherwise it has caused issues with the Excel table auto-write/save.

    To circumvent this I used the code below - which works beautifully - until I split the DB into a front-end and a back-end and now I'm royally hooped with a deadline. Aye aye aye.

    If anyone can offer any guidance I'd be extremely grateful.

    Option Compare Database

    Public Sub UpdatePDFs()
    Dim strSql As String
    Dim n As Integer
    Dim db As DAO.Database
    Set db = CurrentDb

    'Update SARF Table
    DoCmd.RunSQL "DELETE tblSARFSubmissions.* FROM tblSARFSubmissions;"
    DoCmd.TransferSpreadsheet acImport, , "tblSARFSubmissions", "xyzfilepath\exceldoc.xlsm", True, Table1
    DoCmd.SetWarnings True
    For n = db.TableDefs.Count - 1 To 0 Step -1
    ' loop through all tables
    If InStr(1, db.TableDefs(n).Name, "ImportError") > 0 Then
    ' if table is import errors table
    DoCmd.DeleteObject acTable, db.TableDefs(n).Name
    ' delete table
    End If
    Next n

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Even with the Db split, it should still import...tho, what is Table1 in the command?:
    DoCmd.TransferSpreadsheet acImport, , "tblSARFSubmissions", "xyzfilepath\exceldoc.xlsm", True, Table1

    it should be the name of the sheet but in quotes, and the filepath should be the full server UNC path:
    DoCmd.TransferSpreadsheet acImport, xlVersion , "tblSARFSubmissions", "\\server\folder\exceldoc.xlsm", True, "Table1"

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

Similar Threads

  1. Replies: 13
    Last Post: 03-22-2019, 04:09 PM
  2. Replies: 5
    Last Post: 08-26-2018, 07:54 PM
  3. Replies: 9
    Last Post: 03-16-2013, 11:10 AM
  4. Replies: 1
    Last Post: 01-16-2013, 12:40 AM
  5. Replies: 3
    Last Post: 06-07-2012, 07:05 AM

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
Tech Forums: Microsoft Office Forums