Switching Tables in ADO

[Ghost21]

When in ADO I can query data from a table , update , insert , etcc... but I can't be in one table like

RAWDATA and then query data from their then query data from NEWUSER table

is there a special command that I need to switch tables ????

Thanks

[PsaltyDS]

When in ADO I can query data from a table , update , insert , etcc... but I can't be in one table like

RAWDATA and then query data from their then query data from NEWUSER table

is there a special command that I need to switch tables ????

Thanks

Why can't you access more than one table at the same time? You're not limited to only one connection object, or one recordset object, etc., so what's the limitation?

Post the ADO script you're trying to use.

[Ghost21]

Why can't you access more than one table at the same time? You're not limited to only one connection object, or one recordset object, etc., so what's the limitation?

Post the ADO script you're trying to use.

Global $oRS

Global $oConn

$oConn = ObjCreate("ADODB.Connection")

$oRS = ObjCreate("ADODB.Recordset")

$oConn.Open ("Driver={Microsoft Access Driver (*.mdb)};Dbq=sw.mdb")

$oRS.Open ("Select * FROM ACTIVE", $oConn, 1, 3)

$oRS.RecordCount

$oRS.Movefirst

For $iIndex = 1 To $oRS.RecordCount

If @error <> 0 Then ContinueLoop

$PC = $oRS.Fields ("PC").value

$USER = $oRS.Fields ("USER").value

$PING = $oRS.Fields ("PING").value

GUICtrlSetData($Combo1, $PC & ":" & $USER & ":" & $PING)

$PC = ""

$USER = ""

$PING = ""

$oRS.MoveNext

Next

[PsaltyDS]

This reads from the "ACTIVE" and "EVENTS" tables at the same time for me, just by using two record set objects:

Global $sMDB = @ScriptDir & "\Test2.mdb"
Global  $oConn, $oRS_Active, $oRS_Events
Global $sMsg = ""

$oConn = ObjCreate("ADODB.Connection")
$oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=" & $sMDB)

$oRS_Active = ObjCreate("ADODB.Recordset")
$oRS_Active.Open("Select * FROM ACTIVE", $oConn, 1, 3)
$iCnt = $oRS_Active.RecordCount
$oRS_Active.MoveFirst

$oRS_Events = ObjCreate("ADODB.Recordset")
$oRS_Events.Open("Select * FROM EVENTS", $oConn, 1, 3)
If $oRS_Events.RecordCount < $iCnt Then $iCnt = $oRS_Events.RecordCount
$oRS_Events.MoveFirst

For $i = 1 To $iCnt
    $sMsg &= $i & ":" & @CRLF
    $sMsg &= $oRS_Active.Fields("PC").value & ", " & $oRS_Active.Fields("USER" ).value & @CRLF
    $sMsg &= $oRS_Events.Fields("EventCode").value & ", " & $oRS_Events.Fields("TimeWritten").value & @CRLF
    $oRS_Active.MoveNext
    $oRS_Events.MoveNext
Next

MsgBox(64, "Test", $sMsg)

Edited January 28, 2008 by PsaltyDS

[redsleeves]

I use this to grab data from two tables at the same time while they have similar keys.

"ON table_1.field4=table_2.field3," in this case, might each contain a username. The data from table_1.field1 and the data from table_2.field2, where the usernames match, will be returned.

$oConn = ObjCreate("ADODB.Connection")
    $oRS   = ObjCreate("ADODB.Recordset")
    $oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq="&$My_Database&"")
    $oRS.Open("SELECT table_1.field1, table_2.field2 FROM table_1 INNER JOIN table_2 ON table_1.field4=table_2.field3", $oConn, 1,3)
        For $l = 1 to UBound($myArray)
            $field1 = $oRS.Fields( "field1" ).Value
            $field2 = $oRS.Fields( "field2" ).Value
            $MyArray[$l] = $field1 & "|" & $field2
            $oRS.MoveNext
        Next
    $oRS.Close
    $oConn.Close