pasting in new fields and data

[sha7jpm]

is there a way of automating a method to paste in data from an imported table?

I know all about paste append queries etc and make table queries, but the idea is that the database has one main table and with each new survey the data is pasted into the one table with variables added.

please dont reply with "why not have all the data in seperate tables linked by ID number!" I know I know, but the client really insists on a large table of all the data for him to peruse through.

not at all annoying to have a database client who does not understand databases and is unwilling to flexible at all!

still serve him right when he has a table of over 50 to 60 columns, with all the field headings practically the same.!!

any tips?

ta

John

 

[The_Doc_Man]

First, this client needs education. Second, you need education on exactly how to educate your client.

Tell your client just how much it is going to cost to make Access do something that is totally unreasonable. Clients can usually accept less than what they first wanted if they find out that the price is FAR MORE than what they first expected. Like the old USA adage, "Money talks, bull-dinkey walks." (Well, I cleaned that one up a bit for public consumption.)

As to unreasonable customers: Tell me you have not already committed to a fixed-price, fixed-objective contract with massive penalty clauses for non-performance within schedule, and for which the behavior you describe is explicitly within the scope of the fixed objective. If you have, I regret that I have no advice other than to consider which costs more - doing the impossible for a fixed fee or backing away from the impossible and invoking a penalty clause.

OK, enough on the business issues. Now, the techie stuff.

To see how to add new fields to a table, I suggest you read up on the following help topics:

TableDefs collection (as a member of a database)
TableDef object
Fields collection (as a member of TableDef)
Field object (as an element of a TableDef)
CreateField method of a TableDef
Plus, of course, look at the properties of a field to see what you can set once the field has been created.

Don't get confused by the fact that you can see more properties for fields than exist for a table. This is because fields also exist for QueryDefs. Some field properties only apply to the query case.

Also consult these topics to find out how far the program will let you go:

In AC97 and earlier: Limits (table)
In AC2K and later: Specifications (table)

You can do what you want through VBA code until you exceed the maximum size of a record or limit of fields in a single table, whichever comes first.

 

[sha7jpm]

many thanks!

cheers for the tips,

thanks for the new education policy for me to try!!

I'lll give it a go.

ta

John