Opening 2 or more tables?

[belvita]

How can I open two tables at once,
I tried this code but it didn't worked for me:

Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.CursorType = adOpenStatic
    rs.LockType = adLockReadOnly
    rs.Open "SELECT * FROM avatar,singleplayername", conn, adOpenStatic, adLockOptimistic
    
    swfUserProfile.SetVariable "txtPlayerName", rs!playerName
    swfPictureAvatar.LoadMovie 1, rs!avatarFile
    swfPictureAvatar.Movie = rs!avatarFile
    
    rs.Close
    Set rs = Nothing

Need Help

[GaryMazzone]

Sorry each table needs a sperate select statement. Are they related in some way?

[belvita]

They came from the same database, how can I fix it?

[GaryMazzone]

You write two select statements. Or if the tables are related in someway (they hold information about someone some stuff in one table and other stuff in the other) then you do a select from both tables using an Inner Join.

[belvita]

Can I have an example of using select statement with Inner Join?

Thanks. Sorry for bothering.

[si_the_geek]

You can, but we need information from you first.. what fields are in each table? which field(s) are used to link them together?

It would probably help if you show a couple of example rows of data from each table.

[belvita]

These are the fields for the avatar table:
1. avatarNumber
2. avatarName
3. avatarSize
4. avatarFile <----- Location only

These are fields for the singleplayername table:
1. playerNumber
2. playerName

I'm just trying to call them both all at once at the Form_Load()
So the user can choose his or her name and avatar.

[si_the_geek]

Ok, there appears to be no related data at all - what we expected to see was something like the Player table having a field (perhaps called avatarNumber) which refers to a specific row in the Avatar table.

As that isn't the way you have it, a Join is not apt. Instead you should open two separate Recordsets, one for each table.

[nick2k3]

Ok, there appears to be no related data at all - what we expected to see was something like the Player table having a field (perhaps called avatarNumber) which refers to a specific row in the Avatar table.

As that isn't the way you have it, a Join is not apt. Instead you should open two separate Recordsets, one for each table.

hey i remember that you can do multiple select right??? that is why there is "NEXTRECORDSET" procedure on ADO?? i dont know if this is correct...

but how to do this i do not remember...try putting this ";" after each statement...

[si_the_geek]

That would be possible but wouldn't really gain much, it would just mean that there was only one recordset variable (with separate code blocks) instead of two - I think belvita would find it less confusing to have two separate ones.

[nick2k3]

That would be possible but wouldn't really gain much, it would just mean that there was only one recordset variable (with separate code blocks) instead of two - I think belvita would find it less confusing to have two separate ones.

hahaha...im just going with the idea of the two SELECT statements. coz i just remembered that it was kinda' possible..im sorry to have mislead.

[si_the_geek]

There are times when it is a good idea (otherwise it wouldn't be an option!), but in my opinion this isn't one of them.. it is debatable tho, so mentioning it wasn't a bad thing.

[belvita]

Two select statement? like this one?

rs.Open "SELECT * FROM avatar"
rs.Open "SELECT * FROM singleplayername"

Is this the right syntax?

I'd tried this but it didn't worked for me.

[si_the_geek]

Something like this:

      Dim rs As ADODB.Recordset
          Set rs = New ADODB.Recordset

          rs.Open "SELECT * FROM singleplayername", conn, adOpenForwardOnly, adLockReadOnly
          swfUserProfile.SetVariable "txtPlayerName", rs!playerName
          rs.Close

          rs.Open "SELECT * FROM avatar", conn, adOpenForwardOnly, adLockReadOnly
          swfPictureAvatar.LoadMovie 1, rs!avatarFile
          swfPictureAvatar.Movie = rs!avatarFile         
          rs.Close

          Set rs = Nothing

Note however that as you are not using a Where clause with the SQL statements, you cannot tell which record will be read from each table (assuming each table has more than one record).