Results 1 to 5 of 5
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    update backend from command button


    Can I update the backend table from the frontend with a command button on a form? I have a linked table to the backend. I want to rename the current linked table and replace with an imported table. This is my current code to do this in the front end:

    Code:
    DoCmd.Rename "tblMstr" & Format(Date, "yyyymmdd"), acTable, " tblMstr "
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
        " tblMstr ", "C:\Users\folder\Desktop\wfolder\ Mstr.xls", True
    Because it’s a linked table; I would like to code it in the frontend to modify the backend if possible.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Did you try? What happened?

    You want to rename the table that is in the backend and break the link? Then import into the backend with the original table name?

    Why would you want to do this? Is the table structure the same (same fields)? Why not just delete all records and import all new records?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    The code works with the frontend; but I don’t know how to apply it to the backend unless I code it in the backend. The requester of the database wants to keep a snapshot of the table at a particular time. This may change in the future.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Seems I experienced that changing name breaks link and can't just replace the table with another of the same name. Possibly will need code that deletes and recreates the link in the frontend.

    I have code that creates table in backend and sets link in frontend:

    Set cn = New ADODB.Connection
    'connect to the backend database
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & gstrBasePath & "Data\LabData.accdb'"
    'create the test table
    cn.Execute "CREATE TABLE " & Me.tbxTestNum & " (LabNum text(12) PRIMARY KEY Not Null, method text(30) Not Null);"

    'set table link
    Set tdf = CurrentDb.CreateTableDef(Me.tbxTestNum)
    tdf.SourceTableName = Me.tbxTestNum
    tdf.Connect = "; DATABASE=" & gstrBasePath & "Data\LabData.accdb"
    CurrentDb.TableDefs.Append tdf

    Suggest you rename the table, create new table (maybe a copy structure only), delete and recreate link, import into the link in frontend - or something like that process.

    Bing: Access VBA copy table backend
    http://social.msdn.microsoft.com/For...orum=accessdev
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    ok. I will try this. thanks.

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

Similar Threads

  1. Replies: 13
    Last Post: 12-17-2012, 07:52 PM
  2. Replies: 3
    Last Post: 12-13-2012, 01:51 PM
  3. Replies: 1
    Last Post: 12-03-2012, 02:50 PM
  4. Replies: 12
    Last Post: 03-14-2012, 10:54 AM
  5. Replies: 1
    Last Post: 07-27-2010, 02:27 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