RE: SQL Server 2000 & Access 2007 Project

Hi,

I tried creating a new ADP and connecting it to my existing database - this
produced the same error. I then tried creating a new database, and
redirecting the ADP to the new database. Then, when I tried to create a new
table in the new database, I immediately got a similar error - Microsoft
Office Access can't find the object table1.

I checked and I found that the function fn_listextendedproperty does exist
in my Master database.

Thanks,
I. Kinal

"Adams Qu [MSFT]" wrote:

Dear Kinal,

Thank you for your reply.

You have indicated that the Access database is an Access Project file (ADP)
and we are able to open the table's design view without a problem via
linking a table feature in normal database (MDF). But the following error
occurs when we attempt to open the same table's design view in the Access
project:

Microsoft Office Access can't find the object 'Select *,
sql_varient_property(value, 'basetype') as type from
::fn_listextendedproperty(N'ms_displayviewsonsharepointsite',N'user',N'

If I have misunderstood the latest information you provided, please let me
know.

Based on the Book Online, this function is used to get the extend property
of the database and this function may be called by the Access program when
we open in the design view so as to get the relevant data type.

However, I have conducted much test on my test machine (connect Access 2007
to SQL Server 2000 database) but cannot reproduce such behavior. According
to the error message, I suspect that the function returns the blank value
so that SQL server cannot get the type of the data in the design view. We
can try to run this SQL statement on the SQL Server 2000 Query Analyzer to
check if what result is returned or just a blank result.

In addition, as you have previously attempted to link a table in SQL Server
2000 in the newly created database, I also suggest we create a new ADP file
in Access 2007 and then point it to the SQL Server 2000 to check if the
issue also occurs in that newly created ADP file.

1. If the issue does not occur to the new ADP file, we can simply use this
newly created ADP file in the further. We can conclude that the issue is
caused by the connection parameter and the issue is resolved after we
create a new file and rebuild the connection.

2. However, if the issue still persists in the new ADP file, please try to
modify the connection and make it connect to the other database such as
Northwind or the newly created database to test if the issue is related to
database specified.

Have a nice day!

Best regards,

Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support

Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.



--------------------
| Thread-Topic: SQL Server 2000 & Access 2007 Project
| thread-index: Ace88YvnqYb7qk9cTde3IyKmkc88lQ==
| X-WBNR-Posting-Host: 207.46.19.168
| From: =?Utf-8?B?SS4gS2luYWw=?= <ikinal@xxxxxxxxxxxxxxxxx>
| References: <A6D68A9B-C7B4-4688-8D2B-5075288EA0DC@xxxxxxxxxxxxx>
<U8XSOXIvHHA.2220@xxxxxxxxxxxxxxxxxxxxxx>
| Subject: RE: SQL Server 2000 & Access 2007 Project
| Date: Mon, 2 Jul 2007 14:40:02 -0700
| Lines: 118
| Message-ID: <7A37D94D-DC74-41CE-8D5C-7C15F3252D67@xxxxxxxxxxxxx>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
| Newsgroups: microsoft.public.sqlserver.clients
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.clients:527
| NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
| X-Tomcat-NG: microsoft.public.sqlserver.clients
|
| Hi,
|
| Thank you for your response.
|
| The Access database in questions is an Access Project file. I tried your
| suggestion of creating a new, normal database and linking a table from
the
| SQL Server - I was able to open the table's design view without a
problem.
|
| Here is the beginning of the error that I get when trying to open the
same
| table's design view in the Access project: Microsoft Office Access can't
find
| the object 'Select *, sql_varient_property(value, 'basetype') as type
from
| ::fn_listextendedproperty(N'ms_displayviewsonsharepointsite',N'user',N'
|
| Is it looking for that function? Why??
|
| Thanks,
| I. Kinal
|
| "Adams Qu [MSFT]" wrote:
|
| > Dear Kinal,
| >
| > Thank you for posting here.
| >
| > From the problem description of the post you submitted, my
understanding
| > is: the error message "Object does not exists" will be received if we
open
| > query or table in Design View after upgrading to Access 2007. If I have
| > misunderstood about your concern, feel free to let me know.
| >
| > Before going further, I would like to explain that Office Access offers
two
| > ways to connect to SQL Server data: linking to SQL Server and Access
Data
| > Projects (ADPs). In order to ensure the current status, please first
| > confirm with me the way to connect to SQL Server is MDB or ADP.
| >
| > Thank you for taking time to help me check above information.
| >
| > At the same time, to isolate the cause of the issue, it is recommended
that
| > we create a new Access 2007 database and then link table to the SQL
Server
| > 2000 to check if issue still occurs in this newly created Access file.
I
| > have included the summary of the steps for you:
| >
| > 1. Create a new Access 2007 Database
| > 2. Linking to SQL Server
| > 3. Open anyone of the table in the Design Mode and then check if the
issue
| > still persists in this newly created table.
| >
| > Related Information
| > ===================
| > Migration considerations for Access 2007
| >
http://technet2.microsoft.com/Office/en-us/library/1dce641e-ba1c-446a-8ff2-2
| > 21769a58ba51033.mspx?mfr=true
| >
| > If anything is unclear in my post, please don't hesitate to let me know
and
| > I will be glad to help. Thank you for your efforts and time.
| >
| > Have a nice day!
| >
| > Best regards,
| >
| > Adams Qu
| > MCSE, MCDBA, MCTS
| > Microsoft Online Support
| >
| > Microsoft Global Technical Support Center
| >
| > Get Secure! - www.microsoft.com/security
| > =====================================================
| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
| > =====================================================
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| > --------------------
| > | Thread-Topic: SQL Server 2000 & Access 2007 Project
| > | thread-index: Ace7cIjBHI5h7+CTSvCZ5a4VviUIdA==
| > | X-WBNR-Posting-Host: 207.46.19.197
| > | From: =?Utf-8?B?SS4gS2luYWw=?= <ikinal@xxxxxxxxxxxxxxxxx>
| > | Subject: SQL Server 2000 & Access 2007 Project
| > | Date: Sat, 30 Jun 2007 16:44:00 -0700
| > | Lines: 14
| > | Message-ID: <A6D68A9B-C7B4-4688-8D2B-5075288EA0DC@xxxxxxxxxxxxx>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
| > | Newsgroups: microsoft.public.sqlserver.clients
| > | Path: TK2MSFTNGHUB02.phx.gbl
| > | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.clients:522
| > | NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
| > | X-Tomcat-NG: microsoft.public.sqlserver.clients
| > |
| > | Hi,
| > |
| > | I use an Access Project as a front end for my database, which is in
SQL
| > | Server 2000. Everything is working fine in Access 2003. However, I
just
| > | upgraded one machine to Access 2007, and now Access cannot open any
query
| > or
| > | table in design view - I get an error saying that the object does not
| > exist.
| > | This error will either reference the query or table by name, or will
| > | reference a cryptic SQL Statement that mentions Sharepoint. However,
I
| > can
| > | view the results of the query or contents of the table just fine. Is
| > there
| > | something that I need to do in the database for it to open in Access
| > 2007? Or
| > | is the only solution an upgrade to SQL Server 2005?
| > |
| > | Thanks,
| > | I. Kinal
| > |
| >
| >
|


.

Relevant Pages

  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Backup still not working
    ... So you may try to rebuild the RSM database if the ... > rename the existing files in this folder to a .old file extension, ... > this time unchecks "First Storage Group" under Microsoft Exchange Server. ... > 1) Open SQL Server Enterprise Manager. ...
    (microsoft.public.windows.server.sbs)
  • Re: cant move sharepoint by the instructions. AGAIN
    ... Monitoring database to another location. ... If you installed the SQL Server or the SQL Server Management Tools, ... Microsoft CSS Online Newsgroup Support ... This newsgroup only focuses on SBS technical issues. ...
    (microsoft.public.windows.server.sbs)
  • Re: Database Connectivity
    ... But, MSDE has been know to work very reliably in small user LANS, ... > just a simple step to attach the tables from MSDE to SQL Server. ... database, or pay for an "industrial-strength" version of it when there ... Don't like Microsoft?, sounds like a personal problem to me. ...
    (comp.lang.python)
  • Re: Access 2003 to SQL Server 2000 over a VPN
    ... Coming back to the original VPN discussion... ... Would you consider using an ADP talking across an fast broadbank link to ... local database, but ideally we would like to centralise. ... Would SQL Server replication be an option, ...
    (microsoft.public.access.adp.sqlserver)
Loading