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

From: Malcolm Cook (mec_at_stowers-institute.org)
Date: 07/15/04

  • Next message: MGFoster: "Re: new project, existing data, opens read only" 
    Date: Thu, 15 Jul 2004 17:02:44 -0500

    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: MGFoster: "Re: new project, existing data, opens read only"

    Relevant Pages

    • 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: SQL conversion for back end?
      ... >Sometimes you don't get better performance from SQL ... >SQL Server has great security, ... >- Database SIze: Access is limited to 2Gb from A2000, ... >application design as it does with the platform. ...
      (microsoft.public.access.setupconfig)
    • Re: SQL
      ... > If you design right, you can *shift* much behavior to being data and ... > SQL is close to being Turing Complete. ... It is a bad habit of yours. ... If you're seriously suggesting that CRUD applications are equal ...
      (comp.object)
    • Re: Object-oriented thinking in SQL context?
      ... away from arrays and other non-OO data structures associated ... SQL is constrained to 'trivial' arrays. ... Design: Logical Design", 4th edition. ... Something analogous happens with database design. ...
      (comp.databases.theory)
    • Re: Why does 1:3 relationsihp require another table?
      ... Pro SQL Server 2000 Database Design ... > there can be a relation with NULLs in its tuples (hence 1NF models with ... > can only conclude that you do not believe Louis and Lewis are correctly ...
      (microsoft.public.sqlserver.programming)