Database scripts

[dvongrad]

I'm of the understanding that in order to reset identity values in Access 2003/2007, I must delete the relationships for primary/foreign keys, delete the primary key columns in each table, re-add them, then restore relationships. I'm assuming this must be all manually, but please correct me if any of these assumptions are wrong.

What I would really like to do is generate a script to drop all the relationships and tables and recreate everything. The reason I need this is that I'm doing a data load (multiple times for testing purposes), but simply deleting the data and then recreating the data through a C# application (that takes data from a single table Access database and imports it into 60+ table database complete with foreign key relationships) does NOT reset the identity values on each table to 1, but rather uses the next number in the sequence since the tables are not actually dropped first. This is fine for testing purposes, but when my ASP.NET application goes into production, the identity values for all tables MUST start at 1.

Is this at all possible in Access to create a script that drops everything in the current schema (relationships, tables, etc.) and then recreates the schema without the data exactly as it was before? Or is there some utility somewhere that will do this for me? Preferrably free but I would still pay for something if it save me hours or days of manual scripting. For the few tables I have that have hardcoded lookup data, I can easily export the data and re-import it after the schema is recreated as a completely blank database.

Any throughts or suggestions on this are greatly appreciated as this is an urgent matter in the next week or so. If you need further clarification on exactly what I need, please don't hesitate to ask. TIA

[NTC]

well - your description of what you think you need to do is baffling.....but the general answers are this; Access has total VBA customization ability - in the sense that data is in tables or in unbound fields in forms - you can write vb to your hearts content to manipulate things....(plus sql of course)

another general item; one can change data in fields in a record with a relatively simple Update Query...except for the Primary Key data. But keep in mind that the table's formal primary key field (from Access' perspective) doesn't have to be the field that is the reference "primary key" to your other tables. It is not uncommon to have a technical autonumber field as the formal primary key but also to have a second field, which is set to Indexed/NoDuplicates and have values entered here (often with alpha characters) - that are the relationship values cross referencing to other tables.

A third point is that you are not obligated to formally set the relationships between tables. You can set them on-the-fly during query/form design and not set them permanently in the relationships area.

I hope this helps - I tend to think when I see overly complex issues as you have described that there is a misunderstanding and a simpler solution is possibly...though in your case I am not sure.....