Can't Open Table Design View after Moving Database

  • Thread starter Thread starter Fulton Ray
  • Start date Start date
F

Fulton Ray

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
 
I expect that the account you are connecting with is dbo in one database and
not in the other.
 
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
 
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
 
ALL RIGHT!! We have a solution.

I followed your suggestion to "use SQL Profiler to compare
the pattern of calls between one
which works and
one
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
 
Fulton,

Delighted. Glad to have been of help. I had a hunch it was those extended
properties. I've brushed up against ADP's dependence upon them awkwardly in
some other situations. It would be great to have some sort of architecture
guide to ADPs that indicate how they are used....

Please let us know if you figure out how to avoid this problem the next time
you move the database. IMWTK (Inquiring minds want to know).

Regards,

-Malcolm Cook
 
hmmm. In . http://support.microsoft.com/?kbid=325335 I read:

Note When you use the Only script 7.0 compatible features option, you can
change the collation. However, any new SQL Server 2000 options (including
user-defined functions, extended properties, the INSTEAD OF trigger, and
indexes on views) will not be considered when the scripts are generated.

did you have this set?

malcolm

Fulton Ray said:
ALL RIGHT!! We have a solution.

I followed your suggestion to "use SQL Profiler to compare
the pattern of calls between one
which works and
one
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 - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA





.
Click to expand...
Click to expand...
 
Malcolm,

I checked to see if the "Only script 7.0 compatible
features" option was available given the way we moved the
database, and I did not see it, so unless it is checked by
default then, no, it was not checked. We moved the
database by making a backup of the old database using
Enterprise Manager's Action - All Tasks - Backup Database
function, then copied the backup to the new server, and
restored it using the Action - All Tasks - Restore
Database function. If my understanding of KB 325335 is
correct, then the method we used should not have changed
its collation. KB 325335 makes note of transfer methods
that do not change a database's collation.
Specifically, "Backup and restore: Restoring a database on
a server that has a different collation than the server
that is used for the backup does not convert the restored
database to the new collation. The database collation
remains as is." I did run the sp_helpsort function
against the old and new servers to see what their
collations were and got identical results. I must admit
that this is unfamiliar territory for me, so I may not
fully understand the issues involved.

Thanks for the help,

Fulton

-----Original Message-----
hmmm. In . http://support.microsoft.com/?kbid=325335 I read:

Note When you use the Only script 7.0 compatible features option, you can
change the collation. However, any new SQL Server 2000 options (including
user-defined functions, extended properties, the INSTEAD OF trigger, and
indexes on views) will not be considered when the scripts are generated.

did you have this set?

malcolm

Fulton Ray said:
ALL RIGHT!! We have a solution.

I followed your suggestion to "use SQL Profiler to compare
the pattern of calls between one
which works and
one
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
Click to expand...
MO
USA
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
Click to expand...
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: " & !
Click to expand...
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.
Click to expand...
City,
MO
USA

I have an Access Project whose back-end SQL
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
for
this
problem without having to create new copies of the
affected tables and repopulate them? Any help
Click to expand...
would
be
greatly appreciated.

Thanks,
ftr



.



.
Click to expand...
Click to expand...



.
Click to expand...
 
Back
Top