Copy table without keys and index etc.

[kjharve]

Hi all,

I am new to this database lark - trying hard though. What I want to do is use a spreadsheet to update information in a table. The spreadsheet will contain some new records and some existing records. It may also contain some errors - key violations etc. I'll be using VBA to automate this as there will be about 30 tables and 20 or so spreadsheets.

The way I plan to do this is to copy the destination table as a temporary table and add all of the records contained in the spreadsheet using TransferSpreadsheet. Then I will remove any records that have a newer instance and correct any errors.

If I do this though, the table will reject any record of the spreadsheet that does not meet the criteria and dump to an errors table with the expectation that the errors would be corrected manually and re-uploaded. I'd like to remove this responsibility from the user as best I can though.

I guess what I am asking is: how can I create a copy of a table without copying the rules as well?

Am I making life hard for myself? Is there any easier way of doing this?

Help appreciated!

 

[VicRauch]

Kev,
Your last question, "Am I making life hard for myself?" and "Is there any easier way of doing this?" I would have to say YES to both questions, but I really can't because there is not enough information here, at least for me, to be able to help.
But I do have some questions. What are you really trying to do? Why do you want to use a spreadsheet to update data in a table? Is there something that just does not work about Access forms? Or do you know how to work with Excel, but not Access?
It would be best if you could explain in a little more detail just what you objective is, rather than "What I want to do..." If there is an overwhelming reason you NEED to use Excel, we would take that into consideration when trying to help.
HTH,

 

[kjharve]

Hi Vic,

Thanks for replying.

The reason spreadsheets need to be used is because the data will collated by different departments of the company, not all of these departments have Access available to them. Some of the inputs to the database will be manually collated and others will come from existing systems. Off the top of my head there are 6 different departments providing information but only 1 of those has Access available to them.

That manual collation is the main reason for wanting to validate the records before writing them but the computer systems aren't exactly infallible either.

Due to the volume of data, it will not be feasible to have the database operators manually key the data.

For me, the problem is kind of an "ideal world" situation in that MI reports are produced centrally. The amount of work involved makes me feel that the individual departments should be responsible for their own reporting. Unfortunately the decision does not lie with me, however.