Alpha key to surrogate key

  • Thread starter Thread starter JIM
  • Start date Start date
J

JIM

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
 
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
 
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
 
the record source is tblCustomer.
TIA

Jeff Boyce said:
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
Click to expand...
 
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
 
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
 
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
 
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
 
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
 
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?
 
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
 
Thanks, Jeff

Jeff Boyce said:
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
Click to expand...
 
Back
Top