Separating the database

[Gary]

I have a backend Database with just one main table in it (no form/queries
etc)
The network pc's have the front end database (with all the forms,
queries,macros etc). These front ends are linked to the table belonging to
the backend database.
Records have been entered since January this year. However, as soon as the
size of the backend database exceeded 1 gigabyte I got concerned that it
would possibly encounter a corruption of the data at some stage soon as
Access apparently "max's out" at 2 gig.
So I simply moved almost half the records and placed it into a seperate new
database - calling it ArchiveJan-Apr 05.
People on the network will still need to retrieve records in this archived
database. This seems like it will be a fixed procedure from now on...
creating new databases every couple of months to relieve the size of the
main backend database.
I don't want to be creating new front ends for all pc's everytime I create a
new archived database.
I know I could simply (in each existing front end) create a linked table to
the table of a newly created archived database but I don't know which macro
command to create.
But instead of opening the archived databases seperately, ideally a user
would press a command button from their existing front end database and then
it would access the appropriate linked table to the respective archived
database table(if that makes sense).
Or, is there a better solution ?
thanks for any assistance.

[BillCo]

Re: Separating the database

1 gig is massive for any database - but for one with just one table
it's a little crazy. You very much need to find out what's causing
this.
I presume you have been compacting the backend reqularly?
how many records are in this table, and how many fields?

As for how to set up front end for accessing archive databases, you can
include a function in the front end that will re-point the linked
table to another table in another database, allowing the user access
old archives without a second, and third etc front end. I can send you
the code if you need it - but you probably don't. I think a closer look
at whats causing the file size is more important.

[lesperancer@natpro.com]

Re: Separating the database

how many users created 1Gb of data in six months ?
are you sure a 'compact' of the database isn't all that is required ?

[Justin Hoffman]

Re: Separating the database


"Gary" <g45340@hotmail .com> wrote in message
news:42ca645c$0 $20029$afc38c87 @news.optusnet. com.au...[color=blue]
>I have a backend Database with just one main table in it (no form/queries
> etc)
> The network pc's have the front end database (with all the forms,
> queries,macros etc). These front ends are linked to the table belonging
> to
> the backend database.
> Records have been entered since January this year. However, as soon as the
> size of the backend database exceeded 1 gigabyte I got concerned that it
> would possibly encounter a corruption of the data at some stage soon as
> Access apparently "max's out" at 2 gig.
> So I simply moved almost half the records and placed it into a seperate
> new
> database - calling it ArchiveJan-Apr 05.
> People on the network will still need to retrieve records in this archived
> database. This seems like it will be a fixed procedure from now on...
> creating new databases every couple of months to relieve the size of the
> main backend database.
> I don't want to be creating new front ends for all pc's everytime I create
> a
> new archived database.
> I know I could simply (in each existing front end) create a linked table
> to
> the table of a newly created archived database but I don't know which
> macro
> command to create.
> But instead of opening the archived databases seperately, ideally a user
> would press a command button from their existing front end database and
> then
> it would access the appropriate linked table to the respective archived
> database table(if that makes sense).
> Or, is there a better solution ?
> thanks for any assistance.[/color]



This does seem like a very rapid rate of growth. What happens to the size
of the back end if it is compacted? Are you storing anything unusual like
ole objects, pictures, etc within the database?
Anyway, if you really do have to archive copies then you could add a table
of archives giving, say, the file path, file description, date, etc so the
front end has a startup form which shows a listbox or subform showing all
available backends. The code to re-link backends must have been posted many
many times before so I all I will add is to say that in my experience
deleting the links and re-creating from scratch is quicker than trying to
refresh the links.

[BillCo]

Re: Separating the database

Justin - not if you keep a connection to the target datbase open. When
you link your first table open a recordset to it and keep it open until
the rest have been re-linked. I have a 200+ table set up that can be
entirely re-linked over a fairly slow network in under 16 secs doing
this... just a thought :)

[Keith]

Re: Separating the database

"Justin Hoffman" <j@b.com> wrote in message
news:dadqaq$pcv $1@nwrdmz02.dmz .ncs.ea.ibs-infra.bt.com...[color=blue]
>
> Anyway, if you really do have to archive copies then you could add a table
> of archives giving, say, the file path, file description, date, etc so the
> front end has a startup form which shows a listbox or subform showing all
> available backends.[/color]

Another approach might be to just have linked tables from the archives to
the FE, with a button on the main form to select the datasource.

Regards,
Keith.

https://www.keithwilby.com

[Gary]

Re: Separating the database

Thanks for all the advice.
I did neglect to point out that each record allows users to link 4 pictures
(OLE objects) where the pics are stored in a seperate network folder.
There are approx 250 records. And yes, I do compact the databases every day.

"Gary" <g45340@hotmail .com> wrote in message
news:42ca645c$0 $20029$afc38c87 @news.optusnet. com.au...[color=blue]
> I have a backend Database with just one main table in it (no form/queries
> etc)
> The network pc's have the front end database (with all the forms,
> queries,macros etc). These front ends are linked to the table belonging[/color]
to[color=blue]
> the backend database.
> Records have been entered since January this year. However, as soon as the
> size of the backend database exceeded 1 gigabyte I got concerned that it
> would possibly encounter a corruption of the data at some stage soon as
> Access apparently "max's out" at 2 gig.
> So I simply moved almost half the records and placed it into a seperate[/color]
new[color=blue]
> database - calling it ArchiveJan-Apr 05.
> People on the network will still need to retrieve records in this archived
> database. This seems like it will be a fixed procedure from now on...
> creating new databases every couple of months to relieve the size of the
> main backend database.
> I don't want to be creating new front ends for all pc's everytime I create[/color]
a[color=blue]
> new archived database.
> I know I could simply (in each existing front end) create a linked table[/color]
to[color=blue]
> the table of a newly created archived database but I don't know which[/color]
macro[color=blue]
> command to create.
> But instead of opening the archived databases seperately, ideally a user
> would press a command button from their existing front end database and[/color]
then[color=blue]
> it would access the appropriate linked table to the respective archived
> database table(if that makes sense).
> Or, is there a better solution ?
> thanks for any assistance.
>
>[/color]

[Justin Hoffman]

Re: Separating the database

"BillCo" <coleman.bill@g mail.com> wrote in message
news:1120565020 .400884.247480@ g14g2000cwa.goo glegroups.com.. .[color=blue]
> Justin - not if you keep a connection to the target datbase open. When
> you link your first table open a recordset to it and keep it open until
> the rest have been re-linked. I have a 200+ table set up that can be
> entirely re-linked over a fairly slow network in under 16 secs doing
> this... just a thought :)[/color]


Are you saying for this particular database you have compared the two
techniques of refreshing the links versus re-creating the links? If so, how
long did the delete/re-create routine take?
I have found a number of things work better by deleting the links - speed is
one thing and I also have vague memories of odbc links causing trouble where
the back end table structure changes or field data types change and certain
issues were not resolved until I deleted and re-created the links - although
I don't remember precise details.

[Justin Hoffman]

Re: Separating the database

"Keith" <keith.wilby@ba eAWAYWITHITsyst ems.com> wrote in message
news:42ca777b$1 _1@glkas0286.gr eenlnk.net...[color=blue]
> "Justin Hoffman" <j@b.com> wrote in message
> news:dadqaq$pcv $1@nwrdmz02.dmz .ncs.ea.ibs-infra.bt.com...[color=green]
>>
>> Anyway, if you really do have to archive copies then you could add a
>> table of archives giving, say, the file path, file description, date, etc
>> so the front end has a startup form which shows a listbox or subform
>> showing all available backends.[/color]
>
> Another approach might be to just have linked tables from the archives to
> the FE, with a button on the main form to select the datasource.
>
> Regards,
> Keith.
> www.keithwilby.com[/color]



Might well be easier, but the extra table idea allows you to present a
nicely ordered list with user-friendly names for the files such as 'Archive:
Jan 2005 - Jun 2005' instead of '0105-0605.mdb'. You could see at a glance
what you were connected to and you would provide only a limited set of
backends to connect to, so that your average user would not 'browse off into
outer space' looking for a back end to connect to. It would be more work to
maintain, but more user-friendly.
Perhaps a middle-ground approach might be to give the back-ends descriptive
file names, and have the listbox get a list of all files in a folder.

[BillCo]

Re: Separating the database

Justin - not if you keep a connection to the target datbase open. When
you link your first table open a recordset to it and keep it open until
the rest have been re-linked. I have a 200+ table set up that can be
entirely re-linked over a fairly slow network in under 16 secs doing
this... just a thought :)

[Arno R]

Re: Separating the database


"Justin Hoffman" <j@b.com> schreef in bericht news:dadvl8$kk8 $1@nwrdmz03.dmz .ncs.ea.ibs-infra.bt.com...[color=blue]
> I have found a number of things work better by deleting the links - speed is
> one thing and I also have vague memories of odbc links causing trouble where
> the back end table structure changes or field data types change and certain
> issues were not resolved until I deleted and re-created the links - although
> I don't remember precise details.
> [/color]

I agree with you Justin.
I *always* delete and recreate the links since I had very strange issues when I only refreshed the links.
And this was not only with odbc, but also just with FE-BE-links between mdb's.
The link did not represent 'all' of the changed table.
I also don't remember precise details but it was weird!
(maybe we try to forget these nasty details ;-) )

From the moment I discovered this I changed the relink-code in *all* my databases.
I will *always* delete and recreate links I promised myself!

Arno R

[Br@dley]

Re: Separating the database

Gary <g45340@hotmail .com> wrote:[color=blue]
> Thanks for all the advice.
> I did neglect to point out that each record allows users to link 4
> pictures (OLE objects) where the pics are stored in a seperate
> network folder.
> There are approx 250 records. And yes, I do compact the databases
> every day.[/color]

But are you storing the pictures in the DB as OLE objects? All you need
to do is store the network path to the image file and then get Access to
display the image on a form/report. Storing a copy as an OLE object is a
massive waste. If you only store the text path I suspect your DB will be
tiny.
[color=blue]
> "Gary" <g45340@hotmail .com> wrote in message
> news:42ca645c$0 $20029$afc38c87 @news.optusnet. com.au...[color=green]
>> I have a backend Database with just one main table in it (no
>> form/queries etc)
>> The network pc's have the front end database (with all the forms,
>> queries,macros etc). These front ends are linked to the table
>> belonging to the backend database.
>> Records have been entered since January this year. However, as soon
>> as the size of the backend database exceeded 1 gigabyte I got
>> concerned that it would possibly encounter a corruption of the data
>> at some stage soon as Access apparently "max's out" at 2 gig.
>> So I simply moved almost half the records and placed it into a
>> seperate new database - calling it ArchiveJan-Apr 05.
>> People on the network will still need to retrieve records in this
>> archived database. This seems like it will be a fixed procedure from
>> now on... creating new databases every couple of months to relieve
>> the size of the main backend database.
>> I don't want to be creating new front ends for all pc's everytime I
>> create a new archived database.
>> I know I could simply (in each existing front end) create a linked
>> table to the table of a newly created archived database but I don't
>> know which macro command to create.
>> But instead of opening the archived databases seperately, ideally a
>> user would press a command button from their existing front end
>> database and then it would access the appropriate linked table to
>> the respective archived database table(if that makes sense).
>> Or, is there a better solution ?
>> thanks for any assistance.[/color][/color]

--
regards,

Bradley

A Christian Response

http://www.pastornet.net.au/response

[BillCo]

Re: Separating the database

> Are you saying for this particular database you have compared the two[color=blue]
> techniques of refreshing the links versus re-creating the links? If so, how
> long did the delete/re-create routine take?
> I have found a number of things work better by deleting the links - speed is
> one thing and I also have vague memories of odbc links causing trouble where
> the back end table structure changes or field data types change and certain
> issues were not resolved until I deleted and re-created the links - although
> I don't remember precise details.[/color]

sorry for not getting back to you sooner. yes - i have a backend with
around 200 tables which is archived regularly. accessing the archive is
done in code in two ways from the front end:
Firstly deleting the linked tables in the front end and re-creting them
in code - this take 10 - 15 mins to complete.
Secondly, by looping through the tables and re-pointing the links and
refreshing them. By doing it this way it takes approx 15 seconds. The
key to making it fast is to open a recordset to the destination backend
before the routine and release it when all the tables are re-linked.
This code snippet should give you what you need:

function SetLinks(byval strDestDatabase as string)

Dim db As DAO.Database
Dim td As DAO.TableDef
Set db = CurrentDb

'set recordset to destination backend here

'qryLinks is a query on MSysObjects returning all linked tables
Set rstLinks = db.OpenRecordse t("qryLinks")

Do While Not rstLinks.EOF
Set td = db.TableDefs(rs tLinks!Name)
td.Connect = ";DATABASE= " & strDestDatabase & ";TABLE=" &
rstLinks!Name
td.RefreshLink
rstLinks.MoveNe xt
Loop

'realease recordset to destination backend here
rst.close: set rst = nothing
db.close: set db = nothing

End Function

I've never had any problems with this or seen any weird side effects.

hope this helps,
Bill