DAO looking at 2 tables in 2 mdb's and then running sql

[stuartam]

help,

i have 2 database's in each is a table and i need to connect to both with a join and run some sql.

i have the following that connects to each DB but i cant work out how to get it to run the sql.

    Dim dbe As PrivDBEngine
    Dim wrk As Workspace
    Dim dbs As Database
    Dim dbs2 As Database
    Set dbe = New PrivDBEngine
    
    dbe.SystemDB = "c:\wrkgrp.mdw"
    dbe.DefaultUser = "***"
    dbe.DefaultPassword = "***"
    
    Set wrk = dbe.Workspaces(0)
    
    
    Set dbs = wrk.OpenDatabase("C:\Main.mdb")
    Set dbs2 = wrk.OpenDatabase("c:\Info.mdb")
    
    arcsql = "SELECT tbAssetInfo.* INTO tbAssetInfo IN 'C:\Arc.mdb' "
    arcsql = arcsql & "FROM tblErrorInfo INNER JOIN tbAssetInfo ON tblErrorInfo.Uber = tbAssetInfo.Uber "
    arcsql = arcsql & "WHERE (((tblErrorInfo.Status)=121));"
    
    wrk.Execute arcsql

 

[Guus2005]

You are using the wrong syntax. It's INSERT INTO ... SELECT ...
Execute with

Currentdb.execute strSql

It is a lot easier when you just connect the two tables to your own database and work from there.

enjoy!

 

[stuartam]

thanks for the reply.

the query works as its a make table query.

i cant link the 2 tables because they are in workgrouped mdb's and the one that has the above code in isnt workgrouped.

thanks