Results 1 to 7 of 7
  1. #1
    fuecheefang is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2015
    Posts
    43

    Linked tables from excel..can i make local copies for forms then export back...Help???


    Still pretty new to Access, using Access 2010 I am trying to link some tables from Excel into Access. User are familiar with Excel and like the interface and would rather use Excel but need the Query capability of Access. Problem is Access will not allow users to update tables, forms, etc once linked in access. Is there a method where I can create a local copy while still having the excel tables linked. From there I can create forms from the local copy and then export back to excel to update the tables? This has been racking my brain for almost a month now.

  2. #2
    RAshA.pro777 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    41
    But access allow the updates to tables through forms associated with it, however you can write a macro to export data to excel and also can import it to access

  3. #3
    fuecheefang is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2015
    Posts
    43
    Quote Originally Posted by RAshA.pro777 View Post
    But access allow the updates to tables through forms associated with it, however you can write a macro to export data to excel and also can import it to access
    I have tried to create forms from the linked tables, it states "Recordset not Updateable" and I cannot enter in any values. Im not familiar with coding and macros, can you please describe in more details for me Rasha?

  4. #4
    RAshA.pro777 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    41
    There are many reasons why your data may not be updateable.
    *The query is a Totals query (uses GROUP BY) or Crosstab query (uses TRANSFORM), so the records aren't individual records
    *The field is a calculated field, so it can't be edited
    *You don't have permissions/rights to edit the table or database
    The query uses VBA functions or user defined functions and the database isn't enabled (trusted) to allow code to run

  5. #5
    RAshA.pro777 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    41
    Hmmm, may be
    The table that you are updating is a linked table that does not have a primary key or a unique index. Or, the query or the form is based on a linked table that does not have a primary key or a unique index.

  6. #6
    fuecheefang is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2015
    Posts
    43
    Quote Originally Posted by RAshA.pro777 View Post
    Hmmm, may be
    The table that you are updating is a linked table that does not have a primary key or a unique index. Or, the query or the form is based on a linked table that does not have a primary key or a unique index.
    Yes the tables are linked from Excel with no PK. And forms created from these linked tables will not be useable because you are not able to enter data or do any updates. Thats why i am asking if there is a way to convert these linked tables into a local table and then export back to excel?

  7. #7
    RAshA.pro777 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    41
    Ok, know you should to create a PK for your excel tables the PK should be unique and the same record in each table should have the same PK code, the easiest way is create a unique sequence for all records In tables.

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

Similar Threads

  1. Replies: 12
    Last Post: 06-19-2015, 10:02 AM
  2. Export linked tables statically (no password prompt)
    By DaveC in forum Import/Export Data
    Replies: 3
    Last Post: 09-24-2014, 02:30 PM
  3. Replies: 7
    Last Post: 04-25-2013, 03:47 PM
  4. Query Linked Back End Tables
    By AKoval in forum Queries
    Replies: 1
    Last Post: 02-15-2013, 09:14 PM
  5. Database Linked Even If I Make Copies
    By netchie in forum Access
    Replies: 4
    Last Post: 07-22-2011, 11:52 AM

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