Please help me with connection

[Al]

I am using Access 2002 and I upsized a db to SQL. I am
using the MSDE version. I placed the upsized db on the
network and I am trying to connect to it using ODBC. What
I need to do is having Access as a front end and SQL as a
back end. The problem I am having is that in the Server
box I only see (local) and the name of my Machine which of
course will mean (local) as well. How can I connect to the
file on the server of the network. The network server does
not have MSDE or Access installed it only has the back end
file. can someone help?
thanks
Al

 

[Graham R Seach]

Al,

If you've upsized your database from Jet to SQL Server, then you must have
MSDE or SQL Server installed somewhere. You cannot "connect" to an SQL
Server file (the mdf or ldf ) - only to a database that is registered on a
database server. If as you say, the server does not have MSDE (or SQL
Server) installed on it, then you're probably out of luck, unless you have
registered the mdf in your (local) server.

Using SQL Server is not the same as using Access. You can't just drop the
mdf/ldf files in a directory somewhere and expect to use them the same way
you used an mdb (Jet) file. It doesn't work that way. Install SQL Server or
MSDE on the server, then use Enterprise Manager (or some other database
management tool) to register the mdf on that database server. Once you do
that, you'll be able to access the database from anywhere in your
organisation, provided you have permissions to do so.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Al]

I have MSDE installed on my workstation and I did
everything on my workstation. The server does not have the
application MS Access not the MSDE installed on it. I
thought it could act as a file server if everyone has
Access and MSDE installed on their workstations, they can
access the files on the server. However, if I hear you
right, you are saying that I need to install Access and
MSDE on the server, do all the work their then everyone
can access the db.

I am eventually, trying to have SQL backend with Access
front end. I split the db first then upsized the back end
using MSDE I linked all the tables to the front end and
everything is working fine on my machine,of course,
because it is treated as the server. Am I on the right
track toward what I want to do? do I need to install any
other versions of SQL beside the MSDE? My client would not
to pay for any other versions of SQL. They want to use
MSDE because it is free.
thanks
Al

 

[Graham R Seach]

Al,

No, if users have MSDE on their workstations, they can access the mdf on the
server, but everyone'll need some way of registering the mdf with their
instance of MSDE. I'd suggest using Enterprise Manager, or the osql (command
line) utility.

But the better option is to install MSDE (and the mdf) on the server, and
have MS-Access on the workstations connect to it.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Robert]

Although I believe you can point sql server to use
database files on a different machine, I doubt you will
find the performance acceptable.
Best to install sql server where the databases are going
to live.

If you insist , try using a mapped network drive or the
unc directly
//othermachine\c\directory\db.mdf