Strip Away data and copy database

[Denaes]

Hmm :confused: I thought I just posted this as a topic, but it's not here.

 

Is there a way (.net code or otherwise) to make a copy of a database and strip away it's data?

 

I could copy and paste the database and delete records, but I have the TableID's (primary key) set to Autonumber. The clints will need to interact with a few of these table ID's and don't want them in the 1000's.

 

In other words, I need the same structure, but a new database, and I have specific records that need to go into the first few TableID's for some tables.

 

Sure, I'm lazy. I could 'remake' the database again. But this would take some time (possibly introducing new errors into the application) and is feasable once, but I'd prefer an automated method for future uses.

[akiaz]

I do not know much about this, but I am struggling to learn as well. See this:

 

http://support.microsoft.com/default.aspx?scid=kb;EN-US;317881

 

to be able to create an access db in .NET on the fly like you are thinking by using COM interop.

 

HTH

[Joe Mamma]

what database?

 

Access? Making a copy, deleting the data, and then compacting is easy and will reset your auto numbers. . .

 

SQL? generate the SQL schema script and execute it inside a new database. is easy, too

[Denaes]

Access? Making a copy' date=' deleting the data, and then compacting is easy and will reset your auto numbers. . . [/quote']

 

Still annoying to do it by hand.

 

The main point of programming is to replace repeditive simple tasks.

 

But I did not know that once you deleted everything on a table and compacted/repaired it, the autonum would reset.

 

In fact I'm still not sure what exactly compacting/repairing a database does, other than making the database smaller. I do it about once a day before I compress my project to save my days work and knock off between 50k and 600k (off of a database that's normally 300k)

 

Thank you for that knowledge :)

[Nerseus]

Compacting reduces size because Access is using the one database file as a virtual file structure. As records are added and then deleted, their old space is retained in the Database. Updates, I believe, are generally inserts and deletes (how they're handled internally) and so they leave unused space in the file where the "old" records were. A compact looks for these areas and cleans them up. You can think of a "Compact" operation as the same thing as a disk defragmenter - it cleans up unused areas and moves data around so that the "unused" stuff is at the "end" of the file, then truncates it out. More or less :

 

The main point of programming is to replace repeditive simple tasks.

As you said, that's what Programming is - if Access doesn't currently have a "Copy Database minus the Data" function, then you may have spotted a useful programming oportunity :)

 

If you happen to have SQL Server then you can "automate" the copying of the structure by using DTS, which can copy between Access databases. An option of DTS is to only copy structures. If you have a developer license to MSDN then you can install SQL Server tools (Enterprise Manager) to do this for you. As long as you're not managing an Access database used by your company (or you), then you don't need to buy a license to SQL Server to use the tools.

 

-Nerseus

[Joe Mamma]

Compacting reduces size because Access is using the one database file as a virtual file structure. As records are added and then deleted, their old space is retained in the Database. Updates, I believe, are generally inserts and deletes (how they're handled internally) and so they leave unused space in the file where the "old" records were. A compact looks for these areas and cleans them up. You can think of a "Compact" operation as the same thing as a disk defragmenter - it cleans up unused areas and moves data around so that the "unused" stuff is at the "end" of the file, then truncates it out. More or less :

 

 

As you said, that's what Programming is - if Access doesn't currently have a "Copy Database minus the Data" function, then you may have spotted a useful programming oportunity :)

 

If you happen to have SQL Server then you can "automate" the copying of the structure by using DTS, which can copy between Access databases. An option of DTS is to only copy structures. If you have a developer license to MSDN then you can install SQL Server tools (Enterprise Manager) to do this for you. As long as you're not managing an Access database used by your company (or you), then you don't need to buy a license to SQL Server to use the tools.

 

-Nerseus

look into the JRO library