Re: Alpha key to surrogate key

Tech-Archive recommends: Fix windows errors by optimizing your registry

Jim

"Splitting" is really quite simple.

Make a couple copies of your Access database. (keep one as a backup)

In one of those, remove all the tables.

In the other, remove everything BUT the tables.

Go back to the first and tell it where to find its data (i.e., tables) ...
depending on version of Access, you might use Files|Get External Data| Link.
The key word here is "LINK". You don't want copies of the data, you want to
point to where the data is.

That's it!

If you want to post back with questions re: linking (i.e., splitting), try
the .tablesdbdesign newsgroup.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"JIM" <JIM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6DFC0386-789D-458B-856D-907CFF2EDD40@xxxxxxxxxxxxxxxx
Thanks, Jeff. From your reply it sounds like spliting the database would
be
a very critical priority. Do you know a site that I can read up on doing
that?


"Jeff Boyce" wrote:

Jim

I'm not sure I understood before that the actual application lives on the
server.

If that's the case, then every time you start it up, the network has to
pass
all the pieces to your PC to run. The network folks get a bit testy
about
that kind of drain on the system.

Moreover, if you are envisioning multiple folks connecting to (and using)
a
single database file (or a single front-end) over a LAN, you are asking
for
a headache! Not only will that increase the network traffic (those
network
guys again!), but anyone who pulls the plug while using the application
(or
who's PC dies, or who's network card hiccups, or ...) can cause
corruption.

You might want to step back and post a description of your network (LAN?
WAN? Speed? ???) and the application design (?front-end/back-end vs.
all-in-one; properly split with back-end on LAN and copies of front-end
on
each user's PC).

The .tablesdbdesign newsgroup or the .access newsgroup might be a good
place
to post that.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


"JIM" <JIM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C570BA0D-EC09-4E64-ACB2-C7D4B12A0859@xxxxxxxxxxxxxxxx
I'm using Access 2003 with Windows XP Prof which does reside on my
computer
but the database is on a server so that all in the office and field can
use
it. When I say slow I mean sometimes it takes 20 seconds to load this
form.
It doesn't bother me as much as it will bother management, I'm afraid.

Anyway, you've answered my question and that is- should I use a
surrogate
key and would that speed things up? - from your comments, I don't think
so.
And I don't want to go to extra work for nothing. I think I'll have
our
tech
put more RAM on my computer at least. And if the guys in the field
complain
about the speed (on their laptops) I can show them it works great on my
machine. HEE!
Thanks for your input, graetly appreciated

"Jeff Boyce" wrote:

Jim

Access 1.0, 2.0, '97, ...?

What do you mean "loaded from a networked drive"? Does that mean the
Access
application doesn't really live on your PC?

"Painfully slow" is a relative term ... a delay that I find
intolerable
might be "business as usual" for someone else. How long?

Extra RAM almost always helps.

What else (what other applications) is running at the same time?
Which
OS?

Perhaps loading all 450 records PLUS all the related records in the
subforms
is part of the problem. What happens if you make a backup copy of the
form,
delete the subforms, and try the form like that?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"JIM" <JIM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DC84C591-C879-4F52-98DB-C94BF886DC88@xxxxxxxxxxxxxxxx
Hi Jeff, there's only 450 records and it's a local table with no
front-end/back-end yet. This is my slowest loading form but it does
contain
2 subforms. The database is loaded from a networked drive, a
Buffalo
Terastation with lots of space(493 GB free). The development CPU is
a
Pentium(R) 4CPU 2.8 Bhz 2.79 GHZ, 513 MB of RAM. Would it help if
I
had
2
more GB of RAM?
TIA

"Jeff Boyce" wrote:

So, the "form" loads the entire "table"? How many records in the
table?
Using a table local to the application or linked in a
front-end/back-end
design? Over what kind of network? Do you have poor performance
on
any
other forms/tables?

If you don't really need all the fields in the table, use a query
to
return
only the fields you need.

If you don't really need all the records in the table, just one at
a
time,
use a query to return only the record you need.


Regards

Jeff Boyce
Microsoft Office/Accesss MVP

"JIM" <JIM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9FB70725-5380-4DF1-8823-3DB82A13B884@xxxxxxxxxxxxxxxx
the record source is tblCustomer.
TIA

"Jeff Boyce" wrote:

Jim

If your form loads the entire customer master table, rather than
a
single
record, it could take a very long time!

What is the data source for the form?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"JIM" <JIM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6E9ACDB7-0074-4064-BA05-E92CDCC45B75@xxxxxxxxxxxxxxxx
Hi Jeff, whenever a form is loaded, Access takes a long time
to
load
the
form
if the customer master table is in the form. Someone
mentioned
in a
post
that a surrogate key would speed things up although, the forms
still
need
to
see the customers in alphabetic order so maybe the query that
feeds
into
the
form will still take just as much time.--I don't know
Thanks for response JIM

"Jeff Boyce" wrote:

"painfully slow" at what?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"JIM" <JIM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ADF22601-6BD2-4D48-902D-6D74273F3E7A@xxxxxxxxxxxxxxxx
I have a database that I need to convert. Currently it uses
a
40-position
alphatic key on customer name. It's painfully slow and I
know
I
want a
surrogate key. How do I go about converting and adding a
surrogate
key?
Do
I use an append/table creation? If so, how is the key
added?

TIA

















.

Relevant Pages

  • Re: duplicating database on 2nd dataset
    ... Why would your "second" database not need the same tables as ... Microsoft Office/Access MVP ... reports and queries), but insert new underlying tables - where the data is ... "Jeff Boyce" wrote: ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Is this possible??
    ... "Jeff Boyce" wrote: ... all those disparate data sources would have to share common ... Microsoft Office/Access MVP ... a database that they can click on a client's ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Is this possible??
    ... all those disparate data sources would have to share common ... Microsoft Office/Access MVP ... "Jeff Boyce" wrote: ... a database that they can click on a client's ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Copying the last record to a new record
    ... We could append this recordset as mytimestamp) to the database, sort the record source by mytimestamp descand move to the first record - that would get us to a copy of the last record as our new record. ... "Jeff Boyce" wrote: ... you're treating it as if it were a spreadsheet, ... >> Microsoft Office/Access MVP ...
    (microsoft.public.access.macros)
  • Re: Which the best option ?
    ... set up the tables so that there is something in common between the ... You have to tell the database ... "Jeff Boyce" wrote: ... Microsoft Office/Access MVP ...
    (microsoft.public.access.tablesdbdesign)