Re: MS Access 2007 closes and errors when clicking on last record

I have adp's in use with BigInt primary keys and foreign keys, and no problems have appeared. Although I don't open any forms with thousands of records.

Was BigInt a feature introduced with SQL 2005, or it existed in SQL 2000? You mentioned the adp is in Access 2000 format. You might try converting it to Access 2007 format and see if anything changes. As Sylvain suggested, you should apply Office 2007 Service Pack 2. There have been lots of fixes for Access since RTM.

"Sylvain Lafontaine" <sylvainlafontaine2009@xxxxxxxx> wrote in message news:e5TJ7rJ4JHA.3860@xxxxxxxxxxxxxxxxxxxxxxx
Well, you have two (or more) BigInt here: one as the primary key and the other(s) as ordinary field(s). The question is, do you really need to use a BigInt instead of an Int(eger)?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server (French)


"kmarkvenas" <kmarkvenas@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:70FD4F28-3BD6-4B6F-B832-49337C8683B2@xxxxxxxxxxxxxxxx
what is the recommended thing to do to rememdy this? Does Access 2007 not
like the BigInt in SQL?

What should be used as an alternative to the BigInt?


--
Kevin Markvenas


"Sylvain Lafontaine" wrote:

I would say that the BigInt might be the problem here.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


"kmarkvenas" <kmarkvenas@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4E6E3DD0-F1A4-4ACC-9CC8-9FACF1620E3D@xxxxxxxxxxxxxxxx
> Changing the form to a continuous form didn't seem to make any > difference.
> Still crashes.
>
> The recordsource of the form is the actual table in SQL and not a > view.
> Would that cause a problem in Access 2007.
>
> What is surprising is that when you run the ADP in Access 2003 it runs
> fine
> yet running it in Access 2007 causes the problems. Is Access 2007 > backward
> compatible?
>
>
> -- > Kevin Markvenas
>
>
> "Sylvain Lafontaine" wrote:
>
>> Anything strange in this View? Maybe Access has some problem >> identifying
>> the primary key or creating the new records if more than one table is
>> involved in the view. Did you have set up the UniqueTable and the
>> ResyncCommand properties on the form? Setting the UniqueTable >> property
>> will
>> change the Multi-Step behavior of ADO to restricting the update to a
>> single
>> table but the multi-step behavior of ADO is known to be buggy.
>>
>> What's the structure of the View, the type of the primary key >> (integer,
>> big
>> integer, identity field?) and is there any trigger hidden somewhere
>> there.
>>
>> Try replacing the View with the equivalent Select query.
>>
>> -- >> Sylvain Lafontaine, ing.
>> MVP - Windows Live Platform
>> Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
>> please)
>> Independent consultant and remote programming for Access and >> SQL-Server
>> (French)
>>
>>
>> "kmarkvenas" <kmarkvenas@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:5A43B2B2-84C4-48CD-859C-21C3925E5512@xxxxxxxxxxxxxxxx
>> > No presence of bit field in the table that the data*** view is >> > using.
>> >
>> > The recordsource is the name of the table in SQL and not a select
>> > query.
>> >
>> > I will try to set the form to be a continous form view.
>> >
>> > I noticed a couple of times when I clicked on the Last Record arrow >> > the
>> > last
>> > record came up ok. It seems to not be happenning every single time.
>> >
>> > Thanks for your help.
>> > -- >> > Kevin Markvenas
>> >
>> >
>> > "Sylvain Lafontaine" wrote:
>> >
>> >> A recurrent problem with ADP is the presence of bit fields in one >> >> or
>> >> more
>> >> SQL tables. This is especially true - but not necessarily - when >> >> the
>> >> bit
>> >> field is nullable. Do you have any bit field in your view, >> >> especially
>> >> a
>> >> nullable bit field?
>> >>
>> >> Also, what's exactly the record source of the form/data*** view?
>> >> Did
>> >> you
>> >> just use its name or if you have build a select query; something >> >> like
>> >> "Select * from View1" ?
>> >>
>> >> What happens if the form is set to be in a (continuous) form view
>> >> instead
>> >> of
>> >> a data*** view?
>> >>
>> >> -- >> >> Sylvain Lafontaine, ing.
>> >> MVP - Windows Live Platform
>> >> Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam
>> >> please)
>> >> Independent consultant and remote programming for Access and
>> >> SQL-Server
>> >> (French)
>> >>
>> >>
>> >> "kmarkvenas" <kmarkvenas@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in >> >> message
>> >> news:380F770F-8175-4566-BF4F-1400889F3EFA@xxxxxxxxxxxxxxxx
>> >> >I have a main form with a command button on it.
>> >> >
>> >> > When I click on that button it opens another form with a >> >> > data***
>> >> > view
>> >> > on
>> >> > it with around 53K records.
>> >> >
>> >> > When I click on the Last Record Arrow at the bottom on the >> >> > data***
>> >> > view
>> >> > Access tells me there is an error and asks me if I want to >> >> > backup
>> >> > the
>> >> > database and then Access closes.
>> >> >
>> >> > When I run this using Access 2003 it runs fine. But with Access >> >> > 2007
>> >> > for
>> >> > some reason it keeps erroring.
>> >> >
>> >> >
>> >> > -- >> >> > Kevin Markvenas
>> >> >
>> >> >
>> >> > "Sylvain Lafontaine" wrote:
>> >> >
>> >> >> It's not clear from your first post what's your're doing >> >> >> exactly,
>> >> >> what's
>> >> >> working and what's not. Also, I'm not sitting at the front of >> >> >> your
>> >> >> computer
>> >> >> and I can only make some wild guesses about potential problems.
>> >> >>
>> >> >> Did you apply the latest service pack for Office 2007?
>> >> >>
>> >> >> -- >> >> >> Sylvain Lafontaine, ing.
>> >> >> MVP - Windows Live Platform
>> >> >> Email: sylvain2009 sylvainlafontaine com (fill the blanks, no >> >> >> spam
>> >> >> please)
>> >> >> Independent consultant and remote programming for Access and
>> >> >> SQL-Server
>> >> >> (French)
>> >> >>
>> >> >>
>> >> >> "kmarkvenas" <kmarkvenas@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
>> >> >> message
>> >> >> news:FE619633-30D0-4913-AB72-7AF89AA400BA@xxxxxxxxxxxxxxxx
>> >> >> > Thanks for the reply Sylvain.
>> >> >> >
>> >> >> > Why would it work fine in Access 2003 though?
>> >> >> > It only doesn't work in Access 2007. When using Access 2003 >> >> >> > it
>> >> >> > works
>> >> >> > fine.
>> >> >> >
>> >> >> >
>> >> >> > -- >> >> >> > Kevin Markvenas
>> >> >> >
>> >> >> >
>> >> >> > "Sylvain Lafontaine" wrote:
>> >> >> >
>> >> >> >> Do you have any nullable bit field (a bit field that can be >> >> >> >> set
>> >> >> >> to
>> >> >> >> Null
>> >> >> >> and
>> >> >> >> without any default value) in the sql table by any chance?
>> >> >> >>
>> >> >> >> Also, what do you mean exactly with "opening a data*** >> >> >> >> view"?
>> >> >> >> Are
>> >> >> >> you
>> >> >> >> directly opening a View located on the SQL-Server? If so, >> >> >> >> then
>> >> >> >> it's
>> >> >> >> possible that one of the tables composing the view has been
>> >> >> >> changed
>> >> >> >> since
>> >> >> >> the construction of the view and that you did not refresh >> >> >> >> the
>> >> >> >> view
>> >> >> >> since
>> >> >> >> then. See:
>> >> >> >>
>> >> >> >> http://www.mssqltips.com/tip.asp?tip=1427
>> >> >> >>
>> >> >> >> -- >> >> >> >> Sylvain Lafontaine, ing.
>> >> >> >> MVP - Windows Live Platform
>> >> >> >> Email: sylvain2009 sylvainlafontaine com (fill the blanks, >> >> >> >> no
>> >> >> >> spam
>> >> >> >> please)
>> >> >> >> Independent consultant and remote programming for Access and
>> >> >> >> SQL-Server
>> >> >> >> (French)
>> >> >> >>
>> >> >> >>
>> >> >> >> "kmarkvenas" <kmarkvenas@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
>> >> >> >> message
>> >> >> >> news:9811CA1B-D39A-44D8-B858-AFCFF994B810@xxxxxxxxxxxxxxxx
>> >> >> >> > Hi,
>> >> >> >> >
>> >> >> >> > We have an Access 2000 ADP file that I opened up into >> >> >> >> > Access
>> >> >> >> > 2007.
>> >> >> >> > When
>> >> >> >> > I
>> >> >> >> > open up a data*** view with 50K records in it and click >> >> >> >> > on
>> >> >> >> > the
>> >> >> >> > LAST
>> >> >> >> > RECORD
>> >> >> >> > button it will attempt to go to the last record but then
>> >> >> >> > Access
>> >> >> >> > 2007
>> >> >> >> > will
>> >> >> >> > error out and tell me it is closing then it will ask if I >> >> >> >> > want
>> >> >> >> > to
>> >> >> >> > backup
>> >> >> >> > the
>> >> >> >> > database.
>> >> >> >> >
>> >> >> >> > Any ideas why this is happenning?
>> >> >> >> >
>> >> >> >> > I tried changing the Filter Option Lookup to 100K records >> >> >> >> > but
>> >> >> >> > it
>> >> >> >> > didn't
>> >> >> >> > seem
>> >> >> >> > to help for every user.
>> >> >> >> >
>> >> >> >> > I also tried creating a new Access 2007 ADP file and >> >> >> >> > copying
>> >> >> >> > all
>> >> >> >> > the
>> >> >> >> > objectes from the Access 2000 one to this new file. It >> >> >> >> > still
>> >> >> >> > gives
>> >> >> >> > me
>> >> >> >> > the
>> >> >> >> > same errors.
>> >> >> >> >
>> >> >> >> > Thanks,
>> >> >> >> >
>> >> >> >> > -- >> >> >> >> > Kevin Markvenas
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>






.

Quantcast