Copy table without keys and index etc.

kjharve

Board Regular
Joined
Jan 4, 2006
Messages
206
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!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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

Board Regular
Joined
Jan 4, 2006
Messages
206
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.
 

Forum statistics

Threads
1,136,515
Messages
5,676,302
Members
419,619
Latest member
jalme

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top