Copy table data from one Access DB to another

[CraigBest]

Hi Folks

I have an Access database I'm using for my application that contains about 15 tables, mostly fairly static data. But three tables are fairly dynamic and contain data that will change from one 'session' to the next.

I plan to create a separate 'Session' access DB that I can copy and then write the contents of the three tables to, and then also allow that data to be copied back into the main program to let users recall previous sessions. I'm using OLEDB for data handling.

I will have two connection objects, one for the static and working session data and another for the session backup DB. Within the program I'm using a series of data tables (no datasets) to store the data, and I'm wondering if there is a way to to take the data from a data table and copy it as a whole to the second database? The second Session DB will have blank copies of the three tables in it already with the same format.

If there is a better way to do this I'm all ears, this was what I came up with from my limited experience with Access as a database. Thanks for any help you can provide!



CraigHartz

 

[jebenson]

If the static data doesn't take up too much space (i.e., if saving just the session data makes up most of the database's size), I would just use system.io.file.copy and make a file copy of the database for the backup. You could even have a table in the main database with the path/filename of any backups so you could have more than one.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[CraigBest]

Hi JE

Well, the complete DB is about 16-20 mb, and that seems like a lot to make a copy of every time a new session gets created. Then again these days that's a drop in the bucket in terms of storage memory, isn't it?

I did find a reference as follows:

INSERT INTO <tablename1> SELECT * FROM <dbpath>.<tablename2>

which looks promising but I'm not sure how to set it up to use two different connection objects (necessary to connect to two different databases, right?)

Any ideas on this?

Thanks


CraigHartz

 

[jebenson]

You don't need to do two connection objects, you just need one connection to the destination (backup) database. This code should do it:

Dim conn As OleDbConnection
Dim cmd As OleDbCommand
Dim SQLStr As String = ""

conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DestinationDB.mdb;Persist Security Info=False;")

'clear any existing data from the destination table
SQLStr = "Delete from DestinationTable"

conn.Open()

cmd = New OleDbCommand(SQLStr, conn)
cmd.ExecuteNonQuery()

SQLStr = "Insert Into DestinationTable Select * from SourceTable IN 'C:\SourceDatabase.mdb'"
cmd = New OleDbCommand(SQLStr, conn)
cmd.ExecuteNonQuery()

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!