Tables and SP-s don't appear in design in adp

[Vadim Rapp]

AN> Hi, I posted this a few days ago at msde and
AN> asp.db groups but didn't get any real help. I got
AN> the advice not to use Access because MSDE='SQL
AN> server'='too powerful for Access'.

AN> I installed MSDE 2000 on my W2k SP4 system but I
AN> can't see the tables and stored procedures from
AN> within Access projects in the design mode, and
AN> from Visual Interdev 6.0 projects. I see the lists
AN> of tables and sp-s, and I can open the tables as
AN> data but as soon as I try to call them with
AN> design, a window flashes open and disappears.
AN> I have installed and reinstalled MSDE, Office 2000
AN> and VS a few times for no avail. I cannot start a
AN> new project to create a new db. It says 'Overflow'
AN> (just that, nothing else) when I try to enter the
AN> login&passwd. Please respond if this is some known
AN> issue. Why does this happen? I have also a MSDE 7
AN> installed and everiything works there. It cannot
AN> be anything linked with permissions. I'm the
AN> installer of the server MSDE 2000 with
AN> SECURITYMODE=SQL. Yet I assigned miself also to
AN> the db_owner group through osql. Doesn't help.
AN> Thank you.

some ideas:

Check if the overflow happens if you create an ODBC source to the same database

Apply sql server service pack to MSDE (from http://www.microsoft.com/sql/downloads/2000/sp3.asp)
and to Office from http://www.microsoft.com/office/downloads/top10.asp


Vadim

 

[Ashot Nersessian]

Hi, I posted this a few days ago at msde and asp.db groups but didn't get
any real help. I got the advice not to use Access because MSDE='SQL
server'='too powerful for Access'.

I installed MSDE 2000 on my W2k SP4 system but I can't see the tables and
stored procedures from within Access projects in the design mode, and from
Visual Interdev 6.0 projects. I see the lists of tables and sp-s, and I can
open the tables as data but as soon as I try to call them with design, a
window flashes open and disappears.
I have installed and reinstalled MSDE, Office 2000 and VS a few times for no
avail. I cannot start a new project to create a new db. It says 'Overflow'
(just that, nothing else) when I try to enter the login&passwd. Please
respond if this is some known issue. Why does this happen? I have also a
MSDE 7 installed and everiything works there. It cannot be anything linked
with permissions. I'm the installer of the server MSDE 2000 with
SECURITYMODE=SQL. Yet I assigned miself also to the db_owner group through
osql. Doesn't help.
Thank you.

 

[BJ Freeman]

When you open the ADP, what is in the File->connection Dialog

 

[Ashot Nersessian]

No problem opening an existing db from a project. Clicking File->Connection,
I get the Ole DB connection interface and connect to an existing db without
problem. But then nothing in default db-s, not even in samples (like
NorthWind, Pubs etc) I can watch in design mode.

 

[Ashot Nersessian]

MSDE 2000 is installed from sql2kdesksp3.exe so service pack 3 is already
there.

 

[BJ Freeman]

Also I assume that when you click on Test connection, you get the "Test
connection succeeded"

 

[BJ Freeman]

It is best if you look at the SQL users from the SQL manager. under security
logins look at the permissions of the userID you have created.
then clickon the db and rtclick on properties.
you will see the permission for each user for that DB.
sa is the only SQL user that can create new DB remotely.
So unless you NT accounts has sa rights it can not create a db outside of
the SQL manger.
you can not assign a ownership of an DB till it is created.

I remember a knowledge base relative to the overflow, might check there.

 

[Ashot Nersessian]

There is no SQL manager. The installation is MSDE 2000. We're starting over
again. Never mind, I have also MSDE 7 installed which works and this one
I'll figure out. Thanks.

 

[Ashot Nersessian]

You seem to be right about the KB article. It is about upsizing a db but it
is the same. Looks that I need to update my Office installation. I wasn't
using it much, so it turns out it has grown old. Thanks again.

 

[BJ Freeman]

Glad your getting things worked out.

Workaround:
1. Install SR-1 and SP 3.
2. That will solve the problem manipulating tables and sp-s in existing dbs
also with design. But you again get 'Overflow' error trying to create a new
adp to create a new db or trying to upsize an Access 2000 db to SQL 2000 db.
3. Create new databases from osql command line utility with

<code>
use master
go
create database dbname
on
( NAME = nonexistent_dat,
FILENAME = 'full path to nonexistent.mdf' )
go
</code>

Then create an adp file connecting to this (newly created) db and crate
tbl-s, sp-s, triggers and all with design.
4. Workaround for upsizing an Access 2000 db to SQL 2000 db is described in
KB article 272384 - ACC2000 Overflow Error Message When You Try to Upsize to
SQL Server 2000.htm.

There is also the "Access 2000 and SQL Server 2000 Readiness Update" which I
haven't had a look at yet.
Thanks for your attention.

for

 

[Ashot Nersessian]

Workaround:
1. Install SR-1 and SP 3.
2. That will solve the problem manipulating tables and sp-s in existing dbs
also with design. But you again get 'Overflow' error trying to create a new
adp to create a new db or trying to upsize an Access 2000 db to SQL 2000 db.
3. Create new databases from osql command line utility with

<code>
use master
go
create database dbname
on
( NAME = nonexistent_dat,
FILENAME = 'full path to nonexistent.mdf' )
go
</code>

Then create an adp file connecting to this (newly created) db and crate
tbl-s, sp-s, triggers and all with design.
4. Workaround for upsizing an Access 2000 db to SQL 2000 db is described in
KB article 272384 - ACC2000 Overflow Error Message When You Try to Upsize to
SQL Server 2000.htm.

There is also the "Access 2000 and SQL Server 2000 Readiness Update" which I
haven't had a look at yet.
Thanks for your attention.