Problem with "Lookup Wizard" and Tables with Decimal-type Primary Keys?

[C. M.]

Hi,

I do not know if this is the right place to post this, but.. I seem to be having a problem with setting up a table in a database. In particular, I am trying to use the Lookup Wizard to create a relationship between two tables where the Primary Key is a 12-digit precision, 0-scale Decimal number type. Everything else works fine -- The Lookup Wizard works just fine with Integer and Text type PK's, but it gives two errors when I try to do link a Decimal type index:

"An error occurred in the Field Builder.

You've replaced or modified the default Field Builder, and Microsoft Office Access is unable to run the new version. Reinstall Microsoft Office Access to correct this error."

Followed by:

"Microsoft Office Access couldn't launch the Lookup Wizard, or this wizard has been disabled.

Rerun the Microsoft Office Access or Microsoft Office Setup program, click Add/Remove, and select the Wizards check box. To reenable this wizard, click About Microsoft Office Access on the Help menu, and then click the Disabled Items button to view the list of addins which you can enable."

As I stated before, the Lookup Wizard works fine for Integer and Text fields; this error seems to only occur for Decimal fields. (I did not try any field types other then Decimal, Integer or Text...) I also did all the steps the error dialogs suggested, to no avail.

Anyone have any suggestions? Is this maybe a bug?


Thank you.

 

[Nick Coe \(UK\)]

It's generally thought to be best to use long integers for
keys. I would never use a decimal so have never run across
this.

If you need the parent to be unique on the decimal value
then simply make it a unique index and add a seperate
autonumber, unique, long integer field for your primay key
which you then migrate to the child as a foreign key.

--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.mrcomputersltd.com/ Repairs Upgrades

In C. M. typed:

 

[C. M.]

Ooops, I realized after I logged out for the day that I did not give versions.. I'm using Windows XP/SP2 Home Edition, with Office 2003 Pro/Home Use.

Nick Coe wrote to use a Long Integer/Autonumber and avoid the issue all-together.. Unfortunately, that is not an easy option in my case, as the ID numbers are 12 digit coded numbers, there may be too many records, exceeding the limits of the Long Integer Autonumbers. Also, some of the applications which interact and manipulate the data expect those 12 digit ID numbers, I have no way to modify the source code to change this.

A Long Integer in Access is basically a signed 32-bit integer, limited to just over 2 billion, which only allows ten digits, up to those limits. Of course, Access is not the best tool to use for this, either. I would much rather use C++ or Perl and MySQL on a Linux-based machine. But I am constrained to use Access, the 12 digit ID system, and the other software and tools already in use. I do not have a choice in the matter.

Besides, if it is a bug, it needs to be fixed, not merely worked around. That is like saying if I bought a car that turned out to have a mechanical problem in the basic design that made it not able to exceed 25MPH, and then being told "Sorry, I guess you'll have to stay on streets that only go up to 25MPH."

Finally.. It is only the using the Lookup Wizard that seems to exhibit this problem. I can manually enter the lookup options and query string by hand, and manually create the relationship in the relationship editor. I can also use the query builder from the lookup property tab, but I still have to manually enter all the details and other settings. So my immediate problem is solved. That leaves the question(s) of "Is this a bug, should/how do I report it, and should MS fix it?" And if it is not a bug but my own fault, what am I doing/setting wrong?

And those were really my original questions, anyhow.

 

[John Vinson]

the ID numbers are 12 digit coded numbers

Could you consider using a twelve-character Text field, just as one
would do for any other "number" which isn't really a number but a
unique identifier?

This would avoid the frank bugs in the implementation of the Decimal
datatype.

John W. Vinson[MVP]

 

[Nick Coe \(UK\)]

I don't think you're doing anything wrong, you've a hell of
a lot of constraints on you but that's the way things are
sometimes.

Not keen on your analogy with a car but I take your point.

I don't use the lookup wizard, just do everything manually
so I've never run into that problem. You could certainly
write up the problem, create an example application (a
simple one) and submit it to MSFT as a bug or problem. It
is after all always up to them what priority they assign to
resolving such things.

IIRC the wizard code itself is buried in various mde, mdt
files dotted around the office directory structure - if you
feel sufficiently masochistic you could try examining it
yourself to see what's going on, but I wouldn't use time
doing that at the moment.

Sorry I couldn't offer more apposite help originally.

--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.mrcomputersltd.com/ Repairs Upgrades

In C. M. typed:

 

[C. M.]

I considered this, and even made a secondary database doing just that to see
if it would integrate properly within my constraints. Although it works, the
performance isn't terribly good (but neither is it for Decimal types!)
Setting the lookup query by hand worked around the problems with the Wizard,
however, so I'll see how that holds up in full production.

 

[C. M.]

I see your point, too, and apologize for taking my frustration out on you.
Such constraints can be very frustrated, especially when accompanied by
quick deadlines -- When the boss wants a solution "yesterday," you do not
have time to devise a work-around, you need answers "yesterday."

You did answer my question though, in a round-about way. It does appear to
be a bug in the Wizard, and setting the lookup options by hand got around
the problem without having to resort to convoluted VBA code or other
time-consuming solutions.