Re: Access2003 ADP & SQLServer2005

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Peter" <peternews@xxxxxxxxxxxxxxxxxx> wrote in message
news:uEjyrSa2HHA.5160@xxxxxxxxxxxxxxxxxxxxxxx
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.





.

Relevant Pages

  • Re: Future of ADPs
    ... ADP is practically unable to understand more ... additional bugs when this schema name is anything but dbo. ... Sylvain Lafontaine, ing. ... I know that there are some kits for accessing some web services from ...
    (microsoft.public.access.adp.sqlserver)
  • Re: NT Integrated security VS Specific User name and password
    ... While in the .adp, and doing nothing other than setting the connection properties, ... to NT authentication and click test, it takes 12+ seconds to come back as - test connection succeeded. ... Your point about referencing all tables with the dbo schema is a place ... "Bob McClellan" wrote in message ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Access 2007, SQL 2000
    ... A common and old problem with ADP: in many place, ... I have a user who I have made a database owner of one of the databases. ... He can go in and use design view on the other views. ... So there is something about the 2000 Schema and the adp that aren't ...
    (microsoft.public.access.adp.sqlserver)
  • Re: SQL Server 2005 schema for Access 2007 ADP
    ... databases in an ADP. ... anything but dbo as the schema owner has been very ... Sylvain Lafontaine, ing. ... is taking the SQL user name. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: owned schema or role members?
    ... Does the user should run queries which manipulate with tables belong to ... anohter SCHEMA? ... I'd go with ROLES and grant SELECT/EXECUTE permission on SCHEMAs that the ... Schema dbdatareader and Schema db_datawriter ... ...
    (microsoft.public.sqlserver.security)
Loading