Reading table from another mdb

[jbaddock]

I'm building this in Access. I have some queries that pull information from tables, that are currently local. But, when this works, will be in two different access databases. This is one of the working queries.

SELECT C.SyteDesc, Sum(-1) AS Total
FROM [SELECT T.SyteDesc
     FROM 
        (SELECT DISTINCT SyteDesc
         FROM SpecLognew GROUP BY SyteDesc) AS T 
    GROUP BY T.SyteDesc]. AS T2 INNER JOIN SpecLog AS C ON T2.SyteDesc = C.SyteDesc
GROUP BY C.SyteDesc;
 

I have a text box set in a form with the path of the other mdb's, but still not working out.
I tried replacing the table names with the path of the db and the table name...

At the moment, I'm here...

SELECT C.SyteDesc, Sum(1) AS Total
FROM [SELECT T.SyteDesc
     FROM 
        (SELECT DISTINCT SyteDesc
         FROM SpecLog
in ' txtOLDMdbPath ' GROUP BY SyteDesc) AS T 
    GROUP BY T.SyteDesc]. AS T2 INNER JOIN Speclog in  txtNewMDBPath ' AS C ON T2.SyteDesc = C.SyteDesc
GROUP BY C.SyteDesc;
 

Was trying to insert the path into the query, so it would remotely query the other db's. Not sure if this is possible.
If anyone has any other suggestions, I'm all ears. I'm going to continue looking into this one though..

Thanks.

[jbaddock]

I simplified it for myself.
Just trying to connect to another mdb file and do a simple select string.

If I go

SELECT SyteDesc
FROM SpecLog
IN 'C:\Test\Testfile.mdb';
 

It works. But how would I make this possible to link it to a variable that contains the file name?

[nico5038]

Personally I would go for using a linked table, as that's also supported by the Tools/Database utilities/Linkedtable manager and allows the user to relink when the backend database is moved, without going into code.

To make it "semi" flexible you could define a Public variable in a module (e.g. named modGlobalValues) and place a variable there with the default location of the database to be used.

Nic;o)

[jbaddock]

That would be nice (I agree with you about the linked table), however the table we are linking to, the database name changes all the time.

Different databases.

What I can't figure out, is how to get the variable into the query.

I do

SELECT SpecLog.SyteDesc
FROM SpecLog IN 'c:\mdb1.mdb'

But if I replace it with a variable, it fails weirdly. Finds the right path to the file, but puts in the name of the variable instead of the file name. If that makes any sense.

[nico5038]

Ran into the same wierd behaviour.
To manage the changing location you can use a dummy query and fill that with the needed location like:

Option Compare Database
Option Explicit
' Globally available constant with the location of the database:
Public Const pbDatabase As String = "D:\Documents and Settings\Nico\My Documents\Access\Access_2007\Northwind 2007.accdb"
 
Function fncDB() As String
' The next Dim needs the "Microsoft DAO 3.## Object Library" to be
' selected in the Tools/References
Dim qd As DAO.QueryDef
 
' A dummy query named qryDummy only needs to be present, the code
' will set the contents
Set qd = CurrentDb.QueryDefs("qryDummy")
qd.SQL = "SELECT * FROM Invoices IN '" & pbDatabase & "';"
 
' ready, qryDummy can be used
 
End Function
 

The code can be expanded to work for multiple databases, e.g. by filling the location from a table.

Nic;o)

[jbaddock]

Thanks, will try this out.
However, it's not quite what I am after, was just trying to keep it within ms access Query wizard, or whatever you wish to call it.

I'll see if I can get it fixed on my end, and I'll post back here, if not, I'll rewrite it so I can do it this way..

thank you :)

[jbaddock]

In my particular instance, I found a solution.
Still used ms access query wizards. But for my database changing all the time, I just made a sub that updated the query.

    sSql = "Select SyteDesc From SpecLog In '" & Me.txtOldMdbPath & "' Where SyteDesc <> Null"
    sSql1 = "Select SyteDesc From SpecLog In '" & Me.txtNewMDBPath & "' Where SyteDesc <> Null"
 
CurrentDb.QueryDefs("qryOldSpecLog").SQL = sSql
CurrentDb.QueryDefs("qryNewSpecLog").SQL = sSql1
 

Which changed the query to point to whatever database I needed it to point to at the time.
So my query would be permanently changed based on what the sub changed it to. Which is fine by me.
This is the result of the query change.

SELECT SyteDesc
FROM SpecLog IN 'C:\1\Old.MDB'
WHERE SyteDesc <> Null;
 

May not be the best use of things, but it takes a really long boring task and simplifies it down to a matter of a few clicks.

Thanks.