Saving Data and Creating a new Database from an already split DB

[Randy]

I'm an Intermediate (at best) user of Access 2003. I'm trying to determine
the best way to resize - for lack of a better word - a shared (Workgroup)
database. The database in question has already been split and currently the
backend database is upwards of 300 MB. Is it possible to further split the
database or archive historical content? Any guidance would be appreciated.
Thanks - Randy

 

[a a r o n . k e m p f]

move to SQL Server

nothing you can do in MS Access to make 300mb of data work correctly

 

[a a r o n _ k e m p f]

yah archive tables to me-- sound like a giant pain in the ass.

better to keep your data IN ONE PLACE
and use a database engine that 'JUST WORKS' even if your data gets
bigger.

Developing on SQL Server Express; using it that way for a year and
then upgrading to a workgroup edition license (when you've got 20
similiar applications) is a much better way to do things-- than have
12 different partitions of data for the same table.

-Aaron

 

[Tom Wickerath]

Hey Aaron,

nothing you can do in MS Access to make 300mb of data work correctly

Wrong again, as usual.

While I have your attention, and since you've stated in the past how you
have a love affair with DAPs (data access pages, for anyone else reading this
message that doesn't know the abbreviation), why don't you reply to this
user, who has posted a reply to an old thread, specifically to a post you
made previously? Perhaps you haven't seen it yet, so here it is:

Subject: Re: Access table onto webpage
7/11/2008 8:44 AM PST
By: Michael
In: microsoft.public.access

http://www.microsoft.com/office/com...cess&mid=5f804c4a-6da3-4805-94e3-df32cbb3cbf9


It's your chance to prove to the world just how much you really know about
the technologies you pontificate about.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[So Sorry For Poor Aaron]

yah archive tables to me-- sound like a giant pain in the ass.

After your adventures in custody for cyberstalking and threatening a public
servant, who would know better about "giant pain in the ass" than you? Big
Bruce, Big Bubba, and Big Barney, we hear, just can hardly wait for you to
come back. Once they heard how you're behaving, they sent word "Keep it up,
sweetie-pie, you're getting closer and closer."

better to keep your data IN ONE PLACE
and use a database engine that 'JUST WORKS' even if your data gets
bigger.

The space between "sweetie's" ears must be filled with oatmeal mush for him
not to know that any Server DB, especially the free ones, has to have a lot
of tender, loving care. Big Bruce, Big Bubba, and Big Barney don't care that
SQL Server needs a DBA, but YOU should. Competent DBAs don't come cheap,
whether you hire them or contract them -- say, maybe that's "sweetie's" goal,
for you to get in such a mess that you'll have to come back and hire HIM.

 

[So Sorry For Poor Aaron]

move to SQL Server

nothing you can do in MS Access to make
300mb of data work correctly

Sweetie's had his head where the sun don't shine, hasn't he? Lots of people
have Access databases much larger than a mere 300MB that work, not only
correctly, but with good performance. Access MDBs have a 2GB capacity.
That's a long way from where this DB is.

 

[Larry Linson]

Wow guys - I was just trying to help the guy. Can't we be nice!

Bonnie

Some members of this community have obviously come to the conclusion that
being nice to Mr. Kempf is fruitless and have resorted to not-so-nice means
to warn people away from his posts that they deem to be misleading. Perhaps
if his posts were generally civil, informative, and constructive instead of
generally being insulting, uninformative, and destructive, those others
responding "in kind" might be deemed to be "civil, informative, and
constructive," as well.

Larry Linson
Microsoft Office Access MVP

 

[Larry Linson]

I'm an Intermediate (at best) user of Access 2003. I'm
trying to determine the best way to resize - for lack of a
better word - a shared (Workgroup) database. The
database in question has already been split and currently
the backend database is upwards of 300 MB. Is it
possible to further split the database or archive historical
content? Any guidance would be appreciated.

What led you to believe that changing the overal size of the database will
give you a discernable improvement in performance? I believe you can find
posts here that indicate that it is immaterial. You might improve
performance by moving your archives to a separate table -- and, as that is
what would have to be done if you set up another "archival database" might
mislead you into thinking that re-splitting the BE had helped.

Have you visited MVP Tony Toews' site? He has a treasure trove of good
information on performance and avoiding corruption in the multiuser
environment at http://www.granite.ab.ca/accsmstr.htm. There are other sites
with additional information on the subject in the Resources list at
http://sp.ntpcug.org/accesssig/default.aspx.

Larry Linson
Microsoft Office Access MVP

 

[aaron.kempf is a robot]

CAUTION!
It has been determined that the message below was generated
programmatically, by a program robot ("bot") which also allows manual
intervention. This robot was created by a person or persons deliberately
trying to interfere with users of the Microsoft Access database software, and
uses the name of an actual person. As it is not possible to block posting by
this "bot", we post these cautions lest you be misled into taking the posts
as authentic and serious.

 

[aaron.kempf is a robot]

CAUTION!
It has been determined that the message below was generated
programmatically, by a program robot ("bot") which also allows manual
intervention. This robot was created by a person or persons deliberately
trying to interfere with users of the Microsoft Access database software, and
uses the name of an actual person. As it is not possible to block posting by
this "bot", we post these cautions lest you be misled into taking the posts
as authentic and serious.

 

[Tom Wickerath]

Hi Bonnie,

Aaron has quite a reputation in the microsoft.public.access newsgroups for
abusing others. You can read more about it here, if you are interested:

http://www.tonytoews.com/disruptions2007.htm

I was the victim of his cyberstalking last year, which included threats of
burning my home down, and shooting me. Now, poor Mr. Kempf, has the audacity
to make the following claim:

"I wasn't convicted of anything other than some kid whining to the right
pig."

and
"every judge I talked to-- agreed that they never had probable cause."

Source: See messages # 14 and # 1
http://groups.google.com/group/microsoft.public.access/browse_frm/thread/923fa357a3983e5c

He is living in a fantasy land. I was present for most of Aaron's court
appearances in Seattle District Court. I can tell you that he was Mr. Ashamed
in front of the judge. He certainly was not his boastful self that you can
see in several newsgroup postings.

I continue to try to treat Aaron with respect, even though he really doesn't
deserve it.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tony Toews [MVP]]

I'm an Intermediate (at best) user of Access 2003. I'm trying to determine
the best way to resize - for lack of a better word - a shared (Workgroup)
database. The database in question has already been split and currently the
backend database is upwards of 300 MB. Is it possible to further split the
database or archive historical content? Any guidance would be appreciated.

I'm with Larry. Are you experiencing performance problems? If not
then keep on as you are.

BTW do you have graphics or other embedded objects in the tables?
How many records in some of those tables?

I'm not a fan of archiving data because it's a pain in the ***. If
folks want historical data then you have to point the reports to the
historical data. And comparing historical data to current data is a
pain.

However you may have very valid business reasons to upsize to SQL
Server. Your big concern is how mission critical is the data and can
the data be rekeyed if you lose a day.

Mission critical means can you afford to lose an hour if the database
is down? Frequently the data can't be rekeyed. A classic example
being a call centre where you are receiving incoming calls.

Losing a day means that if you have to restore from backup do your
users have the paperwork in place so they can rekey the data? Are
there enough staff to re-enter that data?

I recall a posting by someone working for a large casino/hotel
operation in the mid to late 80s stating to what lengths they spend
over a million dollars duplicating their IBM S/38 mini-computer in
another offsite location with data inserts and updates being copied
from the main system to the backup system in under a second.

Thier attitude was that they could never afford to lose a room
reservation. Imagine the mess if they lost a days worth of phone
calls. <shudder> And the newspaper stories by the upset clients.
And expenses while they placate the customers so they don't go to the
newspapers. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tom Wickerath]

I think I treat Aaron with respect pretty much most (all) of the time. My
earlier reply to him, which read "Wrong again, as usual", was short and to
the point, to let anyone else (such as you) know that he had given incorrect
advise. So, yes, I do personally try to rise above it.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________