来自多个数据库的链接表 [英] Linked tables from multiple databases

问题描述

O.K。我正在寻找一些想法，意见，建议甚至是问题。


目前我正在使用VBA创建临时表然后从那里工作但是我想看看是否可以这样做没有临时表。


大部分我无法控制，因此对结构进行更改不是一种选择。我目前有一个MS Access数据库，数据存储在本地数据库的本地表中。我还将表连接到两个不同的Oracle数据库，我不希望密码存储在链接中，但出于自动化目的，我不想在其中输入任何一个。所以我正在寻找关于如何在sql语句中将三个表链接在一起的想法，以便我可以将它作为单个记录集进行迭代？


可以说表结构如下：


表名1：LocalDB

StatID


表名2：Or1

GrpID

StatID


表名3：Or2

GrpID


在此先感谢。

解决方案

Denburt。


我加入Access数据库本地访问表没有问题用ODBC链接MySQL表。

这是Oracle特有的吗？


问候，

鱼

好​​的我正在寻找一些想法，意见，建议甚至是问题。


目前我正在使用VBA创建临时表然后从那里工作但是我想看看是否可以这样做没有临时表。


大部分我无法控制，因此对结构进行更改不是一种选择。我目前有一个MS Access数据库，数据存储在本地数据库的本地表中。我还将表连接到两个不同的Oracle数据库，我不希望密码存储在链接中，但出于自动化目的，我不想在其中输入任何一个。所以我正在寻找关于如何在sql语句中将三个表链接在一起的想法，以便我可以将它作为单个记录集进行迭代？


可以说表结构如下：


表名1：LocalDB

StatID


表名2：Or1

GrpID

StatID


表名3：Or2

GrpID


提前致谢。

Hello Denburt：如果我过于简单化，我道歉，但是不能在关系中使用本地和链接表，例如通常用于关系中的查询的关系在严格的本地表上定义？只知道查看定义是不可能的，但是可以创建以下关系吗？

展开 | 选择 | 换行 | 行号

< blockquote>

Denburt。


我可以毫无困难地加入带有ODBC链接MySQL表的Access数据库本地Access表。

这是Oracle特有的吗？


问候，

鱼

所以你在VBA上用密码保护的MySQL数据库做这个，密码是'n'用链接表存储但你没有问题？你有办法将密码发送到MySQL链接表吗？我已经考虑重新链接表并在程序开始时包含密码，然后在完成后重新链接它而没有密码。


目前我在VBA中使用oracle连接字符串包含我的用户ID密码等。然后我打开记录集，在创建包含所有本地表的SQL语句之前将数据拉入本地表。


如果你有另一种我感兴趣的方法。

O.K. I am looking for some ideas, comments, suggestions or even questions.

Currently I am using VBA to create temp tables then work from there however I would like to see if this can be done without the temp tables.

Much of this I can''t control so making changes to the structures isn''t an option. I currently have a MS Access database with data stored in this database in local tables. I also have linked tables to two different Oracle databases and I do not want the passwords stored in the link, yet for automation purposes I don''t want to type it in either. So I am looking for ideas on how I can link the three tables together in an sql statement so I can iterate through it as a single recordset?

Lets say the table structure is as follows:

Table Name 1: LocalDB
StatID

Table Name 2: Or1
GrpID
StatID

Table Name 3: Or2
GrpID

Thanks in advance.

解决方案

Hi, Denburt.

I have no trouble to join in an Access database local Access table with ODBC linked MySQL table.
Is that something Oracle specific?

Regards,
Fish

O.K. I am looking for some ideas, comments, suggestions or even questions.

Currently I am using VBA to create temp tables then work from there however I would like to see if this can be done without the temp tables.

Much of this I can''t control so making changes to the structures isn''t an option. I currently have a MS Access database with data stored in this database in local tables. I also have linked tables to two different Oracle databases and I do not want the passwords stored in the link, yet for automation purposes I don''t want to type it in either. So I am looking for ideas on how I can link the three tables together in an sql statement so I can iterate through it as a single recordset?

Lets say the table structure is as follows:

Table Name 1: LocalDB
StatID

Table Name 2: Or1
GrpID
StatID

Table Name 3: Or2
GrpID

Thanks in advance.

Hello Denburt: I apologize if I am oversimplifying matters, but can''t Local and Linked Tables be used in Relationships such as those typically used in Queries where Relationships are defined on strictly Local Tables? It''s impossible to know just looking at the definitions, but could the following Relationships be created?

Expand|Select|Wrap|Line Numbers

Hi, Denburt.

I have no trouble to join in an Access database local Access table with ODBC linked MySQL table.
Is that something Oracle specific?

Regards,
Fish

So you do this in VBA on a password secured MySQL database and the password isn''t stored with the linked table yet you have no problem? Do you have a way to send the password to the MySQL linked table? I have considered relinking the table and include the password at the beginning of the procedure then relink it without a password when I am finished.

Currently I use an oracle connection string in VBA and that contains my user ID password etc. I then open the recordset pulling the data into a local table before I create an SQL statement that contains all local tables.

If you have an alternate method I am interested.

这篇关于来自多个数据库的链接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！