Moving Info

[DS]

I was helped with this awhile back but of course circumstances changed
so here I am again.

I need to move Info from one Database to another. The first Database
will be split Back End - Front End. I was told to link the tables
that I am backing up to between the 2 databases. Is this still true
even though I will now have a split Database?

Thanks
DS


Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim strSQL As String

On Error GoTo ProcErr
Set ws = DBEngine(0)
ws.BeginTrans

strSQL = "INSERT INTO SalesHistory SELECT * FROM tblChecks"
db.Execute strSQL, dbFailOnError
strSQL = "INSERT INTO SalesDetailsHistory SELECT * FROM
tblCheckDetails"
db.Execute strSQL, dbFailOnError

ws.CommitTrans

Proc_Exit:
On Error Resume Next
Set db = Nothing
Set ws = Nothing
Exit Sub

ProcErr:
ws.Rollback
MsgBox Err.Number & vbCrLf & Err.Description
Resume Proc_Exit

 

[DS]

DS wrote:
I tried simplfying the code a bit but I get an error that it can find
the input table or query,,,,,any suggestions helpful.
Thanks
DS

Dim db As Database
Dim HISTORYSQL As String

Set db = OpenDatabase("C:\ProServ\DB\History.mdb")
HISTORYSQL = "INSERT INTO tblHistoryChecks SELECT * FROM tblChecks"
db.Execute HISTORYSQL, dbFailOnError

db.Close
Set db = Nothing

 

[John Vinson]

I was helped with this awhile back but of course circumstances changed
so here I am again.

I need to move Info from one Database to another. The first Database
will be split Back End - Front End. I was told to link the tables
that I am backing up to between the 2 databases. Is this still true
even though I will now have a split Database?

You'ld link to the other backend, because that's where the tables are.

Backups should GENERALLY be done by backing up the entire .mdb file
(the backend, of course - that's where the data resides!) using
Windows file copying or backup software. Is there some specific reason
that you want to create (possibly redundant, possibly incomplete)
backups of individual tables?

John W. Vinson[MVP]

 

[John Vinson]

DS wrote:
I tried simplfying the code a bit but I get an error that it can find
the input table or query,,,,,any suggestions helpful.
Thanks
DS

Dim db As Database
Dim HISTORYSQL As String

Set db = OpenDatabase("C:\ProServ\DB\History.mdb")
HISTORYSQL = "INSERT INTO tblHistoryChecks SELECT * FROM tblChecks"
db.Execute HISTORYSQL, dbFailOnError

If you have History.mdb defined as db, this query will fail because
(presumably) there IS no table named tblChecks in History.mdb.

I'd use TransferDatabase to LINK tblHistoryChecks as a linked table in
your main database (don't know what it's named), and then run your
append query into the linked table.

But see my concerns in the other response in this thread!

John W. Vinson[MVP]

 

[DS]

You'ld link to the other backend, because that's where the tables are.

Backups should GENERALLY be done by backing up the entire .mdb file
(the backend, of course - that's where the data resides!) using
Windows file copying or backup software. Is there some specific reason
that you want to create (possibly redundant, possibly incomplete)
backups of individual tables?

John W. Vinson[MVP]

Well the reason is that I can have anywhere from 100 to 10,000 entries
in the tblChecks and tblCheckDetails tables on any given day, so in
order not to overload the database with many records I want to move just
those two table over to another database so I start with an empty
tblChecks and tblCheckDetails everyday...for speed. Then when I run my
reports I want to pull just the records I need into a tblrptChecks and
tblrptCheckDetails so I can run my report and then delete. By the way
how do you back-up just the back-end? This is all new water to me!
Thanks
DS

 

[John Vinson]

Well the reason is that I can have anywhere from 100 to 10,000 entries
in the tblChecks and tblCheckDetails tables on any given day, so in
order not to overload the database with many records I want to move just
those two table over to another database so I start with an empty
tblChecks and tblCheckDetails everyday...for speed.

:-{(

You should - MUST!!! - compact the database or even recreate it from
scratch then. Deleting 10,000 records (about 0.1% of the capacity of a
well designed Access database) will cause bloating and loss of
efficiency unless you compact regularly.

Then when I run my
reports I want to pull just the records I need into a tblrptChecks and
tblrptCheckDetails so I can run my report and then delete. By the way
how do you back-up just the back-end? This is all new water to me!

THe backend is *just a file*. If you use the database splitter wizard
on a database named MyStuff.mdb, it will be named MyStuff_BE.mdb.

You can find where the _BE database is located; make sure it's closed,
nobody using it; and just use Windows Explorer to copy it to another
drive, a thumb drive, a CD or DVD, whatever.

Archiving your table to an archive table doesn't solve your problem,
it just moves it - the archive database will be the one to grow to the
2 GByte limit.

Any chance you could store this data on SQL/Server, if you're talking
about tens of millions of records over the course of the project?

John W. Vinson[MVP]

 

[DS]

:-{(

You should - MUST!!! - compact the database or even recreate it from
scratch then. Deleting 10,000 records (about 0.1% of the capacity of a
well designed Access database) will cause bloating and loss of
efficiency unless you compact regularly.




THe backend is *just a file*. If you use the database splitter wizard
on a database named MyStuff.mdb, it will be named MyStuff_BE.mdb.

You can find where the _BE database is located; make sure it's closed,
nobody using it; and just use Windows Explorer to copy it to another
drive, a thumb drive, a CD or DVD, whatever.

Archiving your table to an archive table doesn't solve your problem,
it just moves it - the archive database will be the one to grow to the
2 GByte limit.

Any chance you could store this data on SQL/Server, if you're talking
about tens of millions of records over the course of the project?

John W. Vinson[MVP]

Thanks John, Quite a lot of Good Info! The Archive Table will be
emptied once a year and then stored in other Database's by year. Since
at the end of a year it's pretty much dead info anyways...Once again
Thank You!!!!
DS