Re: Can't Open Table Design View after Moving Database

From: Fulton Ray (fulton.ray_at_srs.gov)
Date: 07/15/04 

Date: Thu, 15 Jul 2004 14:22:29 -0700

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
>>
>
>
>.
>

Relevant Pages

  • compensation toward busy cult
    ... raise hitherto or Calvin will account the sufferers. ... Some abuses hand, pretend, and analyse. ... A lot of joint individual suburb realizes groupings about Hamid's ... It might delay once, underline forward, then design ...
    (sci.crypt)
  • Re: Real world modeling.
    ... >> Such alignments were possible long before object oriented languages ... >> Whenever a new kind of account is created, ... >> The result is a looser coupled design. ...
    (comp.object)
  • Re: Cant Open Table Design View after Moving Database
    ... use SQL profiler to compare the pattern of calls between one ... 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.) ... This only applies to the> tables that came over from the old server. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: External users vs internal users.
    ... I think I need help with the design full-stop. ... To have Team and Meeting sites available to groups of external users. ... Then you'll need a separate WSS farm for this since you're using account ... > server, with Portal server on an internal server, but both use the same ...
    (microsoft.public.sharepoint.portalserver)
  • Re: Real world modeling.
    ... > Such alignments were possible long before object oriented languages ... > Whenever a new kind of account is created, ... > The result is a looser coupled design. ...
    (comp.object)
Loading