Access database

[Dammit]

Can someone plz tell me what the easiest way of accessing an Access database is with ASP, preferably with an example. I've tried the MSDN ASP guide and other sources, but none of the things I tried works, probably because they're made up too complicated for me to understand (I just started using ASP, but I want to make all of the code myself to keep it managable)

[QuackHead]

It all depends what you want to do with the access database....

for example, if you have a table with all the names of people in it, you can output them to the page like this:

<%@Language=VBScript%>
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=full_path_to_your_db.mdb"
Conn.Open
Set rsCount = Conn.Execute("SELECT COUNT(*) FROM TABLE_NAME")
If rsCount(0) > 0 Then 'There are actually users in the DB
Set rs = Conn.Execute("SELECT * FROM TABLE_NAME")
rs.MoveFirst
for x = 1 to rsCount(0)
response.write(rs("FIELD_NAME") & "<br>")
rs.MoveNext
next
set rs = Nothing
Else
Response.Write("There are no users in the database.")
End If
Set rsCount = Nothing
Conn.Close
Set Conn = Nothing
%>

That'll get you started...

~Quack

[JoeP]

Good Example Given Above To Get Started. The following is a sample of code I use that may be of some help. It works well with my Access 2000 Database web pages.

This Code Is In My Include File:


'====================================================================
' Database connection string '
Sub OpenDB	
			Set connCP = server.createobject("adodb.connection")
			dbName="/Accounting/Maintenance/Database/CP_Data.mdb"
			connectCP ="provider=microsoft.jet.oledb.4.0;data source="
			connectCP=connectCP & server.mappath(dbName)& ";"
			connCP.open connectCP
End Sub
'====================================================================
'Clean Up
Sub CloseDB
				Set rsCP = Nothing
				Set connectCP = Nothing
				Set SQLstmtCP = Nothing
				connCP.close
				Set connCP = Nothing
				Set dbName = Nothing
End Sub
'====================================================================




This Call Is In A "NEW / EDIT /  DELETE Record Form" Submit page:

'====================================================================
'Capture Data From Vendor Edit / New Form
DIM ChargeID
DIM Ingredient
DIM UnitOfMeasure7Inch
DIM UnitOfMeasure12Inch
DIM UnitCost7Inch
DIM UnitCost12Inch
DIM DateLastChange		
		RecordAction = request.form("RecordAction")
		ChargeID = request.form("ChargeID")
		Ingredient  = ucase(replace(request.form("Ingredient"),"'","''"))
		UnitOfMeasure7Inch = request.form("UnitOfMeasure7Inch")
		UnitOfMeasure12Inch = request.form("UnitOfMeasure12Inch")
		UnitCost7Inch = request.form("UnitCost7Inch")
		UnitCost12Inch = request.form("UnitCost12Inch")
		DateLastChange = request.form("DateLastChange")				
'====================================================================
			Call OpenDB
'====================================================================
If RecordAction = "AddNew" Then 'Add New Record
		SQLstmtCP = "INSERT INTO ChargeTable (Ingredient,UnitOfMeasure7Inch,UnitOfMeasure12Inch,UnitCost7Inch,UnitCost12Inch,DateLastChange)"
		SQLstmtCP = SQLstmtCP & " VALUES ("
		SQLstmtCP = SQLstmtCP & "'" & Ingredient & "',"
		SQLstmtCP = SQLstmtCP & "'" & UnitOfMeasure7Inch & "',"
		SQLstmtCP = SQLstmtCP & "'" & UnitOfMeasure12Inch & "',"		
		SQLstmtCP = SQLstmtCP & "'" & UnitCost7Inch & "',"
		SQLstmtCP = SQLstmtCP & "'" & UnitCost12Inch & "',"
		SQLstmtCP = SQLstmtCP & "'" & DateLastChange & "'"
		SQLstmtCP = SQLstmtCP & ")"
		Set rsCP = connCP.execute(SQLstmtCP)

ElseIf RecordAction = "EditRecord" Then 'Update Edited Record

		SQLstmtCP = "UPDATE ChargeTable"
		SQLstmtCP = SQLstmtCP & " SET Ingredient='" & Ingredient  &"',"
		SQLstmtCP = SQLstmtCP & "UnitOfMeasure7Inch='" & UnitOfMeasure7Inch &"',"
		SQLstmtCP = SQLstmtCP & "UnitOfMeasure12Inch='" & UnitOfMeasure12Inch &"',"		
		SQLstmtCP = SQLstmtCP & "UnitCost7Inch='" & UnitCost7Inch &"',"
		SQLstmtCP = SQLstmtCP & "UnitCost12Inch='" & UnitCost12Inch &"',"
		SQLstmtCP = SQLstmtCP & "DateLastChange='" & DateLastChange &"'"
  		SQLstmtCP = SQLstmtCP & " WHERE ChargeID=" & ChargeID
  		Set rsCP = connCP.execute(SQLstmtCP)

ElseIf request.querystring("RecordAction")  = "DelRecord" Then 'Delete Record
		ChargeID = request.querystring("ChargeID")
		SQLstmtCP = "DELETE * from ChargeTable WHERE ChargeID=" & ChargeID
		Set rsCP = connCP.execute(SQLstmtCP)

End If
		Call CheckErrors
		Call CloseDB
		Response.Redirect "Charges.asp"
'====================================================================