Access 2003 connection to SQL 2000 database

[Jamie]

I have a simple Access2003 database that stores the data locally inside
Access for now but I need to connect it to our SQL2000 database and would
like to know the best method for making this connection? I'm not a database
expert I'm a Network Administrator.

Also any information on a how to or anything would also be helpful. I have
currently tried a ODBC datasource but one of my issues is I have created a
readonly user on the SQL database and I want this Access Database to use
this user credentials no matter what and not prompt me for the credentials.
I also want to use the best defined connection method.

This Access frontend database is a single form that access a single query
and that query currently points to tables in the local database. I know you
can link tables but are these the best methods? I have seen something also
about a SQLpassthrough query? This would elminate the linked table correct?
Again what is the best method for interacting with SQL?

Please help!

 

[Norman Yuan]

If you use Access2003 only as front end to connect to SQL Server, it is
recommended to use Access Project (*.adp file): it is specially designed to
do just that. On the other hand, if you have already gotten some forms, code
in the *.mdb front end, then you may want to use linked table through ODBC,
but you may still need to modify some code in the *.mdb front end, depending
on what the front end does.

From what you descripbed, I'd use *.adp: simple start Access and select
"Project (Existing Data)" (meaning to connect to existing Sql Server
database).

 

[Jamie]

2 questions,
One how do I create a query in the ADP file. I don't want the query created
on the SQL box.

Two how do I save the password for the connection in the ADP file so that it
is seamless for the users? I don't want them to have to reenter this.

 

[Norman Yuan]

See comments inline.

2 questions,
One how do I create a query in the ADP file. I don't want the query created
on the SQL box.

Besides using ADP as SQL Server's front end app, it is also a good tool for
SQL Server database design/management, especially when you use SQL Server
Desktop engine (MSDE). As for query, it is better to use stored procedures
than dynamic SQL statement in front end, not only for performance prupose,
but only for better security control. If you insists not using stored
procedures in SQL Server database, you can only write dynamic SQL Statement
in VBA code, there is no equivalent to *.mdb file's query in *.adp file.

Two how do I save the password for the connection in the ADP file so that it
is seamless for the users? I don't want them to have to reenter this.

When you start *.adp project, you connect it to SQL Server by clicking menu
"File->Connection...". Once connected, the connection setting is saved by
*.adp file automatically. You can design a form as startup form and write
some VBA code under this form to get the adp project connect to specific SQL
Server (using Application.CurrentProject.OpenConnection()/CloseConnection()
methods). If you use Windows Integrated Security to log into SQL Server, you
only need to specify SQL Server name and Database name in ADP's connection
dialog box. Then your users do not need to deal with username/password any
more as long as they are logged on to their computers.