CopyObject - structure only

[AKMonkeyboy]

Anyone have an idea on how to use DoCmd.CopyObject so it copies only the structure?

 

[RobCPA]

I don't believe that you can run the CopyObject without copying the data. Following is a workaround that I use:

Create a make table query including all of the fields within the table with the criteria of Is Null in a field that always has data in it. Then run the query. It will give a message that 0 records are pasted, but will then create the empty table.

Rob

 

[AKMonkeyboy]

Thanks Rob, the only problem with your suggestion is this: If I add a field to the original table, the query would need updated too - otherwise the new field won't be "copied" using the query (unless I edited the query - and I don't want that much overhead). You did give me an idea though, what if I used the CopyObject command, and created a query to delete any records in the newly created table (VBA code could do this as well - I'll have to dig that code up though). That way any new fields will be exported to the new table, and all data will be erased. Wonder why they didn't add a portion to DoCmd.CopyObject that allows for structure only?

Later

 

[RobCPA]

Actually, my method would take care of the any new fields added to the structure, you would just need to construct the query as follows:

1. Pull down all of the fields using the *
For example: the first field would be Tablename.*
2. Put the next field with a criteria of null, but make sure that you unclick the show. You'll have to do this or there will be an error "Can't define field more than once".
3. Run the query.

You could delete the records in the newly created table, but if there are a lot of records, this can take quite a bit of time.

I've thought of writing a VBA function that would do this. You're right, seems like it should be an option in the CopyObject command.

Rob