Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Access 2007 - will not overwrite existing table with Excel data


(!)

120417jhj's Avatar
120417jhj 120417jhj is offline
Computer Specs
Member with 20 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
21-Apr-2012, 09:51 PM #1
Access 2007 - will not overwrite existing table with Excel data
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
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
22-Apr-2012, 06:01 AM #2
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.
__________________
OBP
I do not give up easily
120417jhj's Avatar
120417jhj 120417jhj is offline
Computer Specs
Member with 20 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
22-Apr-2012, 06:24 AM #3
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
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
22-Apr-2012, 06:42 AM #4
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.
120417jhj's Avatar
120417jhj 120417jhj is offline
Computer Specs
Member with 20 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
22-Apr-2012, 07:13 AM #5
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
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
22-Apr-2012, 08:56 AM #6
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.
120417jhj's Avatar
120417jhj 120417jhj is offline
Computer Specs
Member with 20 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
22-Apr-2012, 01:23 PM #7
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
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
22-Apr-2012, 02:12 PM #8
Can you email me the database?
120417jhj's Avatar
120417jhj 120417jhj is offline
Computer Specs
Member with 20 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
22-Apr-2012, 02:32 PM #9
Access 2007 overwrite
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
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
23-Apr-2012, 05:48 AM #10
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.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


Tags
access table overwrite

(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑