1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2007 - will not overwrite existing table with Excel data

Discussion in 'Business Applications' started by 120417jhj, Apr 21, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. 120417jhj

    120417jhj Thread Starter

    Joined:
    Apr 17, 2012
    Messages:
    20
    I am working on an Access DB written by someone much cleverer than me who has use a lot of SQL which I find hard to interpret. It is a DB of client info and order info. I exported the Client Table to Excel so that I could more easily sort out the data (separate name fields into Title/First Name/Last Name, for example) and then I wanted to import the cleaned data back into the Access DB.

    I renamed the existing table as Client Original and I successfully imported the new table giving it the samename as the original used to have so that there wouldn't be any issues involving queries etc which relied on it unbeknown to me.

    I needed to add a field from the Client Table to an Order Form but - to my surprise - the only table available to me was the first one which I had renamed as Original; the new one which had the old name wasn't seen by the Add Fields box - it is as if it doesn't exist (but it does!!)

    I then tried to import into the Client Original table instead, but get the message that it cannot overwrite this table when I get to the end of the wizard. There are no relationships at all involving the Client table (and when I show the Client Original table on the relationships screen it's name is given with _1 at the end of it which isn't in the name I gave it .) I am thinking that the guy who wrote the DB has something hidden in the SQL somewhere which is stopping me from being able to overwrite this table.

    Has anyone got any ideas please?

    Many thanks, JHJ
     
  2. Sponsor

  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,066
    When you changed the name of the original table Access cleverly realised what you had done and changed all the references to the new name. So I would suggest that you copy and paste the original table with another name as a backup.
    Then change the name of your new table to Client Original, Access will tell you that a table with that name already exists and do you want to overwrite it, say yes.
    Providing your new table has the same key ID field data it should make the change easily. But any queries/forms/reports that use the original table will have to have the fields added in Excel to them.
     
  4. 120417jhj

    120417jhj Thread Starter

    Joined:
    Apr 17, 2012
    Messages:
    20
    Hello again! I'll try what you suggest, however things have moved on a bit now. I've discovered that there is a relationship between Clients Original and the Orders table - it uses the Client ID (which is the Primary Key in the Clients Original table). I made careful note of how the relationship was working and then deleted it; I was then allowed to import my cleaned up data from Excel (Hooray) Not hooray, as it happens since when I tried to re-create the relationship with my Clients table it gives me Error 3368 saying field numbers or types are different and, furthermore, it won't let me recreate the relationship which existed previously with Clients Original!!!! Oh joy!
    JHJ
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,066
    I don't understand why it will not allow you to recreate the original relationship.
    If your original table had a Key ID field it was probably an Autonumber field, your field from Excel would not be an Autonumber field and you can't make it one because there are already values in it.
    So under those circumstances I would re-create the relationship to the original table.
    Create new fields in it for the Title/First Name/Last Name and then Update those fields from the Excel table using an Update query.
     
  6. 120417jhj

    120417jhj Thread Starter

    Joined:
    Apr 17, 2012
    Messages:
    20
    I have created a backup for the Client Original and successfully deleted and recreated the relationship - no problem. I then imported the Excel cleaned data into the backup and I'm back to square one with the Error 3368 message. I the Client Original table has the same number of fields as the Excel s/s - I added two further address fields to the s/s but had matched the table prior to importing. All field names match and the Key ID numbers are also identical as I have tried choosing both 'no primary key' and 'own primary key' when importing - doesn't seem to make any difference to the outcome.

    Could the clever guy who wrote this have built in some SQL somewhere which forbids importing data into this table? (There's a Dynamic Financial query which deletes any fields I add after I've run it so he's made this delete changes somehow - wondering if there's something similar going on here.)

    The problem is that on the Filtered Invoice Report he has created a field using Order Contact and Comments and he has a field called OrderContact in the Order Table which is Contacts in the Clients Table. OrderContact is being used to feed the Filtered Invoice Report so there's 2 sets of client contact info knocking about rather than just one set (!??)

    JHJ
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,066
    I doubt if there is anything special going on. You just need to understand how to update the table, you can'y just use the whole table, just the fields that you have added/changed.
    Create a Select Query based on your original table using the new or changed fields.
    Add the imported table to the query and join the tables via the ContactID field.
    See what records you get.
     
  8. 120417jhj

    120417jhj Thread Starter

    Joined:
    Apr 17, 2012
    Messages:
    20
    Hello again OBP. I went away to read up on doing Update Queries. Did the job and it will not let me update the Clients Original Table - I know I'm doing it right because I managed to overwrite my Client Table (which is the one containing the updated info from Excel) with the data from the Client Original Table. It won't work for me the other way round though; it's as if the Client Original Table is locked in some way. I'm feeling a bit hysterical. Have you any thoughts on why this is not allowing me to update it? JHJ
     
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,066
    Can you email me the database?
     
  10. 120417jhj

    120417jhj Thread Starter

    Joined:
    Apr 17, 2012
    Messages:
    20
    I have clicked the wrong thin in my eMail and have unsubscribed from this thread by mistake! Do you know how I re-subscribe to it? (Your responses don't show up on screen for me unless I click on the eMail link.) I don't know how to eMail it to you I'm afraid. Am attaching the one you returned to me the other day. It has no records in it apart from my silly one but the tables are all there.

    JHJ
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,066
    I will give you my email address via private mail and you can send it to me. You should be able be able to re-subscribe using the controls just below the last post on this thread.
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1050303