Can't create ADP database on MSDE using MS Access

[Lou Arnold]

I can't seem to create an ADP Project database on MSDE using MS
Access. I get no error messages, but no database is created and I
can't create tables using MS Access.

I've tried the SQL Server group but they can't solve it.

Please don't suggest alternative tools for creating a database. The
objective is to use MS Access.

Symptoms:
Using the MS Access SQL Server Database Wizard seems to work until the
progress bar appears showing progress in the database creation.
However, this progress bar appears only for a split second and we've
verified that the database has not been created in the sql server.
There is no error message from Access.

Some details:
1) This is all done on one Win2K machine. The server is MSDE2000A and
MS Access is at SP3.
2) File and Printer Sharing and Client for MS networks are
enabled.
3) The MSDE is set for Windows Authentication only, but there is
no domain controller. This is an objective of the test-bed we are
trying to create, although we're not sure we can do this without a
domain controller. TCP/IP and named pipes are enabled, because of a
potential requirement for named pipes if Windows Authentication is to
work.
4) Only the Windows standard "Administrator" account has been
used and that account has the same password as the MSDE SA account. We
thought this would give MS Access all the required permissions under
the BuiltIn/Administrators account on the MSDE.
5) As this is a named instance of MSDE, the client and server
network components have been set up to use port 1433.
6) There is no firewall on the computer.
7) We are sure the server is running.

Lou Arnold
Ottawa, Canada.

 

[Van T. Dinh]

See comments in-line.

(note that I use Win2K Server as DC so this may not be correct for you.)

--
HTH
Van T. Dinh
MVP (Access)

I can't seem to create an ADP Project database on MSDE using MS
Access. I get no error messages, but no database is created and I
can't create tables using MS Access.

I've tried the SQL Server group but they can't solve it.

Please don't suggest alternative tools for creating a database. The
objective is to use MS Access.

Symptoms:
Using the MS Access SQL Server Database Wizard seems to work until the
progress bar appears showing progress in the database creation.
However, this progress bar appears only for a split second and we've
verified that the database has not been created in the sql server.
There is no error message from Access.

Some details:
1) This is all done on one Win2K machine. The server is MSDE2000A and
MS Access is at SP3.
2) File and Printer Sharing and Client for MS networks are
enabled.
3) The MSDE is set for Windows Authentication only, but there is
no domain controller. This is an objective of the test-bed we are
trying to create, although we're not sure we can do this without a
domain controller. TCP/IP and named pipes are enabled, because of a
potential requirement for named pipes if Windows Authentication is to
work.

Not sure of the last statement. We use Windows Authentication WITHOUT Named
Pipe.

4) Only the Windows standard "Administrator" account has been
used and that account has the same password as the MSDE SA account. We
thought this would give MS Access all the required permissions under
the BuiltIn/Administrators account on the MSDE.

I doubt that very much. "Administrator" and "SA" are still 2 different
accounts whether the password is the same or not. Furthermore,
"Adminisitrator" would be Windows-authenticated and "SA"
SQLServer-authenticated.

My feeling is that permission is the problem here because (Windows-local)
Administrator may need to have Database Creators permission. The
BuiltIn/Administrators generally refer to "Domain Administrators". I am not
sure wether you "Administrator" would be considered as a member of Domain
Administrators.

I would suggest using the Enterprise Manager (note whether you have to sign
in as "SA"), remove the Public permission of sample database (either Pubs or
NorthWind). Close the EM. Using Administrator, try setting an ODBC Source
to one of the sample database and see if it let you access the data (you can
use the ODBC-linked Table in Access MDB for testing in the mean time).

 

[Lou Arnold]

Van...

Some interesting comments, thank you very much.

As I have only the MSDE, I can't use Enterprise Manager. Nor was I
able to install the sample databases into MSDE. But I'll look at ODBC
and see what I can try.

Thanks.
Lou.

 

[Van T. Dinh]

IIRC, there is an "MSDE Developer's Tool" (or similar) that you can buy for
about US$50.00 which includes the EM you can use with your MSDE.

 

[Lou Arnold]

Van
The objective is to use MS access and not another tool. This setup
eventually becomes part of a development configuration and the
developers will use MS Access exclusively both to create and use
databases.

I never set up ODBC for this test. Eevrything is a fresh install. I
assumed ODBC setup would be done automatically by MS Access. Do I need
to set up ODBC? If so, how, specifically?

Many thanks,
Lou.

 

[Van T. Dinh]

See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)

Van
The objective is to use MS access and not another tool. This setup
eventually becomes part of a development configuration and the
developers will use MS Access exclusively both to create and use
databases.

I am aware of this in your previous post. On my set-up, I can create an ADP
connecting to either a new SQL Database or an existing database (regardless
of the original creator like Access / VB / EM). However, you can't at the
moment and GUI tools such as EM make it easier to find the problem. Sure, I
can probably use isql or osql in trying to find the problem but it is a lot
easier for me to use EM.

I never set up ODBC for this test. Eevrything is a fresh install. I
assumed ODBC setup would be done automatically by MS Access. Do I need
to set up ODBC? If so, how, specifically?

No, it is NOT done automatically by Access because ODBC is part of the OS.

It is shown as "Data Source (ODBC)" in Control Panel or Administrative
Tools. You need to check in Windows Help or any Windows book you have.

 

[Lou Arnold]

Van
I tried setting up ODBC, but the results were the same. I'm not sure i
set up ODBC properly. There is after all no existing database - I am
trying to create one.

Lou.

 

[Lou Arnold]

BTW: There is an existing User DSN named MOIS that uses the SQL Server
driver. Configuring that DSN probably dint help any since there was no
exiting default database. Using the test connection button did show a
successful test however.

Hope this provides you with more info.

Lou.

 

[Van T. Dinh]

There are *always* existing databases on the SQL Server since the SQL Server
actually uses some of these databases. IIRC, these databases are always set
up with every instance of SQL Server / MSDE:

master
model
msdb
tempdb

and usually the 2 sample databases:

NorthWind
Pubs

 

[Lou Arnold]

OK, well, I can connect to the master DB and it will let me create
tables there, but I cannot create a new database.

Lou.

 

[Van T. Dinh]

--
HTH
Van T. Dinh
MVP (Access)

OK, well, I can connect to the master DB and it will let me create
tables there, but I cannot create a new database.

Lou.

 

[Van T. Dinh]

If you have the SQL Server B.O.L. (Books-On-Line) which you can download
from Microsoft Web site, check the T-SQL function PERMISSIONS().

 

[Lou Arnold]

I'm not sure what it is under permissions that you wanted me to look
at. I did grant login permission to the master db for the local
Windows Admin user (computer-name\Administrator). Should I grant that
for all dbs - master, model, msdb, and temp?

Notes

1) Under the Windows Admin account, I can log onto the SQL server with
osql -E (trusted connection i.e. Windows Authentication), but not
thru the SA account. With osql I was also able to create a database
(Use Master, CREATE DATABASE mytest).

2) With another GUI admin tool named MSDE Plus, under the Windows
Admin account, I was also able to create a database.

3) I can connect to these two databases via MS Access and create
tables, again under the Windows Admin account.

4) Can't figure out why MS Access still won't allow creation under the
Win Admin account.

Lou.

BTW: The sample dbs are a separate download for

 

[Van T. Dinh]

See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)

I'm not sure what it is under permissions that you wanted me to look
at. I did grant login permission to the master db for the local
Windows Admin user (computer-name\Administrator). Should I grant that
for all dbs - master, model, msdb, and temp?

With the T-SQL PERMISSIONS() function, you can check out which permission
the user (local Windows\Administrator) has. In particular, you look for the
dbCreator permission (Server Role)

Notes

1) Under the Windows Admin account, I can log onto the SQL server with
osql -E (trusted connection i.e. Windows Authentication), but not
thru the SA account. With osql I was also able to create a database
(Use Master, CREATE DATABASE mytest).

It does sound like the local Windows/Administrator has dbCreator permission.

2) With another GUI admin tool named MSDE Plus, under the Windows
Admin account, I was also able to create a database.

I haven't heard of MSDE Plus since I use the EM that does most thing I want.
I does confirm that local Windows\Administrator has dbCreator permission.

3) I can connect to these two databases via MS Access and create
tables, again under the Windows Admin account.

ADP or MDB? It is an improvement, regardless.

4) Can't figure out why MS Access still won't allow creation under the
Win Admin account.

Not sure now. My Access installation works every I tried for both new SQL
Server databases and existing SQL databases.

OTOH, most of my developments are MDBs with JET or SQL Server Back-End. So
far, I created 1 tiny ADP for actual use and about 10 ADPs for testing only.

Suggest you try the Microsoft Knowledge Base and see whether there is any
bug or needed settings that oare often missed. The SQL / Access I use are
"default" installation and I don't remember doing any "strange" settings.

 

[Lou Arnold]

Thank you, Van. I'm sure its something simple.

ADP or MDB? It is an improvement, regardless.

This is an ADP project. I dont thonk you can create an MDB project
with SQL Server.

Best wishes in your work, Van.

I will repost in another group with the new information and let's hope
someone else can help.

Lou.
--------

 

[Van T. Dinh]

You can use an Access database (MDB) with SQL Server / MSDE Back-End. In
fact, I am not sure whether you are aware that Microsoft recommends MDB with
SQL Server / MSDE Back-End rather than ADP.

--
HTH
Van T. Dinh
MVP (Access)