Re: Access Queries on an Intranet

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Joe Fallon (jfallon1_at_nospamtwcny.rr.com)
Date: 02/21/04 

Date: Sat, 21 Feb 2004 01:08:46 -0500

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> 

-- 
Joe Fallon
Access MVP
"LW" <anonymous@discussions.microsoft.com> wrote in message
news:11eb401c3f662$a4cef3c0$a101280a@phx.gbl...
> 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!
>
Quantcast