ADO.NET and MS Access - Identity and Foreign key types

  • Thread starter Thread starter Richard Lee
  • Start date Start date
R

Richard Lee

Hi,

I'm working with VS.NET 2003 and Access (all variants).
I've designed the Access DB first, and I have PKeys in
Access defined as Replication ID types. FKeys in the DB
are defined as Text(38) to correpsond with the actual
values the DB auto-enters. (BTW I've also tried defining
FKeys as Long Integer)

Problem is that using the VS .NET DataSet wizard, and
defining relationships in the dataset causes an
error "Parent and Child columns don't have type-matching
columns". Both types show in the designer as
simple 'string', although looking at the Server Explorer
shows the PKey type is a aGUID, and the Fkey type is
adWChar.

Question is: can I use the dataset wizard to help me
here, & if so what types should I use in Access? (I need
Replication Ids on the PKeys) Otherwise can anybody point
me to any resources that provide type info about the
various Access data types so I can be sure to do the
right casts in my code? Yeugh!
PS I know I should be using SQL Server :), but my clients
don't have the capability. What advice on using MSDE
instead to better support multiple users? I've been
unable to find any decent MSDE resources, so I'm nervous
about making a move.

Thanks in advance
Big Rich
 
You are probably adding a layer of unneeded complexity by making your
PK's replication ID's. For the full scoop on that subject, go to
Michael Kaplan's web site, www.trigeminal.com. He's the world's
foremost authority on Jet replication (he wrote the conflict resolver
for several versions of Access).

Supporting multiple users is more a function of how efficiently you
write your application than it is of the engine. In some ways, MSDE
can be more limiting than Jet since there's a performance throttle
with 5 concurrent batches.

-- Mary
MCW Technologies
http://www.mcwtech.com
 
Hi Mary,
thanks for the comments.

I'm flummoxed. I've looked at the site you suggested, but
he assumes a level of Access knowledge that I don't have,
and some of his workarounds for the replication problems
use Access code capabilities - i.e. if I'm just using the
Jet bit with a bespoke front end via ODBC (ADO.NET) then
I don't have that opportunity. He also discusses
something called an 's_Guid' that isn't even mentioned in
the Access 2003 help, whereas Access 2003 does recommend
using Replication ID as the PK for replication??? I'm
sure respect is due to Kaplan, and I'm missing the
point...

The key (no pun intended) question arises: How do I
ensure globally unique records in a Jet db?

As I understand it, a record is identified by its PK.
This can be a single column or a composite (e.g. an
autonumber col and a site code). Record uniqueness
therefore depends on the PK including a guid.

In Jet the built-in type is a Replication ID, but it
seems that the guys who built it, and the replication
services, omitted to ensure that the representation of
the type was coherent at both the Jet and the Access
levels (let alone in MFC/ADO/DAO), or that replication
actually works if the Replication Id column is the PK
column.

All I can see as an option is a dumb Jet db where I
handle guids in code, using the db simply to store them
as, say strings. I've then got to write the code to
synchronise dbs myself. Nice job.

Yours confused...
Big Rich
 
The Jet replication id is desgined to be used for replicated Jet
databases. You haven't indicated that yours is replicated. Even in
replicated databases, you aren't required to use a Guid as the PK. If
it's not replicated, then you don't need a Guid as a PK. You haven't
made a convincing case WHY you need a Guid as a PK -- if the issues of
dealing with Guids are over your head technically, why not use
something simple like an Autonumber or even a long integer that you
generate yourself? If you're working with Jet, you can't be managing
so much data that you're going to run short of autonumbers. Besides
the complexity of dealing with Guids in code, there is the issue of
the Guid PK being too wide since the database engine must maintain an
index on the field. Therefore, the more overhead, the slower inserts
are going to be when the index is rebuilt. You want to make the PK as
narrow as possible, staying away from strings unless the amount of
data you are dealing with is small. You might want to pick up a good
book on database design -- Mike Hernandez wrote a good one for
non-academic types, "Database Design for Mere Mortals".

-- Mary
MCW Technologies
http://www.mcwtech.com
 
Back
Top