SQL使用连接字符串在本地表和外部表之间连接？ [英] SQL Join between a local table and an external one, using connect string?
问题描述
大家好，
我通过连接字符串从Oracle数据库访问数据。
我遇到了我的情况我想编写我的SQL select语句来在我的MS Access（2003）数据库中的本地表和外部Oracle表之间进行连接。但我意识到我不知道如何编写SQL。似乎整个SQL命令都传递给Oracle，没有给我任何可见的本地表，这是正确的吗？
我知道如果我将Oracle表声明为外部表在MS Access环境中（而不是使用连接字符串），我可以编写VBA代码，透明地将它们视为用于SQL语法的本地表，因此我可以用这种方式编写SQL。但是对我施加的安全限制不允许我这样做 - 最终MDE的每个用户都需要他们自己的Oracle安全登录。
我使用以下内容调用我的SQL语句VBA的风格。如果我应该使用另一个VBA命令，或者如果有有效的SQL语法来执行我想要的操作，那么请告诉我。
Hi all,
I am accessing data from an Oracle database via a connect string.
I got into the situation where I wanted to write my SQL select statement to do a join between a local table sitting in my MS Access (2003) database, and an external Oracle table. But I realised I had no idea how to write the SQL. It appears that the whole SQL command gets passed to Oracle, not giving me any visibility to my local tables, is that correct?
I know that if I declare the Oracle tables as external tables in the MS Access environment (rather than use a connect string), I can write VBA code that transparently treats them as local tables for SQL syntax purposes, so I could code SQL that way. But security constraints imposed on me don''t allow me to do that - each user of the final MDE would need their own Oracle security logins.
I call my SQL statements using the following style of VBA. If there is another VBA command I should be using, or if there is valid SQL syntax to do what I want, then please let me know.
推荐答案
Zwoker。
Hi, Zwoker.
.....但我意识到我不知道如何编写SQL。似乎整个SQL命令都传递给了Oracle，没有给我任何对本地表的可见性，这是正确的吗？ ...
..... But I realised I had no idea how to write the SQL. It appears that the whole SQL command gets passed to Oracle, not giving me any visibility to my local tables, is that correct? ...
据我所知，它=真。
To the best of my knowledge it = True.
。 ...
我知道如果我在MS Access环境中将Oracle表声明为外部表（而不是使用连接字符串），我可以编写VBA代码，将它们透明地视为本地表SQL语法的目的，所以我可以用这种方式编写SQL。但是对我施加的安全限制不允许我这样做 - 最终MDE的每个用户都需要他们自己的Oracle安全登录。
....
....
I know that if I declare the Oracle tables as external tables in the MS Access environment (rather than use a connect string), I can write VBA code that transparently treats them as local tables for SQL syntax purposes, so I could code SQL that way. But security constraints imposed on me don''t allow me to do that - each user of the final MDE would need their own Oracle security logins.
....
您可以在用户进入前端之后通过代码动态链接外部表（我希望您有每个用户会话的前端副本）。
请参阅有关DAO.Database.CreateTableDef方法和DAO.TableDefs集合的Access帮助（特别是Append方法）。
探索DAO.TableDef类也会为您提供更多有用的信息。
问候，
鱼
You may link external table dynamically via code just after a user has looged into the front-end (I hope you have a copy of front-end for each user session).
Refer to Access help concerning DAO.Database.CreateTableDef method and DAO.TableDefs collection (particularly Append method).
Exploring DAO.TableDef class will give you much useful information too.
Regards,
Fish
请参阅有关的访问帮助DAO.Database.CreateTableDef方法和DAO.TableDefs集合（特别是Append方法）。
探索DAO.TableDef类也会给你很多有用的信息。
Refer to Access help concerning DAO.Database.CreateTableDef method and DAO.TableDefs collection (particularly Append method).
Exploring DAO.TableDef class will give you much useful information too.
如果我了解你（以及我在CreateTableDefs帮助下的快速查看） ，你是说我可以用它来创建一个指向外部Oracle表的链接，然后把结果当作一个本地表来处理吗？
如果是这样的话，听起来就好像它相当于声明表格部分中的MS Access中的oracle表，但我仍然以编程方式进行安全登录，而不是让每个用户在使用MDE时登录 - 这是我的解决方案的要求。
这是对的吗？或者我误解了？
最后一个问题 - 我一直在我的VBA代码中使用ADODB。我可以和DAO混搭吗？或者是否有一个与CreateTableDefs等效的ADODB？
感谢您的帮助。
Hi,
If I understand you (and the quick look I''ve had at the help for CreateTableDefs), are you saying that I can use that to create a link to the external Oracle table, and then treat the result as a local table?
If so, it sounds like it would be the equivalent of declaring the oracle table in MS Access in the table section, but with me still doing the security login programmatically, rather than having each user log in when they use the MDE - which is a requirement for my solution.
Is that correct? Or have I misunderstood?
One final question - I have been using ADODB in my VBA code. Can I mix and match with DAO? Or is there an ADODB equivalent to the CreateTableDefs?
Thanks for your help.
如果我了解你（以及我在CreateTableDefs帮助下的快速查看），你是说我可以用它来创建一个链接到外部Oracle表，然后将结果视为本地表？
如果是这样，听起来它等同于在表部分中声明MS Access中的oracle表，但是我仍然以编程方式进行安全登录，而不是让每个用户在使用MDE时登录 - 这是我的解决方案的要求。
这是正确的吗？还是我误解了？
Hi,
If I understand you (and the quick look I''ve had at the help for CreateTableDefs), are you saying that I can use that to create a link to the external Oracle table, and then treat the result as a local table?
If so, it sounds like it would be the equivalent of declaring the oracle table in MS Access in the table section, but with me still doing the security login programmatically, rather than having each user log in when they use the MDE - which is a requirement for my solution.
Is that correct? Or have I misunderstood?
您已经以编程方式登录ADO连接。这是不一样的吗？
动态链接你可以在登录时使用连接字符串和用户提供的密码。你是说这个吗？
在用户登录之前关闭或打开db（或者在两种情况下）你应该取消链接表。当然，这意味着多个用户不能同时打开前端的一个副本。
You are already doing login programmatically opening ADO connection. Is this not the same?
Linking dynamically you may use connection string with password provided by user during login. Did you mean this?
On db close or on open before user logs (or in both cases) you should unlink the table. Sure that means one copy of front-end has not to be opened simultaneously by multiple users.
最后一个问题 - 我一直在使用ADODB VBA代码。我可以和DAO混搭吗？或者是否有一个与CreateTableDefs等效的ADODB？
One final question - I have been using ADODB in my VBA code. Can I mix and match with DAO? Or is there an ADODB equivalent to the CreateTableDefs?
通过创建TableDef对象链接外部表与使用向导链接完全相同。它适用于后者吗？
Linking external table via creating TableDef object is exactly the same as linking using wizard. Does it work with the latter?
这篇关于SQL使用连接字符串在本地表和外部表之间连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
