Q: Why can't Access create a DB in SQL Server, yet OSQL can?

[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. Why??

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

Please don't suggest alternative tools for creating a database I've
tried that and it works. The objective now is to use MS Access.

Symptoms:
We attempt to create a new database from MS Access on one computer, in
MSDE on the other computer. The SQL Server Database Wizard executes
normally, produces no error message, but at its final database
creation, its progress bar appears for only a split second and its bar
is empty. Looking in the MSDE directory confirms that no database has
been created.

Yet I am able to log on and create a database using Osql.exe, as well
as MSDE Plus a 3rd party alternative to MS Enterprise Manager. What
could be different between MS Access and this 3rd party tool??

Some details:
1) This is a single Win2K computer with MSDE2000A, MS Access SP3,
and MDAC 2.8.
2) File and Printer Sharing and Client for MS networks are
enabled.
3) MSDE is set for Windows Authentication only, but there is no
domain controller. This is an objective of the test-bed.
4) Named pipes have been disabled. Tests with named pipes enabled
did not change the symptoms or result.
5) All work was done under the standard Windows Administrator
account.
6) As this a named instance of MSDE, the client and server
network components have been set up to use port 1433.
7) There is no firewall on the computer.
8) I can connect to the Master db (a SQL Server default) and
create tables in there, but I cannot create a new database.
9) Using osql -E I was able to log onto the SQL Server in
Windows Authentication mode from the Windows Admin account. I was also
able to create a database once logged in. I could not log on using the
SA account and password.
10) The utility MSDE Plus is an alternative to MS Enterprise
Manager. With it I was able to create a database while logged on as
the Windows Administrator.

 

[Lou Arnold]

Corrected para:

Symptoms:
We attempt to create a new database from MS Access in MSDE on the same computer. The SQL Server Database Wizard executes normally, produces no error message, but at its final database creation, its progress bar appears for only a split second and its bar is empty. Looking in the MSDE directory confirms that no database has been created.

 

[Mary Chipman]

I recommend that you purchase the Developer edition of SQL Server and
use it to create and develop your SQL Server database(s). It's $49,
and you get all the client tools (Enterprise Manager, Query Analyzer,
Profiler, etc). You can't do a decent job without these tools in
building and securing a robust SQLS database. Access is not intended
to be used to create SQL Server databases, and neither is MSDE. Use
MSDE when it comes time to deploy the database, not for development.

--Mary

 

[Lou Arnold]

You've made this recommendation before, and I thank you for your concern. However, Microsoft, by their own documentation, states that MSDE is intended to work with MS Access. Since the final result will be used on a small physically isolated network, we have no worries about security and robust databases at this time. The objective is to get past what appears to be a small hurdle and create a beginning enviroment for the developers. If we encounter serious problems we will evaluate other alternatives, but for now...small steps.

Unless you can substantiate your claims that Access is not intended to be used with SQL Server, nor MSDE, we have no choice but to doubt your recommendation.

Please note that at this time we are using only one computer and the Windows Admin account, and that creating databases via Osql and MSDE Plus works. (And all we want at this point is to create a database, not build an application.) So there is something about MS Access and how it connects and interacts with MSDE that is at the crux of the problem. We have queries out to the developers of MSDE Plus to understand how it might differ from MS Access. We nevertheless hope to have clues and a solution from experienced people volunteering their knowledge in this forum.

 

[Mary Chipman]

Unless you can substantiate your claims that Access is not intended to be used with SQL Server, nor MSDE, we have no choice but to doubt your recommendation.

Please read what I said again -- I most emphatically DID NOT say
Access was not intended to be *used* with SQL Server or MSDE as the
front-end to an existing SQLS database. I said that Access is not
intended to be used as a development environment for a SQL Server
database. Don't take my word for it -- verify it yourself simply by
looking at all of the menu items and right-click shortcuts in the
Access UI. Key functionality is definitely not there.

Before you doubt my credentials, please see the following link and
read some of the reviews:
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

--Mary

 

[Larry Linson]

Unless you can substantiate your claims
that Access is not intended to be used
with SQL Server, nor MSDE, we have
no choice but to doubt your recommendation.

Just FYI, Mary Chipman wrote "The Book" on using Access and on using VB with
SQL Server, so she would be highly unlikely to make such a claim. (And I
don't see her doing so in any post to this thread.)

... We nevertheless hope to have clues
and a solution from experienced people
volunteering their knowledge in this forum.

AAMOF, you've had two replies from Mary Chipman, who is one of the handful
of experts to whom the experts in Access turn with Access and SQL Server
issues. If you'd done any homework or research to speak of on the issue,
you'd have known that and perhaps been more polite. I take your language
that I quoted to imply that she is not even an "experienced person" in this
area and consider it highly condescending.

I'd recommend you take a look at http://www.mvps.org/access/netiquette.htm
for good tips and hints on effective use of newsgroups.

Larry Linson
Microsoft Access MVP

 

[Mary Chipman]

Your original question asked why Access doesn't work the way you
want/expect it to, and you detailed everything your tried in your
original message. Workarounds were suggested, which you rejected out
of hand. Nobody knows why it doesn't work, and even if an explanation
were provided, how would that help you when the software still won't
do what you want it to? The MVPs and other people contributing their
time to the newsgroups are geared towards helping people by providing
practical solutions for everyday problems. When a problem is
intractable, then a new approach is called for. As stated previously,
Access is not intended to be a complete design tool for SQL Server
databases, and no amount of sand pounding will make it so.

--Mary

 

[JIMMIE WHITAKER]

I am not a msvp or whatever, but access 2000 works great with msde. I'm new
to it and learning it, and have already converted 2 databases that work
great. I also used the northwindcs example to help me learn. I don't know
the problem you're having, but IT 100% WORKS with no problem in access 2000.
You may just need a re install or something.

 

[JIMMIE WHITAKER]

P.S to last post. Make a regular database, then do an upsize, That is what
I did, with no problem.