How2: Transfer Table info from one MDB to anther MDB?

[Mr. B]

In my current application, I read 5 tables that are in a single MS dB file.
What I find is that there is about a 2 second hit per each table read when my
application loads.

What I'd like to do is to create another MDB file and transfer all the tables
information into ONE (1) table in the New file. Thus I can read the
information faster. It IS a pain when my application starts as there is other
info I read... so if I can shave off some start time, that'd be great!.

The existng data MDB file gets updated almost daily. So I'd end up running a
scheduled task each night to 'Purge' my new file of all info before I update
it... This is to get ride of any obsolete info that is in it.

Any idea on how to accomplish this? I've never done this much less, done
anything other than read/write to a MDB with my application.

Regards,

Bruce

 

[Guest]

One solution would be to write a vb app that contains some sql in a timer event. The timers interval property would be set so that it ran once every 24 hours. The sql within the timer event would contain a series of "Insert Statements' that would read from the 5 tables into one table.
This is one out of a possible number of different solutions.

 

[Miha Markic [MVP C#]]

Hi,

You won't read data significantly faster in that way.
How much data are we talking about?
And why do you read all the data?

 

[Val Mazur]

Hi,

You could use SELECT INTO statement using some sort of similar code

Use a "SELECT INTO" sql command as follows:


Dim oConn As ADODB.Connection
Dim sSQL As String

Set oConn = New ADODB.Connection
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\Backup.mdb;"

sSQL = "SELECT * INTO Products_Backup FROM "
sSQL = sSQL &
"[odbc;DSN=northwind;UID=myUsername;PWD=myPassword;].Products"
oConn.Execute sSQL, , adExecuteNoRecords