Cannot Open SQL Server Table in Access.ADP File.

[Guest]

I don't expect to get an answer, but I just need to post something.

I have an SQL Server 2005 database which functions properly with my
Access.ADP application. In preparing my backend database for shipment I ran
the Generate Script Wizard to recreate the SQL Server Database. The Wizard in
generaly generate a lot of errors of which i could not diagnose, but I was
able to work around. Subsequently, I now have a SQL Script that builds my new
SQL Server database ready for shipment.

What is happening now has me completely stuck. Several of my tables cannot
be accessed in my Access.ADP application. For example, I have a table named
tblObject that appears to be completely intact within SQL Server. You can
view the data structure, add data to it from SQL, etc. It appears to be 100%
fine. But, when I attempt to open the table from within the Access DB Window
I get an error. Several different errors or messages actually.

The first time I click on the table I get a message reporting the "stored
procedure ran successfully but returned no records", even though there are
records in it. Then, If I attempt to open another table, it opens. For
example, I opened tblForm and viewed the table with no problem. After that
then, I go back and double-click on the tblObject table and now I get a
message "the field FormID is read-only" and the table does not open. It
appears the what ever table I successfully opened last will have it's primary
key displayed in a message box "the field FormID is read-only".

Thus, my database is rendered useless. Has anyone come across such behavior.
At this point, I am finding the Script Building features for SQL Server 2005
to be unreliable.

Is there a know bug of some sort.

 

[Sylvain Lafontaine]

Many possibilities here, the most probable one would be that you forget to
set the primary key for your tables in your scripts.

Other possiblities would be that you didn't refresh the database window
after creating the tables/views/SP; for example by closing/reopening the ADP
application. This can be important if you are using ADP to recreate the
database instead of using another pgm.

Finally, make sure that you specify dbo as the owner (or the schema under
SQL-Server 2005) for all your tables/ views/ functions/ stored procedures;
otherwise, you can run into a lot of trouble.

 

[Peter Yang [MSFT]]

Hello Greg,

I understand that you'd like to script out database diagram. Based on my
research, I think we are not able to script out a database diagram in
script wizard. Database diagrams are stored in the "sysdiagrams" table of
each database. In this table the diagram is stored in a column with a
varbinary(max) datatype, which SQL Server translates the data in this
column to the diagram you see on screen.

However, you could use Import/Export wizard to migrate diagram between SQL
2005 databases:

1. Right click the new database->Task->Import Data
2. Select Source Server and database (old database)
3. Select Destination Server and database (new database)
4. Select "Copy data from one or more tables or views"
5. Select "databasename.dbo.sysdiagrams"
6. Click Finish to import the table.

If you have any comments or concerns on this, please feel free to let's
know. Thank you

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[aaron.kempf]

because stored procedures can depend on views.. views can depend on
views also so you're probably not out of the woods yet

I dont think that SQL Server will take 'veiw dependencies' into effect;
but you're right-- I agree-- it should work better

-Aaron

 

[aaron.kempf]

uh what tables are you copying using this DTS package?

'all of them' are you including system tables?

how many tables; any that start with sys for example?

-Aaron

 

[aaron.kempf]

are you sure you didn't copy extended properties from one database to
another?

sounds to me like that is all hosed up
-Aaron

 

[Peter Yang [MSFT]]

Hello Greg,

Based on my experience, this issue may occur if the table sysdiagrams exist
on the destination table. When creating a new database, the table does not
exist.

You may try the following steps to test:

1. On destination database, under Database diagrams, and delete existing
diagrams.

2. Under tables or System tables, delete dbo.sysdiagrams table

3. Try the steps I mentioined to copy the table dbo.sysdiagrams.

4. Right click the database->Refresh and check the diagram again.

If you have any update, please feel free to post back. Thanks.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[aaron.kempf]

hey that sounds REALLY EFFICIENT Peter

why don't you clue in those dipshits in Redmond to make this a little
bit less painful?

'oh the table doesnt exist until' gag me with a spoon

-Aaron

 

[Guest]

Peter,

I looked in my destinatioin database and I had created one Diagram and that
was what was preventing the import to work, as you stated. So, I deleted the
sysDiagrams table from my Destination Database and imported the sysDiagrams
table from my Source Database and it appears my Diagrams transferred, in
tact.

Thanks so much for you help. Now I will be able to continue using this
valuable tool

 

[Peter Yang [MSFT]]

Hello Greg,

My pleasure. Your experience will benefit the community!

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support


=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================