Join to a field from an external table

[Reem]

It would be great if someone can help me with that.
I'm trying to join two tables, one of them in an external database, and I didn't find the right syntax for that till now.
Here is my SQL statement:

datEGS.RecordSource =
"SELECT code.description, Session.Name, Session.Date
FROM Session INNER JOIN Code IN 'c:\Prj5\Codetbl.mdb'
ON Session.No = Code.No"

datEGS is a data control (DatabaseName is the current database),
Session is a table in the current database,
and code is a table in an external database Codetbl.mdb

Thanks,

[RSINGH]

Im assuming you're using access as you're joining onto an mdb. Have you tried creating a linked table in the mdb and then joining onto the linked table?

[Reem]

Actually I didn't try that,
I don't want to create a new table that I'll have to delete after I retreive the records that I want.
Is there a way to link the table from the code and this link would be temprorary till I close the database without creating any new tables?
If so please give me a hint.

[RSINGH]

With DAO you can use the transferdatabase method of docmd as below.

DoCmd.TransferDatabase acLink, "ODBC Database", _
	"ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=us_english;" _
	& "DATABASE=pubs", acTable, "Authors", "dboAuthors"

You will have to change some of th eparameteres to point to the correct database and table. Its explained in more detail under access help under the transferdatabase key word .

It creates a reference to the table but does not import the data or create a new table. However it will make your joins easier to write. Just create the link once and leave it there.

[Reem]

I tried that code and it worked.
But in order to use a DoCmd object I got to use an Access object. This is a problem as some of the users won't have The Access installed. I think this function will not work on a machine without the same version of Microsoft Access installed as the one that I'm using.
Correct me if I'm wrong !!
Any other ideas?

[RSINGH]

I'm guessing here but if you use the package and deployment wizard to distribute your app, then it should install the relevant components onto the users machines. Or you may need to get the mdac 2.5 from the microsoft site.

Also, can you clarify the following.

Am I right in thinking that you have two mdb's which you are trying to query against from VB?

[Reem]

That's exactly what I'm trying to do, I have two mdb files that I'm trying to query, I need to get information from the two different tables each one in a different database file in one recordset so I can populate it to a grid. I don't want to modify the values in the grid as it complicates the coding for managing the grid and sorting so much.
I'm not sure if the package and deployment wizard will be able to distribute the files as what happens when I execute the code that it physically opens the Access itself and then closes it again, when I tried to execute this code on Office 2000 it gave me an error that it cannot import a table to an older version of Microsoft Access ( as I'm using Access97 database in the application).
That's my delimma, anyway out?
Thanks a lot for your help.

[RSINGH]

The best way would be for you to link the tables in db1 into db2. Then you only have to access db2 as Access would keep track of which tables are where.

DB2 should be Access 2000 and DB1 should be Access 97 if I've read you correctly.

Do NOT import - use the link option through Access (not code)and keep the link permanent.

I don't want to create a new table that I'll have to delete after I retreive the records that I want.
Is there a way to link the table from the code and this link would be temprorary till I close the database without creating any new tables?

The above should address the above issues except the temporary link. Keep it permanent its better. It will 'appear' as if both tables are now in the same db thus simplifying your JOIN operations.

[Reem]

This is a very good idea, though I'll have to discuss the impact of doing that with the other developers of the application. Also I have to find a way to create this link for users of the old versions upgrading to this version, as the mdb files don't get replaced they are just updated with the changes in order to preserve the data.
About the two mdb files, they are both Access 97. For developing in VB I have to use Access 97 as this is the standard for our project. But some of the users' machines may have Access 2000 or Access 95 loaded on them and others might not have Access at all !!
I think this is my only choice, as joining two tables in an SQL statement one of them from an external database doesn't work.
How to create the table link for user's upgrading without using the DoCmd object? I think this is my challange now.

[Reem]

Here is the code to create a linked table from inside the Vb code without using the DoCmd command:

Sub CreateLinkedTable()
    
    Dim dbsCurrent As Database
    Dim tdfLinked As Tabledef
    
    ' db1 is the destination database, 
    ' where the linked table will reside.
    Set dbsCurrent = DBEngine.Workspaces(0).OpenDatabase("C:\path\db1.mdb")
    Set tdfLinked = dbsCurrent.CreateTableDef("lnkCode")

    ' db2 is the source database, 
    ' where the table "code" resides
    tdfLinked.Connect = ";DATABASE=C:\path\db2.mdb"
    tdfLinked.SourceTableName = "Code"
    dbsCurrent.TableDefs.Append tdfLinked
    
End Sub

All what it requires is a reference to DAO, and the right connection string.