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
    Posts
    6

    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
    Location
    Kentucky
    Posts
    7,568
    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