database design - Append data from an existing mdb into another existing mdb -

-

i'm upgrading existing front-end , back-end set of mdbs. back-end getting bloated , impacting performance. data more 2 years old still needs retained, unlikely ever accessed again. thinking appropriate have active back-end (i.e., current , previous year's worth of data) , archived back-end (i.e., @ beginning of each year, append data active back-end existing, archived back-end).

  • what other approaches should considering?
  • if appropriate approach, how append data active back-end mdb archive back-end, preferably front-end (e.g., start-up function)?
    • the method i've been able identify, go archive back-end, import every table active back-end, append. there way of front-end?

you mentioned bloating. run compact , repair on bloated back-end mdb, if haven't already. in addition shrinking file size, update index statistics can allow query optimizer make better decisions query plans.

you didn't provide details existing databases , tables, made several simplifying assumptions.

  1. your front-end mdb contains links of tables in back-end mdb want archive.
  2. all front-end links links tables. (iow, no links tables don't want archive.
  3. the link names match table names in back-end mdb.
  4. your tables include date field named "date_field". (this field values used determine records archived.)
  5. you have created archive database, c:\db\archive.mdb, includes empty copies of tables want archive. may want convert autonumber fields long integer. if you're not anticipating archived data hardly ever used, drop indexes (which include primary keys, unique constraints, , relationships) ... there considerable space savings if care ... inserting data can faster when don't have indexes update.

so suggest making copy of back-end database, give procedure try. in immediate window check insert , delete statements. if reasonable uncomment 2 currentdb.execute lines , see happens. (you did make backup first, right?)

public sub doarchive()     const cstrarchive string = "c:\db\archive.mdb"     dim db dao.database     dim tdf dao.tabledef     dim strappend string     dim strcutoff string     dim strdelete string     dim strwhere string     dim strmsg string  on error goto errorhandler      set db = currentdb     strcutoff = "#" & year(date) - 1 & "/01/01#"     strwhere = " date_field < " & strcutoff     each tdf in db.tabledefs         if len(tdf.connect) > 0             strappend = "insert [" & tdf.name & "] in '" & _                 cstrarchive & "' select * [" & tdf.name & _                 "]" & strwhere & ";"             debug.print strappend             ''currentdb.execute strappend, dbfailonerror             strdelete = "delete [" & tdf.name & "]" & _                 strwhere & ";"             debug.print strdelete             ''currentdb.execute strdelete, dbfailonerror         end if     next tdf  exithere:     on error goto 0     set tdf = nothing     set db = nothing     exit sub  errorhandler:     strmsg = "error " & err.number & " (" & err.description _         & ") in procedure doarchive"     msgbox strmsg     goto exithere end sub

Comments

Post a Comment

Popular posts from this blog

php - What is the difference between $_SERVER['PATH_INFO'] and $_SERVER['ORIG_PATH_INFO']? -

-
what’s difference between $_server['path_info'] , $_server['orig_path_info'] ? how use them? when run print_r($_server) , path_info , orig_path_info not present in array. why not? how can enable them? i have read php manual on them, still don’t understand them. the path_info variable present if invoke php script this: http://www.example.com/phpinfo.php/hello_there it's /hello_there part after .php script. if don't invoke url that, there won't $_server["path_info"] environment variable. the porig_ prefix uncommon. path_info standard cgi-environment variable, , should never prefixed. did read that? (there issues around php3/php4 if invoked php interpreter via cgi-bin/ - hardly has such setups today.) for reference: http://www.ietf.org/rfc/rfc3875
Read more

fortran - Function return type mismatch -

-
i'm attempting recode old c++ program in fortran make use of lapack (i'm aware c++ have lapack++, i'm having lot of trouble installing it, gave up). i didn't have problems compilation, when had variables declared real . when started coding section of program required lapack, found parameters passed dsyev need double precision . tried change double precision (including changing hard coded numbers double precision counterparts, i.e. 0.0 -> 0.0d0) when try compile, following error of functions , subroutines i've written: error: return type mismatch of function <function> @ (1) (real(4)/real(8)) i'm not sure coming from, in program has been changed double precision. for example, i've declared following: double precision :: alpha(3),d(3),zeta1,zeta2 double precision :: a1(3),a2(3),d1(3),d2(3) double precision :: pi pi = 3.14159265359d0 alpha = (/0.109818d0, 0.405771d0, 2.22766d0/) d = (/0.444635d0, 0.535328d0, 0.154329d0 /) 10 i=1,3 ...
Read more

queue - mq_receive: message too long -

-
i implementing communication between 2 processes using queue. problem when call function mq_receive, error: message long. i have done following: struct mq_attr attr; long size = attr.mq_msgsize; .... // initializing queue "/gateway" int rc = mq_receive(gateway, buffer, size, &prio); if print size value, size=1, while when print same size program (got same mechanism), not long integer ( -1217186280 )... how can solve error?....so while size = 1, believe it's right "message long" why 1? p.s. have tried put : int rc = mq_receive(gateway, buffer, sizeof(buffer), &prio); but no result. it seems need read docs more carefully. when call mq_receive should pass size of destination buffer. this size must greater mq_msgsize attribute of queue . in addition, seems have error in queue attributes initialisation makes proper mq_receive call impossible. here standard message queue session: fill mq_attr struct ( doc ): struct mq_a...
Read more