Linking to mdf SQL Server Express database

[simonc]

Is there a way I can get into Access links to tables in a SQL Server Express
database (.mdf file)? I have tried Get External Data>Link tables but .mdf is
not one of the available file type options. I tried experimenting with
choosing the option ODBC Databases() but this leads into questions about
selecting the driver I want to connect to, which I'm afraid I don't properly
understand.

Grateful for advice.

I am using Access 2002.

 

[Rick Brandt]

Is there a way I can get into Access links to tables in a SQL Server
Express database (.mdf file)? I have tried Get External Data>Link
tables but .mdf is not one of the available file type options. I
tried experimenting with choosing the option ODBC Databases() but
this leads into questions about selecting the driver I want to
connect to, which I'm afraid I don't properly understand.

Grateful for advice.

I am using Access 2002.

SQL Server data has to be "served" to Access by a running SQL Server
instance using ODBC or other driver types which can connect to the service.
There is no data access to the MDF files themselves except by SQL Server.
That pretty much defines how a "server database" must work.

 

[Stefan Hoffmann]

hi,

Is there a way I can get into Access links to tables in a SQL Server Express
database (.mdf file)? I have tried Get External Data>Link tables but .mdf is
not one of the available file type options.

This is not possible. You need a running SQL Server (Express) instance
which uses this file to provide access to this database. Then you can
link a ODBC source pointing to this server\database.


mfG
--> stefan <--

 

[simonc]

Thanks for your help. Sorry if my next question seems very basic, but how do
I create a running SQL server instance?

(I created the mdf file following instructions in one of Microsoft's SQL
Server Express Video Tutorials, using Visual Web Developer Express. Does
doing this somehow initiate a SQL Server?)

 

[Stefan Hoffmann]

hi,

Thanks for your help. Sorry if my next question seems very basic, but how do
I create a running SQL server instance?

Have you installed SQL Server Express and SQL Server Managment Studio
Express ?

Open the SSMS(E). Select your instance. Right click on the database node
in the object explorer, select attach database.
Otherwise open a new query and execute

sp_attach_db
N'databaseName',
N'pathAndFileNameToMdf'


This should attach your file.


mfG
--> stefan <--

 

[a a r o n . k e m p f]

WRONG ANSWER

it's called a USER CONNECTION

SQL Server data has to be "served" to Access by a running SQL Server
instance using ODBC or other driver types which can connect to the service.
There is no data access to the MDF files themselves except by SQL Server.
That pretty much defines how a "server database" must work.

 

[a a r o n . k e m p f]

WRONG

it's called a 'user connection'

you don't have to register MDFs with a service _ANY_ Longer.

(unless you choose to).

-Aaron

 

[a a r o n . k e m p f]

http://www.connectionstrings.com/?carrier=sqlserver2005

Using an User Instance on a local SQL Server Express instance
The User Instance functionality creates a new SQL Server instance on
the fly during connect. This works only on a local SQL Server 2005
instance and only when connecting using windows authentication over
local named pipes. The purpose is to be able to create a full rights
SQL Server instance to a user with limited administrative rights on
the computer.
Data Source=.\SQLExpress;Integrated Security=true;AttachDbFilename=|
DataDirectory|\mydb.mdf;User Instance=true;

of course.. this might be more appropriate:
Standard Security

Provider=sqloledb;Data Source=myServerAddress;Initial
Catalog=myDataBase;User Id=myUsername;Password=myPassword;

or this

Trusted connection

Provider=sqloledb;Data Source=myServerAddress;Initial
Catalog=myDataBase;Integrated Security=SSPI;

 

[Stefan Hoffmann]

Using an User Instance on a local SQL Server Express instance

Oops, what happend, got brain? Almost an answer worth considering...

mfG
--> stefan <--

 

[Larry Linson]

Oops, what happend, got brain? Almost an answer worth considering...

Excuse me, but for there to be a "local SQL Server Express instance", does
SQL Server not have to be installed, as you and Rick have explained, but to
which fact Mr. Kempf appears to have taken exception? If so, the "almost"
in your response is the operative word.

Larry Linson
Microsoft Office Access MVP

 

[simonc]

Boy, what have I started here!!

Anyway, I don't (yet) have SQL Server Managment Studio Express installed, so
I'll sort that and see if it all becomes clearer.

Thanks for the repsonses.

 

[Larry Linson]

_You_ didn't start anything... Mr. Kempf is a strong advocate of SQL Server,
so strong that he [often|usually|always] allows his advocacy to override
good judgement and promotes SQL Server whether or not it might be helpful.
He also tries so hard to find flaws with Access/Jet/ACCDB that sometimes he
finds flaws that don't even exist. He's not going to go away, but it's
neither your nor our fault that he comes here to stalk, mislead, and
disparage.

Larry Linson
Microsoft Office Access MVP

 

[a a r o n . k e m p f]

SQL Server comes installed by default with most of the Visual Studio
applications.
Every serious database developer should have SQL Server installed
locally.

SO sorry that Next, Next, Yes, Next is too complex for you Larry.

-Aaron