how to query tables together from different databases

[Southern at Heart]

I'm needing to update a table in another database with data from a table in
the current database
I think I've figured out how to open the other database, like this:
....
Set dbsBook = DBEngine.Workspaces(0).OpenDatabase(strFileName)
....

I think I can open the table in it for editting, like this:
....
Set rst = dbsBook.OpenRecordset("tblName")

But Here's my problem. I'm needing to update it with data from a table in
the current database that I need joined to it before I know what to update.
I mean, I've got one table in the current database, and this tblName in the
other database, and they both have a Key, the field

 which matches them
up.  So I need to query them both via that Key, and open that as a recordset,
and then update the other database's table with data from the current
databases table.
I've opened a recordset using two tables in the current database like this:
....
strSql = "SELECT tbData.Name, tblContacts.HomeAddressStreet " & _
"FROM tbData, tblContacts " & _
"ORDER BY tbData.Name;"

Set rst = CurrentDb().OpenRecordset(strSQL)


....But I don't know how to do it when one table is in another database.

Is this possible?
Totally Stumped

 

[tkelley via AccessMonster.com]

I'm a simple man. So I may be missing something. Please explain why you're
not just creating a link to the external table and then using it just as you
would a local table.

I'm needing to update a table in another database with data from a table in
the current database
I think I've figured out how to open the other database, like this:
...
Set dbsBook = DBEngine.Workspaces(0).OpenDatabase(strFileName)
...

I think I can open the table in it for editting, like this:
...
Set rst = dbsBook.OpenRecordset("tblName")

But Here's my problem. I'm needing to update it with data from a table in
the current database that I need joined to it before I know what to update.
I mean, I've got one table in the current database, and this tblName in the
other database, and they both have a Key, the field

 which matches them
up.  So I need to query them both via that Key, and open that as a recordset,
and then update the other database's table with data from the current
databases table.
I've opened a recordset using two tables in the current database like this:
...
strSql = "SELECT tbData.Name, tblContacts.HomeAddressStreet " & _
"FROM tbData, tblContacts " & _
"ORDER BY tbData.Name;"

Set rst = CurrentDb().OpenRecordset(strSQL)

...But I don't know how to do it when one table is in another database.

Is this possible?
Totally Stumped[/QUOTE]

 

[Chris O'C via AccessMonster.com]

No need for a recordset. Link to the table in the other db and create an
update query with that. Use inner join syntax, not a cartesian join.

Chris
Microsoft MVP

I'm needing to update a table in another database with data from a table in
the current database
I think I've figured out how to open the other database, like this:
...
Set dbsBook = DBEngine.Workspaces(0).OpenDatabase(strFileName)
...

I think I can open the table in it for editting, like this:
...
Set rst = dbsBook.OpenRecordset("tblName")

But Here's my problem. I'm needing to update it with data from a table in
the current database that I need joined to it before I know what to update.
I mean, I've got one table in the current database, and this tblName in the
other database, and they both have a Key, the field

 which matches them
up.  So I need to query them both via that Key, and open that as a recordset,
and then update the other database's table with data from the current
databases table.
I've opened a recordset using two tables in the current database like this:
...
strSql = "SELECT tbData.Name, tblContacts.HomeAddressStreet " & _
"FROM tbData, tblContacts " & _
"ORDER BY tbData.Name;"

Set rst = CurrentDb().OpenRecordset(strSQL)

...But I don't know how to do it when one table is in another database.

Is this possible?
Totally Stumped[/QUOTE]

 

[Southern at Heart]

....a link to the external table...
That sounds just like what I need (I'm a VERY simple man; self taught)
I'll have to figure out how you do that....
southernatheart

I'm a simple man. So I may be missing something. Please explain why you're
not just creating a link to the external table and then using it just as you
would a local table.

I'm needing to update a table in another database with data from a table in
the current database
I think I've figured out how to open the other database, like this:
...
Set dbsBook = DBEngine.Workspaces(0).OpenDatabase(strFileName)
...

I think I can open the table in it for editting, like this:
...
Set rst = dbsBook.OpenRecordset("tblName")

But Here's my problem. I'm needing to update it with data from a table in
the current database that I need joined to it before I know what to update.
I mean, I've got one table in the current database, and this tblName in the
other database, and they both have a Key, the field

 which matches them
up.  So I need to query them both via that Key, and open that as a recordset,
and then update the other database's table with data from the current
databases table.
I've opened a recordset using two tables in the current database like this:
...
strSql = "SELECT tbData.Name, tblContacts.HomeAddressStreet " & _
"FROM tbData, tblContacts " & _
"ORDER BY tbData.Name;"

Set rst = CurrentDb().OpenRecordset(strSQL)

...But I don't know how to do it when one table is in another database.

Is this possible?
Totally Stumped[/QUOTE]
[/QUOTE]

 

[Southern at Heart]

Could you give me a simple example of this?
If it's not so simple, I will search the web and try to find an example.
thanks,

No need for a recordset. Link to the table in the other db and create an
update query with that. Use inner join syntax, not a cartesian join.

Chris
Microsoft MVP

I'm needing to update a table in another database with data from a table in
the current database
I think I've figured out how to open the other database, like this:
...
Set dbsBook = DBEngine.Workspaces(0).OpenDatabase(strFileName)
...

I think I can open the table in it for editting, like this:
...
Set rst = dbsBook.OpenRecordset("tblName")

But Here's my problem. I'm needing to update it with data from a table in
the current database that I need joined to it before I know what to update.
I mean, I've got one table in the current database, and this tblName in the
other database, and they both have a Key, the field

 which matches them
up.  So I need to query them both via that Key, and open that as a recordset,
and then update the other database's table with data from the current
databases table.
I've opened a recordset using two tables in the current database like this:
...
strSql = "SELECT tbData.Name, tblContacts.HomeAddressStreet " & _
"FROM tbData, tblContacts " & _
"ORDER BY tbData.Name;"

Set rst = CurrentDb().OpenRecordset(strSQL)

...But I don't know how to do it when one table is in another database.

Is this possible?
Totally Stumped[/QUOTE]
[/QUOTE]

 

[tkelley via AccessMonster.com]

In the tables window, right-click in the white space and select import, then
point to your external databse, then find the table. It will create a live
link to the table in the external source, but you can basically treat it as a
local table.

...a link to the external table...
That sounds just like what I need (I'm a VERY simple man; self taught)
I'll have to figure out how you do that....
southernatheart

I'm a simple man. So I may be missing something. Please explain why you're
not just creating a link to the external table and then using it just as you

[quoted text clipped - 30 lines]

 

[Southern at Heart]

I see how this works, but I have to do this all via code, after the user
selects the targeted .mdb file.
Is that possible?

In the tables window, right-click in the white space and select import, then
point to your external databse, then find the table. It will create a live
link to the table in the external source, but you can basically treat it as a
local table.

...a link to the external table...
That sounds just like what I need (I'm a VERY simple man; self taught)
I'll have to figure out how you do that....
southernatheart

I'm a simple man. So I may be missing something. Please explain why you're
not just creating a link to the external table and then using it just as you

[quoted text clipped - 30 lines]

Is this possible?
Totally Stumped

 

[tkelley via AccessMonster.com]

The user doesn't have to select anything with regard to the targeted mdb.
YOU create the link. It will stay there for good once you do it the first
time.

Then build the queries and write the code to do whatever you needed to have
the user do. I suspect your waayyy over-complicating things. To accomplish
what you spoke of above, you absolutely do not have to have the user call up
an external database. Just create the link and proceed as if it were a part
of your local database all along.

This is very simple. Create the link. Treat it like any other table.

Use your delete button on this:
Set dbsBook = DBEngine.Workspaces(0).OpenDatabase(strFileName)
Set rst = dbsBook.OpenRecordset("tblName")

I see how this works, but I have to do this all via code, after the user
selects the targeted .mdb file.
Is that possible?

In the tables window, right-click in the white space and select import, then
point to your external databse, then find the table. It will create a live

[quoted text clipped - 11 lines]

 

[Southern at Heart]

Sorry, I guess I need to explain the situation a little more. The 'other
database' is not static, it doesn't even exist yet. But when the user uses
my access database, it will exist, somewhere in the file directory, where
ever they saved it. I don't know where it will be or even what the file
neame will be, but I DO know exactly what tables will be in it. The database
I'm making will update one of those tables.

Is is possible to programatically link to a table?
If not, I think I've got another solution: I can import it, query it with
my other onboard table, update it and then export it. All the user will have
to supply is the file location which I'll get from a filedialog...

....unless there's an easy way to programatically link to another table,
which I'd like to learn...

The user doesn't have to select anything with regard to the targeted mdb.
YOU create the link. It will stay there for good once you do it the first
time.

Then build the queries and write the code to do whatever you needed to have
the user do. I suspect your waayyy over-complicating things. To accomplish
what you spoke of above, you absolutely do not have to have the user call up
an external database. Just create the link and proceed as if it were a part
of your local database all along.

This is very simple. Create the link. Treat it like any other table.

Use your delete button on this:
Set dbsBook = DBEngine.Workspaces(0).OpenDatabase(strFileName)
Set rst = dbsBook.OpenRecordset("tblName")

I see how this works, but I have to do this all via code, after the user
selects the targeted .mdb file.
Is that possible?

In the tables window, right-click in the white space and select import, then
point to your external databse, then find the table. It will create a live

[quoted text clipped - 11 lines]

Is this possible?
Totally Stumped

 

[tkelley via AccessMonster.com]

Do I hear you saying that the other database is not in a static LOCATION?
One user can point to it in one location, then another user could point to it
in another location? (If so, I'll spare you my question of why ... If that's
the way it is, then it is what it is.)

Here is what I do to programatically create/recreate links, but to SQL Server.

You'll have to parse out and modify to do it for another mdb in the location
you pass.
Maybe some more research on this particular topic can help too.
=============================================
Private Function fnRefreshLinks(strSourceTable As String, _
strDestinationTable As String) As Boolean

Dim db As DAO.Database
Dim tdf As DAO.TableDef

fnRefreshLinks = False

On Error GoTo Err_

Set db = currentdb
Set tdf = db.TableDefs(strDestinationTable)

DeleteExisting:
db.TableDefs.Delete strDestinationTable
db.TableDefs.Refresh

CreateNewLink:
Set tdf = db.CreateTableDef(strDestinationTable)

tdf.Connect = Your connection string

tdf.SourceTableName = strSourceTable
db.TableDefs.Append tdf
db.TableDefs.Refresh

fnRefreshLinks = True
Set db = Nothing
Set tdf = Nothing

Exit Function

Err_:
If Err.Number = 3265 Then
Err.Clear
Resume CreateNewLink
Else
Set db = Nothing
Set tdf = Nothing
MsgBox Err.Description & ": " & Err.Number
fnRefreshLinks = False
End If

End Function
=============================================
It's a boolean that returns TRUE when successful, FALSE when not,
so you can handle unsuccessfuls in your code.

Sorry, I guess I need to explain the situation a little more. The 'other
database' is not static, it doesn't even exist yet. But when the user uses
my access database, it will exist, somewhere in the file directory, where
ever they saved it. I don't know where it will be or even what the file
neame will be, but I DO know exactly what tables will be in it. The database
I'm making will update one of those tables.

Is is possible to programatically link to a table?
If not, I think I've got another solution: I can import it, query it with
my other onboard table, update it and then export it. All the user will have
to supply is the file location which I'll get from a filedialog...

...unless there's an easy way to programatically link to another table,
which I'd like to learn...

The user doesn't have to select anything with regard to the targeted mdb.
YOU create the link. It will stay there for good once you do it the first

[quoted text clipped - 21 lines]

 

[Chris O'C via AccessMonster.com]

It's easy to link a table, but you're asking for trouble when you rely on the
user to do the db design correctly.

DoCmd.TransferDatabase acLink, "Microsoft Access", _
"c:\data\db.mdb", acTable, "sourcetbl", "linktbl"

Chris
Microsoft MVP

 

[Southern at Heart]

Thanks, this is what I need!

Do I hear you saying that the other database is not in a static LOCATION?
One user can point to it in one location, then another user could point to it
in another location? (If so, I'll spare you my question of why ... If that's
the way it is, then it is what it is.)

Here is what I do to programatically create/recreate links, but to SQL Server.

You'll have to parse out and modify to do it for another mdb in the location
you pass.
Maybe some more research on this particular topic can help too.
=============================================
Private Function fnRefreshLinks(strSourceTable As String, _
strDestinationTable As String) As Boolean

Dim db As DAO.Database
Dim tdf As DAO.TableDef

fnRefreshLinks = False

On Error GoTo Err_

Set db = currentdb
Set tdf = db.TableDefs(strDestinationTable)

DeleteExisting:
db.TableDefs.Delete strDestinationTable
db.TableDefs.Refresh

CreateNewLink:
Set tdf = db.CreateTableDef(strDestinationTable)

tdf.Connect = Your connection string

tdf.SourceTableName = strSourceTable
db.TableDefs.Append tdf
db.TableDefs.Refresh

fnRefreshLinks = True
Set db = Nothing
Set tdf = Nothing

Exit Function

Err_:
If Err.Number = 3265 Then
Err.Clear
Resume CreateNewLink
Else
Set db = Nothing
Set tdf = Nothing
MsgBox Err.Description & ": " & Err.Number
fnRefreshLinks = False
End If

End Function
=============================================
It's a boolean that returns TRUE when successful, FALSE when not,
so you can handle unsuccessfuls in your code.

Sorry, I guess I need to explain the situation a little more. The 'other
database' is not static, it doesn't even exist yet. But when the user uses
my access database, it will exist, somewhere in the file directory, where
ever they saved it. I don't know where it will be or even what the file
neame will be, but I DO know exactly what tables will be in it. The database
I'm making will update one of those tables.

Is is possible to programatically link to a table?
If not, I think I've got another solution: I can import it, query it with
my other onboard table, update it and then export it. All the user will have
to supply is the file location which I'll get from a filedialog...

...unless there's an easy way to programatically link to another table,
which I'd like to learn...

The user doesn't have to select anything with regard to the targeted mdb.
YOU create the link. It will stay there for good once you do it the first

[quoted text clipped - 21 lines]

Is this possible?
Totally Stumped

 

[tkelley via AccessMonster.com]

If you're not using SQL Server, strongly consider using the suggestion at:
Chris O'C - 11-14-2008 14:31

Good luck.

Thanks, this is what I need!

Do I hear you saying that the other database is not in a static LOCATION?
One user can point to it in one location, then another user could point to it

[quoted text clipped - 75 lines]

 

[John W. Vinson]

Could you give me a simple example of this?

On the menu, File... Get External Data... Link.
Select the default .mdb file as Files Of Type.
Navigate to the remote database.
Select the table.

You will now have a permanent link to that table in your database, and you can
use it in Queries just as if it were a local table. The only thing you can't
do is create enforced relationships between tables in different databases.