Subdatasheet error on ADP projects in Access 2010

  • Thread starter Thread starter Grant Morgan
  • Start date Start date
G

Grant Morgan

Running Office 2010 Pro on Windows 7. Database connection to SQL Server 2008
via MS Access ADP.

When viewing a table datasheet in Access 2010 Database Project (ADP) linked
to SQL Server 2008 database, if you go to try to expand the subdatasheet to
show data in linked tables, you receive an error. If the master table is
named 'MASTER_TABLE', and the detail table is named 'DETAIL_TABLE' you
receive the message:
"The table or query name 'dbo.DETAIL_TABL' you entered in either the
property sheet or macro is mispelled or refers to a table or query that
doesn't exist." Note that the last character is dropped off the detail table
name - i.e. DETAIL_TABL instead of DETAIL_TABLE.

If you remove and re-link the subdatasheet, it then works OK and shows the
related data. But if you close and re-open the database and try to expand the
subdatasheet again, it will fail with the same error (even if you previously
saved the table).

This error only occurs in Access 2010 ADP's, and is not evident in a regular
Access Database with local table data.

This definately seems like a bug! Hoping someone can help me out.

Cheers
Grant
 
I too am experiencing the same issue except that I am connecting to SQL
Server 2005 via an ADP project in Access 2010.
 
I do not have Office 2010 but...
Do you have the schema in the Record Source Qualifier showing dbo or
whatever the schema is?
I've seen this cause the probelm you are describing.
hth,
...bob
 
Hi Bob

Table names not showing DBO - they are just showing the actual table name as
it is an ADP.

Was that what you meant?

G
 
Grant,
In 2007..and 2003 .adp, on the property sheet for the form,
On the Data Tab
The first property is Record Source
The 2nd is Record Source Qualifier.
If the schema for the table or stored procedure being used in the Record
Source is dbo
then put dbo in the Record Source Qualifier.
I've seen the problem you are describing and found that simply putting the
schema in the
Record Source Qualifier was what was missing. Along those same lines.... if
the user
does not have permission for the schema you will see the same problem.

hth,
...bob
 
Grant,
Not sure what to suggest to you. I don't build anything where I allow
users
to work at the table level.
 
Hi Bob

I agree - I don't let users to work at the table level, but every once in a
while it's useful if I need to change some data that hasn't had a UI written
for it....

I feel it is a genuine bug in the software - do you know where abouts I can
submit it as such to Microsoft?

G
 
Back
Top