Connecting to multiple tables

Results 1 to 3 of 3

Thread: Connecting to multiple tables

  1. #1
    Yog Guest

    Default Connecting to multiple tables

    I have a search text box and i need to search more than 5 tables for the value in the text box(and obviously all the fields in the tables).How do i frame my query accordingly...<BR>i am relatively new to asp....please guide me on the same.

  2. #2
    Join Date
    Dec 1969

    Default RE: Connecting to multiple tables

    Hi.<BR><BR>A simple way to do this is to connect 5 times (one for each table) and return a recordset for each one (of that is what you want).<BR><BR>Or, if possible, use the JOIN-command in the SQL-string, but if you are a beginner that might not be the best thing.<BR><BR>A third way to do it (if you are using an Access-database) is to make a Question in the database that you call and that Question search all the tables for the word(s) you are looking for. I have never tried this tho so I dont know if It works....but that is not a good solution for a beginner.<BR><BR>Hope you can use some od what I wrote.

  3. #3
    Join Date
    Dec 1969

    Default RE: Connecting to multiple tables

    Anax has a made good suggestions, I am only going to expand on one of them. If you are not confortable using SQL in your code, or not sure, then simply do as Anax suggested and create a query in Access to do what you want. Once you have the query in Access working the way you want and you can see the data, you can call the query directly from your .asp page and get the recordset. you will need to create a command object. Here is small sample of a query that takes parameters.<BR><BR>Const adCmdStoredProc = 4<BR>Const adcmdText = 1<BR><BR>Set objConn = CreateObject("ADODB.Connection")<BR>objconn.Open "DSNname"<BR>Set objCmd = CreateObject("ADODB.Command")<BR>Set objCmd.ActiveConnection = objConn<BR>objCmd.CommandText = "Qry_YourQueryName(&#039"&datBeginingDate&"&#039,& #039"&datEndingDate&"&#039)"<BR>objCmd.CommandType = adCmdStoredProc<BR>set objRs = objCmd.Execute<BR><BR>Then loop through the recordset. Now I write many many little vbs functions to do access database maintenance for clients, this was cut from one of those samples. This query takes two parameters, a start and end date...if your query DOES NOT need parameters then cut the part between "(" to ")" from the commandtext section. Otherwise put some in :)<BR><BR>Be sure to close your connections at the end of your code.<BR>Set connectionName = Nothing<BR>Or Access will be kept open and server performance will drop.<BR><BR>Hope that helps

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts