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)

 

[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.

 

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

 

[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.

 

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

 

[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.

 

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

 

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

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?

 

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