N
nms
hello,
i'd like to know the best practice for a utility i want to include in
my recent project. the requirements is like this,
There is a database (access or SQL Server 2005) we use as back-end. now
i do have some transaction table where i get 50-100+ records per
second.
1) this leads to tremendous growth of size of the MDB (access database
file) after some days.
and some time we need to do compact and repair immediately to resume
operations.
2) also we don't want to allow user to see transactions that are 1
month old. (for some reason) and for that reason i need to move all
records to some other table.
Here we may also opt for another field (yes/no) called "Archived" that
will help to decide archived status of records. BUT
In case ms-access is being used as back-end, i'd like move records in
some other database.
just to keep main db small in size.
vs2005 is being used for development in asp.net
now this is what i've been trying...
a) used "append query" to move records from one database "source.mdb"
to "archive.mdb" by,
i. INSERT INTO table_name IN '" & MapPath("db/archived.mdb") & "'
SELECT * from table_name
WHERE dtDateTime <= # var_date #"
ii. DELETE from table_name WHERE dtDateTime <= # var_date #"
--> i observed that after each operation both database grows in size.
and some times i need to compact and repair 'em.
b) i copy records one by one, using two adodb.recordsset.
--> this is not worth while in case large data. that i have. takes too
much time. ALSO db size increases a bit.
so what i want is the way to move records from one database to another.
connection strings are available in web.config for both.
i hope there will be way to cope up with this, regardless whatever is
being used as back-end.
p.s. here this post is worth to see BUT it's for mdb to sql server
only.
http://groups.google.co.in/group/mi...=gst&q=bulk+copy+data&rnum=1#5163a62c4d6eaf40
thanks in advance.
i'd like to know the best practice for a utility i want to include in
my recent project. the requirements is like this,
There is a database (access or SQL Server 2005) we use as back-end. now
i do have some transaction table where i get 50-100+ records per
second.
1) this leads to tremendous growth of size of the MDB (access database
file) after some days.
and some time we need to do compact and repair immediately to resume
operations.
2) also we don't want to allow user to see transactions that are 1
month old. (for some reason) and for that reason i need to move all
records to some other table.
Here we may also opt for another field (yes/no) called "Archived" that
will help to decide archived status of records. BUT
In case ms-access is being used as back-end, i'd like move records in
some other database.
just to keep main db small in size.
vs2005 is being used for development in asp.net
now this is what i've been trying...
a) used "append query" to move records from one database "source.mdb"
to "archive.mdb" by,
i. INSERT INTO table_name IN '" & MapPath("db/archived.mdb") & "'
SELECT * from table_name
WHERE dtDateTime <= # var_date #"
ii. DELETE from table_name WHERE dtDateTime <= # var_date #"
--> i observed that after each operation both database grows in size.
and some times i need to compact and repair 'em.
b) i copy records one by one, using two adodb.recordsset.
--> this is not worth while in case large data. that i have. takes too
much time. ALSO db size increases a bit.
so what i want is the way to move records from one database to another.
connection strings are available in web.config for both.
i hope there will be way to cope up with this, regardless whatever is
being used as back-end.
p.s. here this post is worth to see BUT it's for mdb to sql server
only.
http://groups.google.co.in/group/mi...=gst&q=bulk+copy+data&rnum=1#5163a62c4d6eaf40
thanks in advance.