Making Query from another DB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I make a query from another Access DB?

I have a browse button that lets you choose another DB.
Using ADO, I can make a query in that DB. If I make one in this DB it cant be
read becase it doesn't contain the original path.

I basically want to create a temp query to look at. What the best way of doing
it?
Create the temp in the other DB or the one I'm running code from?
(e-mail address removed)
 
Spammastergrand said:
How do I make a query from another Access DB?

I have a browse button that lets you choose another DB.
Using ADO, I can make a query in that DB. If I make one in this DB it cant be
read becase it doesn't contain the original path.

I basically want to create a temp query to look at. What the best way of doing
it?
Create the temp in the other DB or the one I'm running code from?
Click to expand...

Hi,

I'm not completely sure of what you want, but....

If you have a table "tbl" in another db "db2"
and you want to query "tbl" in "db2," from
db1 you could use the following query:

SELECT * FROM tbl
IN '' [;DATABASE=C:\db2.mdb];

As John Viescas once pointed out to me,
the simplest way to find out what goes in
the brackets is to link to the table temporarily,
then run the following in Immediate window:

?CurrentDb.TableDefs("tbl").Connect

Of course you must have set reference to
DAO library. In above case it's a "no-brainer,"
but good to know.

One gotcha (maybe) is that if you want to
use this technique for an action query and
use DoCmd.RunSQL, it will not work I am
told because RunSQL cannot contain any
connection information.

This is another good source of ADO Connection info:

http://www.able-consulting.com/ADO_Conn.htm

Apologies if I have misunderstood.

Good luck,

Gary Walter
 
The problem is this. When I use the pulldown to select a database and then
query, it queries that database and table and makes a query in it as well. I'd
like to make a query in this database from info in that database.
(e-mail address removed)
 
Dear Spam:

There are a lot of variables in figuring out what you want, and how to
do it. But here's a possible suggestion.

If the database is in MSDE / SQL Server, you can reference other
databases from within the query. There is a four part syntax to each
table reference that allows you to get well outside the current
database. In addition, you can set up Linked Servers to instances of
MSDE / SQL Server as well as foreign database engines, apparently Jet
databases in your case. Some combination of this, perhaps with the
use of dynamic generation of the SQL, may be a way to make all this
work.

Working within DAO you could either link the tables from other sources
or make references to outside databases within the SQL. This would
not be a form of the ADO I understand as your starting point.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I'm used to working with Excel, pulling data from Access or SQL server into it.
No queries involved, just mining and importation into cells.

Now I'm trying to build a form in Access to let you view tables in other Access
DBs and then I'll eventually work on viewing SQL server tables.

Problem is, when I make the query using currentdb it makes a query, but at
runtime, can't find the table. It just says select * from tablename. Doesnt
include a path.

In the queryview in a regular Access query, how would I write one refering to
another path and DB? "Select * from C:db3.tbale3"?

Here is what the VBA, DAO code looks like so far:

Dim DB As Database, rs As DAO.Recordset
Dim qry As String, FileToOpen As String

'Me.Results.Value = ""
FileToOpen = Me.tSource.Value 'Database path and name

Call DeleteQ9 'delete temp query
qry = "Select * from " & Me.TableBox.Value '& " IN [DATABASE=" &
Me.tSource & "];"
'MsgBox qry
Set DB = DBEngine.Workspaces(0).OpenDatabase(FileToOpen)
Set rs = DB.OpenRecordset(qry, dbOpenSnapshot)
Dim Qname
Qname = "TempQuery9" '
Dim qryDef As QueryDef
CreateQueryDef(Qname, qry)
' Set qryDef = DB.CreateQueryDef(Qname, qry)
Set qryDef = CurrentDb.CreateQueryDef(Qname, qry)
Dim stDocName As String
'OPEN QUERY
DoCmd.OpenQuery Qname, acNormal, acEdit

DB.Close
(e-mail address removed)
 
Spammastergrand said:
The problem is this. When I use the pulldown to select a database and then
query, it queries that database and table and makes a query in it as well. I'd
like to make a query in this database from info in that database.
(e-mail address removed)
Click to expand...

Private Sub cmdOpenQueryToRemote_Click()
On Error GoTo Err_cmdOpenQueryToRemote_Click
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strQryName As String
Dim varTable As Variant
Dim varPath As Variant

'get table and path to remote db
varTable = Me!txtTable
varPath = Me!txtPath

'check that user has defined table and path
If Len(Trim(varTable & "")) > 0 Then
If Len(Trim(varPath & "")) > 0 Then
'check that path exists
If Nz(Dir(varPath)) <> "" Then
'have a table name and valid path
Else
MsgBox "Please check your path."
Exit Sub
End If
Else
MsgBox "Please enter a path to remote db."
Exit Sub
End If
Else
MsgBox "Please enter a name for the table."
Exit Sub
End If

'name of your stored query
strQryName = "qryTemp"

'create new SQL for your stored query
strSQL = "SELECT * FROM " & varTable _
& " IN '" & varPath & "';"
'Debug.Print strSQL

'redefine query
Set qdf = CurrentDb.QueryDefs(strQryName)
qdf.SQL = strSQL
qdf.Close

'now open your stored query with new SQL
DoCmd.OpenQuery strQryName, acViewNormal, acEdit


Exit_cmdOpenQueryToRemote_Click:
Set qdf = Nothing
Exit Sub

Err_cmdOpenQueryToRemote_Click:
MsgBox Err.Description
Resume Exit_cmdOpenQueryToRemote_Click
End Sub
 
Back
Top