Access to SQL

[MN]

Hello,
I have 1 PC for me to develope MS Access.
I have 1 MS SQL 2K Server.
How do I create a MS Access in my PC then to deploy to the
user PC. So the database is on SQL Server.
How to the user connect to SQL Server and run MS Access?
These questionn are stupid but I very confuse.
Please Help.
Many Thanks...
MN

 

[Cameron Sutherland]

Since you are already using an Access application that is
linked to a SQL server I am going to assume you know at
least a bit about ODBC DSN connections so I will up the
technical language just a bit...
When you develop your Access application to connect to
your SQL server you are currently using an ODBC connection
that you probably made through Control Panel--> Data
Source (ODBC). When you created the ODBC connection object
you made a linked table from it correct? Often people use
the User DSN or System DSN connections (tabs across the
top). User DSN would only allow you on that PC (not good
for other users) but System allows anyone to use it. The
down side is you have to make the exact same ODBC
definition on every users PC and when they open your
Access file that you can copy onto a network or their PC
it looks for the same DSN name. A bit time consuming but
safe. The third option is to make a File DSN and create
the linked table from it. These DSN save their connection
information inside Access so you don't have to make the
ODBC object on every PC that will be using it. The
downside is IT STORES THE USERNAME AND PASSWORD IN A
SYSTEM TABLE OTHERS CAN SEE! If security is not a problem
or if you use trusted connections this is the best
solution.
-Cameron Sutherland

 

[Cameron Sutherland]

First of you will probably want the application to open
your form for the user so they don't see your database
design. From the menu:
Tools-->Startup...-->Display Form-->Select the form you
want to open when the application is opened.

As to how you distribute to users you can either give each
of them a copy of the MDB file or put it on your network
that they will all click on. Since the table is linked
they will all see the same data and your Access MDB is
only a Front-End.

You mentioned that you "tried to connect to SQL". Does
this mean that it didn't work? When you create the ODBC
object to SQL server the last step has a button
called "Test Data Source". When you click on it it will
tell you if you have made it correctly. Again your
response doesn't tell me if you had a problem making that
or when you tried linking to the data in Access. To link
to your ODBC object when it is working go to the tables
tab in design view of your Access applicaiton. Click New--

Link Table-->Under Type Select ODBC Databases-->Select

the ODBC you just made and choose which tables you want to
link to. Once it is linked Access doesn't care if it is a
local table or linked everything (queries, forms,
reports...) acts the same. Does your response mean that
you don't know what to do when it is linked to display the
data? Them set your Record Source of your form to point to
your new linked table and click and drag the fields you
want to show from your Field List onto the form in design
view.

Just give me a bit more information where you are having
problems and I can help you better understand what you are
missing.

-Cameron Sutherland