Union data from two databases

  • Thread starter Thread starter Steven
  • Start date Start date
S

Steven

I have too many records in my database and want to store
some of the history in a second database. I want to be
able to access the data in the history database and query
on certain criteria like ClientId = "?????" and Union this
with the current data from the main table in the current
database. I have a little experience with workspaces, if
that is what you use, but I do not see how to union data
from different databases and create a recordset to use as
a controlsource for a form or report.

Thank you for your help.

Steven
 
Steven-

A simple way is to use the IN clause directly in the SQL:

SELECT *
FROM tblClientHistory
WHERE ClientID = 555
UNION
SELECT *
FROM tblClientHistory
IN "C:\MyDatabases\ArchivedHistory.mdb"
WHERE ClientID = 555

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Thank you for the response. And if the history database
has a password then I can just pass the password in the
sql statement with the
IN "C:\MyDatabases\ArchivedHistory.mdb ; PWD=joven1"
or something like that.

Thanks.

Steven
 
Close. I find it's easiest to link the table and then look at the Connect
property of the linked TableDef. Try this IN clause:

IN "" [MS Access;PWD=joven1;DATABASE=C:\MyDatabases\ArchivedHistory.mdb]

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top