MS Access 2007 closes and errors when clicking on last record

[kmarkvenas]

Hi,

We have an Access 2000 ADP file that I opened up into Access 2007. When I
open up a datasheet 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,

 

[Sylvain Lafontaine]

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 datasheet 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]

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.

 

[Sylvain Lafontaine]

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]

I have a main form with a command button on it.

When I click on that button it opens another form with a datasheet view on
it with around 53K records.

When I click on the Last Record Arrow at the bottom on the datasheet 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.

 

[Sylvain Lafontaine]

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/datasheet 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 datasheet 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]

No presence of bit field in the table that the datasheet 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.

 

[Sylvain Lafontaine]

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]

The table in SQL is a single table. The primary key is a bigInt.
The columns in the table are a mixture of bigint, int, varchar, nvarchar,
smalldatetime, and money variable types.

There are no triggers on this table.

I am trying to use the Continous Form option and it seems to be working
right now in the Access 2007 ADP I created last night.

When I switched the form to continous form in the 2000 version of the ADP it
didn't make any difference.

What I did last night was create a new Acess 2007 ADP file and copy all the
forms, reports, and modules from the 2000 version to the Access 2007 version.

 

[kmarkvenas]

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?

 

[Sylvain Lafontaine]

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]

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?

 

[Sylvain Lafontaine]

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)

 

[Paul Shapiro]

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.

 

[kmarkvenas]

Paul,

We removed the BigInt types for the table in SQL and switched them over to
regular INT's. Problem still occurrs.

I already installed Office SP2. Not sure why Access 2007 doesn't want to
cooperate.

Typical Microsoft I guess.

Thanks for your help though.

Kevin

 

[Sylvain Lafontaine]

Did you try to decompile or better, to import everything into a blank
database after removing the BigInt?

Also, sometime, when there is a problem with a form, instead of importing it
or to save it under a new name or copy&paste it, it's better to open a blank
form and to copy&paste all the controls from the old form on it. When there
is a strange error on a form, making a direct copy of the whole form will
often copy the error (corruption) too.

Also, is there in the table any field with a possible reserved name such as
[Date]? If possible, try removing every field until you find the culprit;
if there is one in particular. Don't forget to decompile the ADP file as
often as possible when making these tests.

--
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]

How do I decompile the ADP file? Do I need to do this in Access 2000 which it
was created in?

Thanks,
--
Kevin Markvenas

Did you try to decompile or better, to import everything into a blank
database after removing the BigInt?

Also, sometime, when there is a problem with a form, instead of importing it
or to save it under a new name or copy&paste it, it's better to open a blank
form and to copy&paste all the controls from the old form on it. When there
is a strange error on a form, making a direct copy of the whole form will
often copy the error (corruption) too.

Also, is there in the table any field with a possible reserved name such as
[Date]? If possible, try removing every field until you find the culprit;
if there is one in particular. Don't forget to decompile the ADP file as
often as possible when making these tests.

--
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)

Paul,

We removed the BigInt types for the table in SQL and switched them over to
regular INT's. Problem still occurrs.

I already installed Office SP2. Not sure why Access 2007 doesn't want to
cooperate.

Typical Microsoft I guess.

Thanks for your help though.

Kevin

 

[Sylvain Lafontaine]

The easiest way to decompile an ADP or a MDB file is to add a dummy
reference in the References dialog window (on the VBA Window) and then
remove it. Changing the list of references forces Access to recompile every
module the next time they are accessed.

First you add a reference (anyone), then you close the References dialog
window (important!). After that, you can reopen the references window and
remove the dummy that you have just added before.

The other way is to use the option switches /decompile /compact /repair in a
shortcut link to your database project file, something like:

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE"
"C:\MyDatabase.adp" /decompile /compact /repair

--
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)

How do I decompile the ADP file? Do I need to do this in Access 2000 which
it
was created in?

Thanks,
--
Kevin Markvenas

Did you try to decompile or better, to import everything into a blank
database after removing the BigInt?

Also, sometime, when there is a problem with a form, instead of importing
it
or to save it under a new name or copy&paste it, it's better to open a
blank
form and to copy&paste all the controls from the old form on it. When
there
is a strange error on a form, making a direct copy of the whole form will
often copy the error (corruption) too.

Also, is there in the table any field with a possible reserved name such
as
[Date]? If possible, try removing every field until you find the
culprit;
if there is one in particular. Don't forget to decompile the ADP file as
often as possible when making these tests.

--
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)

Paul,

We removed the BigInt types for the table in SQL and switched them over
to
regular INT's. Problem still occurrs.

I already installed Office SP2. Not sure why Access 2007 doesn't want
to
cooperate.

Typical Microsoft I guess.

Thanks for your help though.

Kevin
--
Kevin Markvenas


:

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.

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)


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


:

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)


message
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


:

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)


message
No presence of bit field in the table that the datasheet 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


:

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/datasheet
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 datasheet 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)


message
I have a main form with a command button on it.

When I click on that button it opens another form with a
datasheet
view
on
it with around 53K records.

When I click on the Last Record Arrow at the bottom on the
datasheet
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


:

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)


in
message
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


:

Do you have any nullable bit field (a bit field that
can
be
set
to

 

[Gema ; Rodriguez]

Hi,

We have an Access 2000 ADP file that I opened up into Access 2007. When I
open up a datasheet 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,

 

[huangtianliang]

»ÆÌìÁÁ·¢²¼ÐÂÎÅ×éÐÅÏ¢