Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / November 2007

Tip: Looking for answers? Try searching our database.

how do i query Multiple Databases in access?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Danc383@hotmail.com - 10 Nov 2007 19:34 GMT
Help…

I need to create an access database that would query Multiple Paradox
Databases

the Paradox Databases all have the same fields.

the purpose of the access database to check all the  Paradox Databases for
any differences from the master and flag and variance with a indicial Paradox
Database.

Dan
:)
MGFoster - 10 Nov 2007 23:02 GMT
> I need to create an access database that would query Multiple Paradox
> Databases
[quoted text clipped - 4 lines]
> any differences from the master and flag and variance with a indicial Paradox
> Database.

Basically, you're talking about replication.  I don't believe Access can
do that w/ Paradox files.  The alternative is to run a query that checks
each individual row/column (record/field) value and show rows that are
different.  You'd have to use a unique identifier (Primary Key) to
identify each row to be compared.

I'd create a VBA routine (using the TransferDatabase method of the DoCmd
object - see the VBA Help file on TransferDatabase method for examples)
that attaches each Paradox data file to the Access front-end, one at a
time, and run the comparison query.

The query would look something like this, if there were 4 columns
(primary_key, col1, col2, col3) in the comparison tables:

SELECT COUNT(*) AS RowAnomolies
FROM MasterTable As A INNER JOIN AttachedTable As B
  ON A.primary_key = B.primary_key
WHERE A.col1 <> B.col1
OR A.col2 <> B.col2
OR A.col3 <> B.col3

You'd change the names of the tables and the columns to whatever your
tables and column names were.  You'd change the names and number of
columns in the WHERE clause (IOW, be sure to compare all columns).  The
primary key can be more than one column so be sure to include all of the
PK's columns in the JOIN's ON clause.

If the query returned a value > 0 then you'd know that the AttachedTable
didn't match the MasterTable.
Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

Danc383@hotmail.com - 11 Nov 2007 18:41 GMT
> > I need to create an access database that would query Multiple Paradox
> > Databases
[quoted text clipped - 37 lines]
> If the query returned a value > 0 then you'd know that the AttachedTable
> didn't match the MasterTable.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.