Access2003 ADP & SQLServer2005

[Peter]

Never really bothered with adps and very little experience with SQL server,
but i just installed SQLServer2005 and then tried to create an adp using the
Adventureworks sample database.
The adp appeared to create ok but if I try to open almost any of the table I
get an error
'Ado Error: Object 'dbo.tablenamehere' does not exist or is not a valid
object for this operation'

If I research the error i get stuff about not using a client machine set up
with a different codepage to access my sql server machine. This is off-beam,
because all of this is on one machine.

If I link to Adventureworks from an mdb I can do so succesfully.

So even though Access created the adp and gives me a list of tables there is
clearly something awry with what it has created.

I believe the problem is something to do with the 'schema' part of the table
definition. I don't understand the schema object in sql server terms but I
am able to open 3 tables which appear to belong to dbo. All the other tables
belong to different schemas such as Humanresources and Person and I am
guessing (!!!) that this is what the adp creation gets wrong.(It assumes
that everything always belongs to dbo?)

Can anyone throw any light on this and perhaps simply tell me that adps
can't deal with schemas or alternatively that there is a solution.

 

[Sylvain Lafontaine]

I never tried with the 2005 Adventureworks sample database but it's true
that ADP is able to understand only a single schema name (usually but not
necessarily dbo. ); so if it's understand dbo.table1, it won't be able to
understand Humanresources.table2 or Person.table3 and vice-versa because it
will try to call dbo.table2 and dbo.table3 instead of simply trying to call
table2, table3, etc. (When you call table2, Humanresources.table2 will be
called if there is no other table2 under the current default schema name for
the current login but if you try to call dbo.table2 then
Humanresources.table2 will never be called. Same thing with the other
schema names and with the current default schema name.)

This is one of these annoying bugs that are plaguing ADP.

 

[Peter]

Thanks for your comments.
At least I can stop thinking it's my incompetence that is giving me this
problem.
It does seem silly that the adp-creation process can find it's way through
the schemas to identify the tables, but can't then set up the correct links.
But should I be surprised at anything in the IT world; I guess not!

Pete