Results 1 to 6 of 6

How to link Access to Excel and update both ways?

  1. #1
    JennyL is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    28

    How to link Access to Excel and update both ways?

    Hi all,



    I am new to this forum hoping someone can help me with my Access issue. I am currently building a Setup Profile database which will be used by about 10 users. I am planning to split the database after everything is done. My problem now is that some users work from home on some days and we have to access through VPN. I do not want users to access the database through VPN because it will most likely crash. I am trying to come up with a work around for these users when they are working from home. I created excel with connection to Microsoft Query (Access Database) and it works great for users to read and research the data when they cannot access the database. However, if a user needs to update the information, the change does not feed back to Access Database. Any recommendations on what I need to build so that users can use Database at work, but use either excel or another application to add or edit the information and feed back to access when they need to work from home through VPN?

    Thank you in advance!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    4,978
    I work with access via VPN. It works fine. no crashes.
    you can work in excel, which access can read as a table,
    you cannot work in Access to update live excel. you must export access to excel.

  3. #3
    JennyL is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    28
    Thanks for the response Ranman256. Not sure how my VPN is setup but whenever we try to open the access, it will always crash that's why I am so afraid of letting users using it when they work from home. I am looking for a workaround as backup option. I started connecting access to sharepoint but then it removed the relationship among my tables so that's not going to work.

  4. #4
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,342
    VPN: a split database must have both the front & back files on the LAN. The front file is NOT on the remote user's PC. The remote user connects to their front end file remotely. Each remote user must have their own front end - they cannot share.

    I remote into my client's application frequently and this works fine.

  5. #5
    rdougherty is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    27
    just make sure front-end db is not very bulky, and tables indexed properly. And, as NTC states, do not allow users to copy and paste their own versions of the front end. The trouble with that is, users will DEFINITELY create their own shortcuts to the front-end, and accidentally copy the file rather than creating a shortcut as intended. Heavily training them NOT to do this and placing some helpful hints in appropirate places, is likely all you can do...

    EDIT: also, ideally, one is not running complex queries on large tables remotely...

    Also also, I have not researched whether this still occurs in recent versions, but Access 97 and XP both (IIRC) had issues when a user's IP address was not a "ten dot"... (could be your crash issue?)

  6. #6
    Micron is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    2,416
    accidentally copy the file rather than creating a shortcut as intended
    It wasn't a VPN situation, but to prevent this in other db's of mine, the startup routine checked that the FE's path was authentic. If not, entry wasn't permitted. In some situations this could be only a partial path if that was sufficient.
    - "doesn't work" is no help.Error #s/text do. State what's happening.
    - Use code tags for code/sql; show where errors occur
    Make all suggested changes in copies of your database or to its objects

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

Similar Threads

  1. Replies: 7
    Last Post: 04-16-2014, 06:07 AM
  2. Link to Excel; number of records change and Excel can't update
    By crobaseball in forum Import/Export Data
    Replies: 5
    Last Post: 03-22-2014, 10:40 PM
  3. Link excel to access
    By medaccess in forum Programming
    Replies: 7
    Last Post: 04-28-2013, 06:03 PM
  4. One Way Link From Access to Excel
    By dargo72 in forum Import/Export Data
    Replies: 1
    Last Post: 12-06-2012, 12:31 PM
  5. link between excel and ms-access
    By Bala Preetha in forum Access
    Replies: 1
    Last Post: 11-28-2010, 07:23 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
  •  
Tech Forums: Microsoft Office Forums