Importing Data Question

[dubczech]

So I'm creating a new db from scratch (basically transitioning a massive excel sheet into access).

It is a Property Database. with Site #s, addresses and more standard fields like those.

I've got a couple things working correctly, but there is one field that is only maintained in the excel sheet (Site Status).

There is one table set up in the db called "Site List" where all of teh properties are located. The second table is called "Site Details". The Proprty Number Field has a 1-to-1 relationship between these two tables (With Enforced Referential Integrity, Cascade Update Related Fields and Cascade Delete Related Fields all checked).

So what I want to do is have every Property # that exists in "Site List" table exist on "Site Details". Then I want to import the data for the field "Site Status" into the "Site Details".

My thoughts were to create an append query based off of tblSiteList to push all of the Property Numbers to tblSiteDetails and then import the data. Teh Append query works fine.

When I try to import the data, (there are 1012 records in the table, but only 940 in the excel sheet), into tblSiteDetails, it gives the error "765 records were lost due to key violations". When I open up the table, it has no data in the Site Status field.

Any help would be wonderful.

 

[JamesMcS]

Well, key violations just mean that you're trying to add duplicate values into whichever field you've set as your primary key. If it's the site status field that's the key, I would imagine that it would only ever contain one of a few values - so that would be the problem.

Try removing the key from the site status field.

 

[JamesMcS]

Also, I've seen the problem where you're importing more records into an Access table than exist on the Excel spreadsheet. Go to the first blank row on the bottom of your spreadsheet, select the whole row and press ctrl+shift+down to select all the rows (down to 65535 or whatever it is). Right click, delete, save and bob's your uncle. (This also gets rid of the problem in Excel where the vertical scroll bar doesn't stop at the bottom of your table - not a bug, Excel just seems to remember the longest your table's been and sets the scroll bar accrodingly)

 

[dubczech]

Nope. That didn't work.

 

[JamesMcS]

Ah - so which fields in the target table are primaries?

 

[dubczech]

I dont' have primary keys set up for either table.

 

[JamesMcS]

Strange, I always thought that you had to have primary keys to be able to set up referential integrity, and the key violation error definitely says "this table has a primary key". So, if you go into the design view of your table, none of the fields have a little key to their left?

 

[dubczech]

Correct. I think you can still have a relationship set up without a primary key.

 

[JamesMcS]

You can, it's the referential integrity bit that I thought required PKs. Can you import the spreadsheet into a new table without problems, using the wizard? If you can't that leaves me stumped, I've never seen key violations when importing to a table without a primary key. Sorry....

 

[dubczech]

Can anyone else assist?