A SQL Statement

[Y2J]

Hi Everybody,

I am writing an application in VB with an Access database, in which information is grouped together by project numbers. When a project is completed I need a way of archiving all the information for that project into a separate database. The way that I was thinking about doing it was having a template database already set up, and then when I need to archive the data I would do a SELECT and then an INSERT statement, and then copy the template database to a different name. My problem is that I’m not sure how to do the INSERT statement from one database to another. If anyone knows how to do this, or a better way it would be greatly appreciated.

Thanks in advance.

[rcallan]

You can actually use the INSERT INTO statement to copy records into another database:

INSERT INTO [TableName] IN [ExternalDatabase] (Field1, field2 etc..) SELECT FROM [SourceTable]

I hope this makes sense.

[Y2J]

Thanks, that does make sense, but is it possible to to use the '*' wildcard instead of specifying the field names (field1, field2)?

[rcallan]

Yes you can but the source and destination table columns must match exactly (column names and data types) so if it is just a replica of the source database you should have no problem. There is another way, which is using synchronisation but this method is a little slower.

[Y2J]

Thanks, it seems to be working good now, but I still have to check the integerity of database some more.