How do I use Access 2003 with SQL Server Express?

[NGM MS Office]

I have SQL Server Express installed and am trying to create an Access 2003
project. When Access asks for an SQL server I specify (local) however I get
the following error message:


Microsoft SQL Server Login

Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist
or access denied.

My ultimate goal is to create a website with Visual Web Developer 2005 with
a parametric filter (search engine) to pull records from an SQL database that
was created via Access 2003.

 

[Brendan Reynolds]

Access 2003 is probably not the right tool to choose for developing SQL
Server Express databases. If you really want to use Access to design the
database, you could try using Access 2007. I have done some very basic tests
and have verified that I am able to create tables in a SQL Server Express
database using Access 2007 Beta 2 TR, but I have not done any more extensive
testing than that.

Alternatively you could use Access 2003 and MSDE instead of SQL Server
Express during development and transfer the database to SQL Server Express
when deploying it to the production server.

Alternatives to Access include the Visual Studio 2005 built-in database
designers, and SQL Server Management Studio Express, available as a free
download from Microsoft's web site.

 

[NGM MS Office]

Is MSDE (Microsoft Database Environment) yet another semi-compatible storage
engine to compete with Access and SQL or is MSDE the name for Access's
database engine?

I'll post a mockup of what I'm looking to create and hopefully that will
make my questions pertaining to the proper tools to use a little easier to
address.

Thanks

-Nathan

 

[pvdg42]

Is MSDE (Microsoft Database Environment) yet another semi-compatible
storage
engine to compete with Access and SQL or is MSDE the name for Access's
database engine?

MSDE is the barebones edition of SQL Server 2000. No GUI tools and a limit
on database size.
The name of the Access database engine is Jet.

 

[Brendan Reynolds]

MSDE (I believe the 'E' stands for 'Engine' and not 'Environment') is to SQL
Server 2000 as SQL Server Express Edition is to SQL Server 2005. Because it
is an older version of SQL Server, it is compatible with current versions of
Access. So far as I am aware, I don't think you should have any significant
problems transferring the MSDE database to SQL Server Express for
deployment, though you might want to ask about that in a SQL Server forum.

Obviously, though, you're not going to be able to take advantage of new
features of SQL Server Express that way, so either the VS.NET tools or SQL
Server Management Studio Express might be a better choice for designing SQL
Server Express databases.

I'm not sure which editions of Visual Studio do or do not include the
database designers, or which edition you're using, so I can't be sure
whether you have access to those tools or not. But SQL Server Management
Studio Express is a capable tool, available to anyone, and it's free.

 

[John F]

In response to the original post, I found that just using the instance name
gives the error message noted. The machine name followed by instance
connected ok - e.g machinename\SQLEXPRESS.

My question-

Using upsizine wizard to work with SSE 2005, all seemed ok, but I get two
messages 1) when opening a table in Design Mode, message

This version does not support design changes with the version of SQL Server
your access project is connected to. See the Office Update Web site for
latest info on downloads. Your design changes will not be changed.

Then another window

You have connected to a version of SQL Server later than SQL Server 2000.
.... you might encounter problems. Pls check to see if there is a Service
Pack that you should apply to Visual Studio or Office....

I did not see any download that appeared obvious for this. Any help?

Thanks.

John

 

[Brendan Reynolds]

There is no update that adds design support for SQL Server 2005 to Access
2003.

 

[John F]

Thanks Brendan,

I guess my major options are to 1) use Access 2003 with SQL Server 2000 or
an MSDE version, or 2) Drop Access 2003 and use instead Visual Studio either
Express or full version.

I have the impression that if I want the control of using unbound controls
on forms, then I have to code anyway and there does not seem to be any major
advantage in using Access 2003 over Visual Studio and coding in VB.NET or C#.
Do you agree?

Thanks,

John

 

[Brendan Reynolds]

The original question was, as I understood it, about designing a SQL Server
2005 database using Access 2003. This question seems to be about which tool
to use to build a front-end application, which is a very different
discussion.

Generally speaking, for designing data-centric desktop applications, I think
Access still has the edge. It's true that if you use unbound forms you will
give up many of the advantages of using Access (so make sure you have a
really good reason for doing so) but even so you'll still probably have to
write considerably less code in Access than you would in .NET. If you're
developing a Web application, then of course you should use a tool designed
for developing Web applications.

In Access, you're only choice for Web applications is Data Access Pages.
These are now deprecated in Access 2007 (you can run existing DAPs, but can
not make design changes to them or create new ones in Access 2007). So I
certainly would not recommend Access for developing Web applications. For
Web applications, ASP.NET is a great choice - provided, of course, that you
don't work for an organisation that has standardised on UNIX and JSP!

This of course begs the question of when to use a Web application and when
to use a desktop application, but perhaps that's a discussion for another
day!

 

[John F]

Thanks again for the response and for your insight into the question. Yes, I
did bring up a different topic in the same thread. At the time I thought
they were related in that if Access could not work with the current version
of SQL Server, maybe I should consider a different front-end.

Another point favoring the use of Access is that with a new application
keeping the forums bound initiallly serves as a high-leverage RAD tool. Once
the application stabilizes, if necessary the forms can be unbound and the
code written at that point rather than now. Not much inefficiency - write
code later instead of now, and not much lost.

Will probably use SQL Server 2000 to have the graphical management tool that
MSDE lacks. From all that I have heard, Access 2003 and SQL Server 2000
should work fine together.