SQL Table Join?

[Steve Thomas]

following is an example of a table join where both tables are in the same Access database (ONE.MDB.)

Set dbs = Workspaces(0).OpenDatabase(App.Path & "\ONE.mdb")
strSql = "UPDATE Table_1 INNER JOIN Table_2" _
& "ON Table_1.SSN = Table_2.SSN " _
& "SET Table_1.SomeValue = 1, "
& "WHERE (((Table_1.SSN)= 555555555));" _
dbs.Execute strSql
dbs.Close

What I want to do is to create two access databases (ONE.MDB and TWO.MDB). I want ONE.MDB to have TABLE_1 in it and I want TWO.MDB to have Table_2. Now, how do I run the above code to join the tables?

My thought is you do something like this:

Set dbs = Workspaces(0).OpenDatabase(App.Path & "\ONE.mdb")
Set dbs2 = Workspaces(0).OpenDatabase(App.Path & "\TWO.mdb")

But I am not sure how you write the SQL statement?????????

Thanks

[Nias]

See if this helps:
Create your join inside the MSACCESS database and then click the SQL tab to view the SQL code it generates. Then you can cut and paste that into your VB code to get your SQL statement.
I hope that helps!

[JHausmann]

Your example doesn't need a join, try:

Set dbs = Workspaces(0).OpenDatabase(App.Path & "\ONE.mdb")
strSql = "UPDATE Table_1 SET SomeValue = 1 WHERE SSN= '555555555' and SSN IN (select distinct SSN from table2)"
dbs.Execute strSql
dbs.Close

1) What you want is a "View". Access, for every release prior to Access 2000 (and I don't know about 2000), does not support views.

2) SQL server does support views but to update data, you need to make a pass for each table that is being updated. In your example, if you wanted to update table1 and table2, you would have to issue two update statements.

[Steve Thomas]

NIAS - Thanks for the info. However, the problem is that you have to link access to another table and then write the query using the linked table. You can not write a query that points to a database outside of access that is not linked. What I am trying to do is keep the database seperate without links, and still join them.

The reason for this is because I am most likely going to move to SQL server and I want to use something more powerful than Access. As I have never used SQL server before, I am sure there is a way to do this but I don't know how.

JHAUSMANN - The query works just fine the way it is. I simply want to write a SQL string that links two seperate tables together. I am not sure what a view is but I was hopeing I could create two recordsets and then query them???