Importing Data Question? 1

[Cordury]

I have a database that has information that is static (Client #, Address, Name, etc) but every week a file is created to be imported into the database that has both static and information that is updated, such as construction dates.

Is it possible to import this info into a database without saving over or replacing the static data. I.e. Client X may be on the import file for 2 months until the construction is complete. What will happen if I have Client X already in the database and try and import the exact same records for it down the road? Redundant records?

Thanks

 

[GingerR]

in your 'static' existing table, you can set indexes or keys and the data will not be overwritten.

so if you only ever want one combination of Client + Date, then make those two fields the PRIMARY KEY (in the table design).
if the combo of CLIENT + DATE + PROJECT is what is unique, then make those three fields the PRIMARY KEY. and so on.....

then you can never add any records with that same primary key (however many fields you make it). even with an append query, at first it will say "I'm gonna add 1000 records" and you hit OK but then it says "I can't add 500 records cause they violate primary keys" or something like that. goody, you think, cause you only wanted the 500 new records anyhow.
ok?

 

[Cordury]

Ginger,
You mention "indexes or keys". I was not aware that you could have multiple primary keys?

 

[Cordury]

So If I want the Client #, Address, name fields to remain static, I change their indexes to "Yes (No duplicates)"? This way when data is imported next week, the only info added to the DB will be project and date info?

 

[GingerR]

no.
in the table design, you pick those three fields, then hit the KEY button.
what this will do tho is never import any data that has an existing combination of that information in it's row. so if you already have existing data:

ClientID Address Name ProjectStart ProjectEnd
1233 123 Main St. Ginger 5/1/03 5/8/03

you will never import again ANY data that has 1233 + 123 Main St. + Ginger, no matter what the rest of it is.

i'm wondering if this is not what you are looking for? the best thing to do is to make a temp/test table, then import in or create a second table (test import table) and append away--figuring out what you have to do to get the results you want. if you are still stuck, please put a real example of the data you have, the data you want to import,and what final results you are looking for. thanks--g

 

[Cordury]

I guess the real problem is that I am pulling the Store (which does not change)Info (Address, City, State, etc) from a query to be viewed through a form.

Behind the scenes I would like to be able to import store data (when construction is to begin, end, etc- these dates change on a regualr basis) into the form on a weekly basis without redundant address data.

Which only leads me to another problem because there are more text boxes on my form than the query (record source) can handle.

It is a nightmare.

Sorry about the confusion. I will try your suggestion to see if I can get some more ideas.
Thank you,
Cord

 

[Cordury]

Ginger,
I did what you suggested; I seperated 100 records into two tables. The first table has 50 records (tblImport1). This table also has the Store ID, Address, City, State as primary keys.

The 2nd table (tblImport2) contains all 100 records including the 50 in tblImport1.

When I imported tblImport 2 (the one with all 100 records) into tblImport1, I get the following error:

Microsoft Access was unable to append all the data to the table.

The contents of fields in zero records were deleted, and 48 records were lost due to key viloations.

*if records you lost, either the records you lost contain primary key values that already exist in the destination table, or they violate integrity rules.

Is it safe to assume that those records lost were the Store IDs, Addresses, Cities, and states that were already in my tblImport1?
Thanks,
Cord

 

[GingerR]

does the resulting table look as you expect?

 

[Cordury]

At first glance it looks ok but I will have to drill down on the data to make sure.

But I plan on importing data every week, I guess I am going to receive that error every week.

Thanks for your time, I appreciate it.

 

[GingerR]

you can turn off the error messages like this: it's just making it pretty...

what are you importing from? another access db? an excel file?

if it's excel: then make a new form.
put a button on it, cancel the wizard.
in the button's OnClick event, (if excel) put (you can follow the prompts when you type in docmd.transferspreadsheet)

 docmd.setwarnings FALSE 'this gets rid of the error messages
docmd.transferspreadsheet acImport,acspreadsheettypeexcel9,"TableNameInAccess","pathToExcelFile",TRUE
docmd.setwarnings TRUE

let me know if it's a text file i'll show you how to do that.

 

[Cordury]

Good Morning Ginger,
As of now the data will be imported from Excel. I will try and make the new form today and use the code you provided. I will let you know how it turns out.

Thanks,
Cord