Access Queries on an Intranet

  • Thread starter Thread starter LW
  • Start date Start date
L

LW

We have been trying to display our Access Database
information on our Intranet, and enable users to search by
different fields. We do not want anyone to update through
the Intranet. The updating is done directly by one person
into the Access file. Something seemingly easy has been a
nightmare. This is what we have been trying:

1. Using Front Page's Database Results Wizard, we have had
to first import our database into our Intranet web, and
then we can see records, and search etc. However since
this database is updated elsewhere, we can't keep
reimporting the file to keep the info dynamic, and it will
not open the imported Access file in its web location for
editing.

2. Using Access's data access page feature, we have been
able to get our records to show up on the Intranet and be
connected to the underlying file. In addition we have been
able to seal records from user editing, but the problem is
that only search capabilities are a weak filter by
selection that does not do wildcard searches (such as one
word in a description field).

3. In our Access database we have a perfect setup of
parameter queries so that the user inputs all or part of a
reference and gets a nice report. When we get this into
our web thru a data access page, the parameter form does
come up, but after filling it in, records never show in
the report.

It seems we have come up against specific stumbling
blocks. For instance, does anyone know how to set up a
hyperlink on a page created in Front Page that would just
bring up and transfer control to the Access file? Right
now, "Quick View" gibberish comes up.

Does anyone know how can I reference an Access database
outside of the Intranet in a file such as global.asa? It
only appears to recognize http:// and not network letters?

Any help would be appreciated since we have been pulling
our hair out!
 
I did some nice searching in a DAP once.
The backend was SQL Server, but the idea is the same.
I used 4 cbos as query criteria.

See if this helps:

Create a Stored Procedure that accepts a search term:

CREATE Procedure dbo.uspName @searchtext VARCHAR(2000)
AS
SELECT ID, Field1, Field2, Field3
FROM MyTable
WHERE Field1 Like '%' + @searchtext + '%'
OR Field2 Like '%' + @searchtext + '%'
OR Field3 Like '%' + @searchtext + '%'

--the contains clause below is used for Full Text Search. Replace where
clause if FTS is enabled.
--WHERE CONTAINS(*,@searchtext)
ORDER BY ID
RETURN
GO

'Create a textbox to enter the search term and a button named btnSearch to
call this code:

<SCRIPT language=vbscript event=onclick for=btnSearch>
If txtSearch.value <> "" Then
FindText()
Else
MsgBox("Please enter a search string.")
End If
</SCRIPT>

<SCRIPT language=vbscript>
Sub FindText()
Dim rst
adUseServer=2
adOpenStatic=3
adLockOptimistic=3

Set rst = CreateObject("ADODB.Recordset")

rst.CursorLocation = adUseServer
'call a stored procedure that takes a parameter
rst.Source = "uspName '" & txtSearch.value & "'"
rst.ActiveConnection =
MSODSC.DataPages.Item(0).Recordset.ActiveConnection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.Properties("Unique Table").Value ="MyTable"
rst.Open

'I have a few cbos that filter the original recordset. I want the
existing filter to apply to the search
'results above. If you are just searching the entire table, then you can
omit this.
Dim strWhere
If cboID.value <> "" Then
strWhere = strWhere & "ID=" & cboID.value & " AND "
End If
If cbo2.value <> "" Then
strWhere = strWhere & "Field2ID=" & cbo2.value & " AND "
End If
If cbo3.value <> "" Then
strWhere = strWhere & "Field3ID=" & cbo3.value & " AND "
End If
If cbo4.value <> "" Then
strWhere = strWhere & "Field4ID=" & cbo4.value & " AND "
End If

If strWhere = "" Then
rst.Filter = ""
Else
strWhere = Left(strWhere, Len(strWhere) - 5)
rst.Filter = strWhere
End If

'swap out the original bound data with the search results
MSODSC.SetRootRecordset "MyTable", rst

End Sub
</SCRIPT>

'Create a Reset button to re-load the original page. (Simplest method for
starting over).
<SCRIPT language=vbscript event=onclick for=btnReset>
're-load the page.
window.navigate("MyDAP.htm")
</SCRIPT>
 
Back
Top