Re: Can't Open Table Design View after Moving Database
From: Fulton Ray (fulton.ray_at_srs.gov)
Date: 07/16/04
- Next message: Roca: "BOOK MS ACCESS/SQL SERVER"
- Previous message: Jack D. Ripper: "Re: Crosstab Queries"
- In reply to: Malcolm Cook: "Re: Can't Open Table Design View after Moving Database"
- Next in thread: Malcolm Cook: "Re: Can't Open Table Design View after Moving Database"
- Reply: Malcolm Cook: "Re: Can't Open Table Design View after Moving Database"
- Reply: Malcolm Cook: "Re: Can't Open Table Design View after Moving Database"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 16 Jul 2004 12:37:29 -0700
ALL RIGHT!! We have a solution.
I followed your suggestion to "use SQL Profiler to compare
the pattern of calls between one [table] which works and
one [table] which does not." Turns out the process
aborted after executing the statement (see below) to
retrieve the table's extended properties.
SELECT *, SQL_VARIANT_PROPERTY(value, 'basetype') AS
type
FROM ::fn_listextendedproperty(NULL, N'user',
N'dbo', N'table', N'tblSeriesTitleGrade', NULL, NULL)
I was able to run the above SELECT statement from Access's
Query view which resulted in an "#Error" value being
generated for the table's MS_OrderByOn extended property.
Note - I did not get an error message when I ran the above
from SQL's Query Analyzer. Instead, I got a bit value
(0x, 0x01) of type varbinary. Access apparently handles
the "#Error" result by aborting the successive steps in
the call to SQL, so the stored procedure called DID
executed successfully but no records were returned because
it did not get to that step in the call process. Anyhow,
this pattern repeated itself for all the affected tables.
I was able to change the property value through SQL's
Query Analyzer to a valid value (0) (you can also do it
with sp_updateextendedproperty) and that allowed the above
statement to execute. However, the story does not end
there.
The next step in the call process is for any extended
properties associated with fields in the table (see
statement below).
SELECT TOP 100 PERCENT *, SQL_VARIANT_PROPERTY
(value, 'basetype') AS type
FROM ::fn_listextendedproperty(NULL, N'user', N'dbo',
N'table', N'tblSeriesTitleGrade', N'column', NULL)
ORDER BY PATINDEX(N'MS_DisplayControl', name) DESC
You guessed it - I had similar problems here with errors
being generate for the fields' extended properties. The
solution was the same as above - change the property value
to a valid value (again 0). Once this was done,
everything worked fine from Access.
The mystery still remains as to why this happened. Both
were SQL 2000 Servers. The premove database does not have
these "corrupted" extended properties, and the move to the
new server was done by making a backup from the old server
and restoring it to the new. The good thing is now I know
what to look for in future instances should they occur.
Thanks greatly for you suggestions and assistance!!!
ftr
>-----Original Message-----
>Hmmm.
>
>How did you move the backend?
>
>A thought: use SQL profiler to compare the pattern of
calls between one
>which works and one which does not.
>
>I wonder. Could Access be looking for an extended
property on one of the
>tables which did not survive your migration.
>
>Hmmm
>
>
>--
>Malcolm Cook - mec@stowers-institute.org
>Database Applications Manager - Bioinformatics
>Stowers Institute for Medical Research - Kansas City, MO
USA
>
>
>"Fulton Ray" <fulton.ray@srs.gov> wrote in message
>news:2e59f01c46ab1$d54cc3b0$a301280a@phx.gbl...
>> Thanks for the response. I appreciate it,
>> unfortunately ...
>>
>> I verified that the account I was using is designated as
>> dbo on both servers. In fact I went so far as to
create a
>> new account and assign it dbo rights with the same
results
>> (i.e. connecting using the new account from within the
ADP
>> file, I could open/view some tables from the design view
>> window, but others I couldn't.)
>>
>> I am wondering if maybe I have a couple of corrupt
>> security permissions unique to an Access Project for the
>> tables involved (if this is possible), as I can open any
>> of the affected tables in VB.NET's design view. The
>> situation is similar to how the system tables are hidden
>> in the Access design view. While I can't see the system
>> tables from the Access environment, I can execute
queries
>> programmatically against them like below:
>>
>> Public Sub Check_Access()
>>
>> Dim rs As New ADODB.Recordset, strSQL As String
>>
>> strSQL = "SELECT Name, ID FROM sysObjects" & _
>> "WHERE Name='tblElementItem';"
>>
>> With rs
>> .Open strSQL, CurrentProject.Connection, _
>> adOpenForwardOnly, adLockReadOnly,
adCmdText
>> If Not .EOF Then _
>> MsgBox "Name: " & !Name & " and ID: " & !id,
_
>> vbOKOnly, "System Table Access"
>> .Close
>> End With
>>
>> Set rs = Nothing
>>
>> End Sub
>>
>> The similiarity arises in that I can see the table names
>> in the ADP's design view but am not allowed to open or
>> change them from within it. This only applies to the
>> tables that came over from the old server. Any new ones
>> that I have created either from the ADP's design view or
>> using SQL's Enterprise Manager/Query Analyzer I have
full
>> rights to (open, edit, sort, change design, etc).
>>
>> So, any other ideas?
>>
>> All help is greatly appreciated,
>>
>> ftr
>>
>> >-----Original Message-----
>> >I expect that the account you are connecting with is
dbo
>> >in one database and not in the other.
>> >
>> >
>> >--
>> >Malcolm Cook - mec@stowers-institute.org
>> >Database Applications Manager - Bioinformatics
>> >Stowers Institute for Medical Research - Kansas City,
MO
>> USA
>> >
>> >"Fulton Ray" <fulton.ray@srs.gov> wrote in message
>> >news:340f01c46829$757bc510$7d02280a@phx.gbl...
>> >> I have an Access Project whose back-end SQL database
was
>> >> moved from one server to another. After the move, I
>> >> discovered that I could open some tables/views but
could
>> >> not open others when in the project's design
>> environment.
>> >> In instances where I can't open a table/view, I get
the
>> >> following message (no error number): "The stored
>> procedure
>> >> executed successfully but did not return records."
>> >>
>> >> I use Windows Authenication to connect to the SQL
>> Server,
>> >> and I have checked and reset the standard permission
>> >> settings on the affected tables. Still I cannot open
the
>> >> tables. I have gone so far as to setup an ODBC
>> connection
>> >> to test whether I can open the tables in an Access
MDB
>> >> file and I CAN.
>> >>
>> >> The strangest thing about this is that the problem
does
>> >> not affect the execution of the project's code. I
can
>> >> add, modify, and delete all the records in the
affected
>> >> tables through coded functions and procedure. In
fact, I
>> >> can actually create new tables from the Project's
design
>> >> environment and open them to add records.
>> >>
>> >> So what happened in the move and is there a fix for
this
>> >> problem without having to create new copies of the
>> >> affected tables and repopulate them? Any help would
be
>> >> greatly appreciated.
>> >>
>> >> Thanks,
>> >> ftr
>> >>
>> >
>> >
>> >.
>> >
>
>
>.
>
- Next message: Roca: "BOOK MS ACCESS/SQL SERVER"
- Previous message: Jack D. Ripper: "Re: Crosstab Queries"
- In reply to: Malcolm Cook: "Re: Can't Open Table Design View after Moving Database"
- Next in thread: Malcolm Cook: "Re: Can't Open Table Design View after Moving Database"
- Reply: Malcolm Cook: "Re: Can't Open Table Design View after Moving Database"
- Reply: Malcolm Cook: "Re: Can't Open Table Design View after Moving Database"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
