How do I tie a Sql Server backend to a Web or Access front-end

[HotGeek]

Hi, I need to understand how do I tie a sql server backend to a web-based or Access97/2002 front end. I know this is a stupid and easy question but I need an explanation of how this is done. Thank you

 

[flybravo]

There are a couple of ways to tie a SQL-Server to access.

1. create a ODBC, system DSN to SQL Server
Contral panel, either a icon will say ODBC or data sources or double click on administrative tools, then double click ODBC
2. CLick on the System DSN, click on ADD button
3. cycle to the bottom of the list to SQL Server.
4. CLick on SQL Server and click on the finish button
5. Type in any name you and click the down arrow on the server box. this will show a list of any SQL Servers that is on your network.
6. depending on the setup of teh security on SQL, you will use either of the radio buttons
7. Click next after you fill out the security information
8. set the default databse
9. Click next then finish

Open Access
1. create a new database, on the tables section right click on click on the link table
2. for the file type use ODBC Databases, click the Machine Data Source Tab and click on the name you type into the ODBC box.
3. CLick OK and you are connect to the SQL Server

 

[ecobb]

To put it on the web you'll just need to build a site using a server side language such as ASP, ColdFusion, etc...

Hope This Helps!

Ecobb

"Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer." - Homer Simpson

 

[soylentgreen]

OK, all this works fine & dandy when the Access db is local, but it breaks when I upload the Access front end to the website.
The SQL server db is already on the website. Here is the code I am using for my ASP pages:
DIM Connect2
SET Connect2=Server.CreateObject("ADODB.CONNECTION")
DSNTemp="Driver={Microsoft Access Driver (*.mdb)}; "
DSNTemp = DSNTemp & "DBQ=" & server.mappath("testconnectionSQL.mdb")
connect2.Open DSNTemp

SET onwash=Connect2.Execute("SELECT * FROM qryagents")

Any suggestions would be appreciated. Thank you!

 

[TheBugSlayer]

Move the access DB to a server that is accessible from the web host and link the SQL tables from that box.

Do you really need to have two layers of databases? Why can't you just move the Access tables that you don't have in SQL Server and use the latter as the sole back-end?