Selecting data from two databases

Can I make a question?
Why are you using 2 MS.Access databases? Why dont you merge it?
I haved bad experiencies with this behavior.
Think about ok? This is only a sugestion.
"Alex Kudrasev" <alexk DeleteThis @art-technology.com.au> escreveu na mensagem
news:ep7RCoSmDHA.964@TK2MSFTNGP10.phx.gbl...
 > Is it possible to select data from two databases (Access)...?
 >
 >
 > I've seen examples where SQL becomes
 > "select database..table.field from database..table"
 >
 > But how could the syntax be used when DAO makes you use the database
 > reference when executing the SQL, ie:
 >
 > dim mydb as database
 > sqltxt = "select distinct xtitle from reports order by xtitle"
 > Set mytable = mydb.OpenRecordset(sqltxt, dbOpenDynaset)
 >
 > Can anybody help???
 >
 > Thanks,
 >
 > Alex Kudrasev
 >
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Selecting data from two databases 

The easiest way would be to have a linked table in database 1 that points to
the table in database 2, and then simply query database 1.

If you don't want to do this, you can query from one database to a table in
another database using:

SELECT CustomerID
FROM Customers
IN OtherDB.mdb
WHERE CustomerID Like "A*";


I haven't tried, but I would assume you could extend this to:

SELECT Customers.CustomerID, Invoices.InvoiceID
FROM Customers
IN OtherDB.mdb
INNER JOIN Invoices
ON Customers.CustomerID = Invoices.CustomerID
WHERE Customers.CustomerID Like "A*";




--
Doug Steele, Microsoft Access MVP
<a style='text-decoration: underline;' href="http://I.Am/DougSteele" target="_blank">http://I.Am/DougSteele</a>
(No private e-mails, please)



"Alex Kudrasev" <alexk DeleteThis @art-technology.com.au> wrote in message
news:ep7RCoSmDHA.964@TK2MSFTNGP10.phx.gbl...
 > Is it possible to select data from two databases (Access)...?
 >
 >
 > I've seen examples where SQL becomes
 > "select database..table.field from database..table"
 >
 > But how could the syntax be used when DAO makes you use the database
 > reference when executing the SQL, ie:
 >
 > dim mydb as database
 > sqltxt = "select distinct xtitle from reports order by xtitle"
 > Set mytable = mydb.OpenRecordset(sqltxt, dbOpenDynaset)
 >
 > Can anybody help???
 >
 > Thanks,
 >
 > Alex Kudrasev
 >
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Selecting data from two databases 

You may try to link a table in one of the databases and it'll do the trick.
raymond

"Alex Kudrasev" <alexk RemoveThis @art-technology.com.au> wrote in message
news:ep7RCoSmDHA.964@TK2MSFTNGP10.phx.gbl...
 > Is it possible to select data from two databases (Access)...?
 >
 >
 > I've seen examples where SQL becomes
 > "select database..table.field from database..table"
 >
 > But how could the syntax be used when DAO makes you use the database
 > reference when executing the SQL, ie:
 >
 > dim mydb as database
 > sqltxt = "select distinct xtitle from reports order by xtitle"
 > Set mytable = mydb.OpenRecordset(sqltxt, dbOpenDynaset)
 >
 > Can anybody help???
 >
 > Thanks,
 >
 > Alex Kudrasev
 >
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Selecting data from two databases 

Except...linking a table will lock in the path of the linked database. So -
can't use that if distributing the application...!

Alex Kudrasev.

Raymond Chiu <rayswchiuUNOTINVALID RemoveThis @netvigator.com> wrote in message
news:eMDMDlVmDHA.2216@TK2MSFTNGP12.phx.gbl...
 > You may try to link a table in one of the databases and it'll do the
trick.
 > raymond
 >
 > "Alex Kudrasev" <alexk RemoveThis @art-technology.com.au> wrote in message
 > news:ep7RCoSmDHA.964@TK2MSFTNGP10.phx.gbl...
  > > Is it possible to select data from two databases (Access)...?
  > >
  > >
  > > I've seen examples where SQL becomes
  > > "select database..table.field from database..table"
  > >
  > > But how could the syntax be used when DAO makes you use the database
  > > reference when executing the SQL, ie:
  > >
  > > dim mydb as database
  > > sqltxt = "select distinct xtitle from reports order by xtitle"
  > > Set mytable = mydb.OpenRecordset(sqltxt, dbOpenDynaset)
  > >
  > > Can anybody help???
  > >
  > > Thanks,
  > >
  > > Alex Kudrasev
  > >
  > >
  > >
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Selecting data from two databases 

Hi Alex!
I use something like that:
sqltxt="SELECT field1, Field2 FROM `C:\OTHERDB.MDB`.Table1 ORDER BY Field2"
Note that the char ` is ASCII 96.
If you have a password protect database use as follow:
sqltxt="SELECT field1, Field2 FROM
`C:\OTHERDB.MDB;USER=Admin;Password=mypass`.Table1 ORDER BY Field2"

Hope be helpful.
João Araújo

"Alex Kudrasev" <alexk.DeleteThis@art-technology.com.au> wrote in message
news:ep7RCoSmDHA.964@TK2MSFTNGP10.phx.gbl...
 > Is it possible to select data from two databases (Access)...?
 >
 >
 > I've seen examples where SQL becomes
 > "select database..table.field from database..table"
 >
 > But how could the syntax be used when DAO makes you use the database
 > reference when executing the SQL, ie:
 >
 > dim mydb as database
 > sqltxt = "select distinct xtitle from reports order by xtitle"
 > Set mytable = mydb.OpenRecordset(sqltxt, dbOpenDynaset)
 >
 > Can anybody help???
 >
 > Thanks,
 >
 > Alex Kudrasev
 >
 >
 ><!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Selecting data from two databases