using access to update a sharepoint (SPS 2003) list

[aaron.kempf]

team

so i have a nice little list in sharepoint.. about 15k items

i've got the simplest little join statement in access; and im trying to
UPDATE the sharepoint list via an access query.

so im joining between my access table and then sharepoint list and i
get the infamous 'numeric field overflow'.

i checked the range of everything

both columns are 'choice'-- ie they are prepopulated with the years
from 1960 to 2020

but it just looks like a pretty straight update clause and it's not
working.

1) i have a list named DEVELOPMENT RMTS: ALL Boxes or something; i
renamed this to SP_BOXES

2) a query that pulls from sharepoint; into an Access temp table the
min and max values for a year column (at the box level-- one level
above the file granularity)

SELECT Min(TF03_FID_STBN.[File Inclusion Date]) AS MINYEAR,
Max(TF03_FID_STBN.[File Inclusion Date]) AS MAXYEAR, TF03_FID_STBN.[ST
Box Number]
FROM TF03_FID_STBN
GROUP BY TF03_FID_STBN.[ST Box Number]
HAVING (((TF03_FID_STBN.[ST Box Number]) Is Not Null));

this is flashed from the query named 'QF04_FID_STBN_MINMAX' into a
table named 'TF04_FID_STBN_MINMAX'. When I say flash; i just wrap a
make-table statement on the outside of it and push it into a temp
table.

3) simple query that tries to update the sp list--- this just looks
like it's not working
UPDATE TF04_FID_STBN_MINMAX INNER JOIN SP_ALLBOXES ON
TF04_FID_STBN_MINMAX.[ST Box Number] = SP_ALLBOXES.[ST Box Number] SET
SP_ALLBOXES.[Record End Inclusion Date] = [MINYEAR],
SP_ALLBOXES.[Record Start Inclusion Date] = [MAXYEAR];


I wish i could give you guys more information.. i just need to get this
working; and it's driving me crazy

'numeric field overflow' is the error i get

 

[Chris2]

team

I wish i could give you guys more information.. i just need to get this
working; and it's driving me crazy

Aaron Kempf,

I do, too.

'numeric field overflow' is the error i get

This is more likely the result of differing underlying data types
than anything else (or, at least, IMO).

Posting the DDL would be much more useful.

You mentioned:

i checked the range of everything

both columns are 'choice'-- ie they are prepopulated with the
years from 1960 to 2020

Note that the above description does not relate any information
about the data types of the columns involved.

What is the exact data type of the columns in Sharepoint, and was is
the exact data type of the columns in MS Access?

Also, how are you connecting to Sharepoint? ADO? ODBC? ODBC
Direct? OLEDB?


Sincerely,

Chris O.

 

[aaron.kempf]

im connecting via Access linked tables. everythign is in access.

Access against Sharepoint-- these systems are supposed to be geared
towards beginners.

BUT THEY JUST DONT WORK CORRECTLY.

'numeric field overflow' isn't a valid debugging message.
i dont know or care what datatype the fields really are in under the
covers. if shit is numeric and shit is numeric; and it doesn't work
then it is microsofts fault.

perhaps i should change these fields to not be multiple choice; and
then change them back to mult choice after the records get in there?

i just wish that MS would quit the bullshit and make this system more
open. I mean-- they already have a wonderful system for SQL via MDB--
why do they need to add another layer of complexity to the equation?
so that they can make it a feature and sell it to us?

Microsoft you are a bunch of drunk dorks and i hope your company gets a
whuppin' soon. you abuse your customers, your employees..

and you dont fix bugs.

TO MICROSOFT
make the WHOLE bug-tracking system OPEN AND PUBLIC microsoft so that we
dont spend half of our days tracking down a bug that you guys already
know about.

AND START FIXING BUGS MICROSOFT, THEY'RE NOT FEATURES, THEY ARE
**BUGS**

piece of #### company god i hope you guys get slaughtered in the open
marketplace soon

 

[Terry Kreft]

Well, a diatribe against a company is just a great use of NG resources.

If you actually knew what you were doing then the error message 'numeric
field overflow' would point you in exactly the right direction, just as it
did Chris2 when he replied to your first post.

Oh well, if you actually get around to answering him, or asking for
clarification of his response so you can answer him, then you might get an
answer to your problem.

 

[lylefair]

It seems a "tolerance overflow" error has occurred here.

A good error handler might post all the relevant information here, and
suggest a "SLEEP" procedure (waking up after we have had a chance to
consider that).

I'm assuming the error occurs in "3) simple query" (else why would you
have carried your description that far?) and that you have included
parts 1) and 2) as background information?

I see that you say you have checked the range of everything and my
recollection of previous posts you have made in other groups suggest
that you are much too advanced to say that numeric to numeric should
work regardless of data type, unless there is something special that I
don't understand.

Can we assume that we are talking JET?

Can we assume that your machinations with Date and Year do not at any
time result in asking whatever engine we are using to put eight bytes
of date information into a four byte integer holder?

BTW, Aaron, when you are calm and rational, your capability shines
through. When you rant, it's quite a bit harder to discern.

It would be really helpful for me and probably for others as well, if
you wrote a nice little post on Access and SharePoint, about which I
know Zip. Yes, I could Google it, but some personal observations and
pointers would provide a motivation for doing so.

 

[aaron.kempf]

Terry

I know what im doing.

Sharepoint is buggy; Access is buggy.. and I dont know why in the hell
people buy this shit anymore. Microsoft has proved their incompetence.

all I'm doing is linking to a sharepoint list and running an update
query. I mean.. it's all that I'm doing.

And it doesnt work.. and the ONLY error message i've ever gotten out of
doign anything like this is 'numeric field overflow'

i think that microsoft did a jerry-rigged job of making access and
sharepoint work together; and I think that they need to fix it. Every
error i ever get is 'numeric field overflow'-- i mean.. come on
microsoft; give real debugging information

 

[aaron.kempf]

and im not ever really doing anything with date fields. it is all
strings.

-aaron