• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

Open Recordset

In db access, How to open the two table?

set rs = dbLocal.OpenRecordset("select * from orissued ")
I have another table name collect
I want to call this two table in one time..
Please help..
Thanks
0
Whing Dela Cruz
Asked:
Whing Dela Cruz
  • 4
  • 2
  • 2
  • +2
5 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you need a union;
set rs = dbLocal.OpenRecordset("select * from orissued union all select * from collect ") 
NOTE: your select ... union all select .. in both select parts you must have the same number of columns AND the data types must match!

Open in new window

0
 
Chandan_GowdaCommented:
set rs = dbLocal.OpenRecordset("select * from orissued,collect")
0
 
Whing Dela CruzAuthor Commented:
i'll try it now, thanks

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JackOfPHCommented:
It this two columns has a relationship?
0
 
Whing Dela CruzAuthor Commented:
yes, they are in one database under cashierfile.mdb
0
 
FatEric13Commented:
If there is a relationship between the two tables, you can do as follows:

set rs = dbLocal.OpenRecordset("select * from orissued o left join collect c on [c.***] = [o.***] ")

The [***] must be replaced by the column names from the two tables that have the link.

This will give everything from the first table linked to the matched rows in the second table.
0
 
Whing Dela CruzAuthor Commented:
Okey i will try it now, Thanks
0
 
Whing Dela CruzAuthor Commented:
Hi! everybody i tried this code
set rs = dbLocal.OpenRecordset("select * from orissued o left join collect c on [c.***] = [o.***] ")
but an error appear "Syntax error in join operation"

0
 
JackOfPHCommented:
Try using this sql pattern...

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

You can have the explanation in this site:

http://www.w3schools.com/Sql/sql_join_inner.asp
0
 
FatEric13Commented:
Hi ERWIN,

Build up the statement in a string like
Dim strSQL = "SELECT * FROM orissued o LEFT JOIN collect c ON (o.ColumnName = c.ColumnName)"

Then open the recordset with:

set rs = dbLocal.OpenRecordset(strSQL, dbOpenDynaset)

As JackOfPH said, check the site for which join type you need (inner join, outer join, left join, ...)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>It this two columns has a relationship?
you replied:
>yes, they are in one database under cashierfile.mdb

that does not clarify if you have a relation between the 2 tablese
please clarify the table schema, and the columns that are common (aka the values of them are to be used to "join").

I presumed the tables are just to be appended in the output...

sample data?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now