By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,285 Members | 1,619 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,285 IT Pros & Developers. It's quick & easy.

Linked tables from multiple databases

Denburt
Expert 100+
P: 1,356
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.
Dec 4 '07 #1
Share this Question
Share on Google+
12 Replies


FishVal
Expert 2.5K+
P: 2,653
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
Dec 4 '07 #2

ADezii
Expert 5K+
P: 8,669
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
  1. 'The following scenario would indicate a MANY to MANY Relationship between Table Name 1 and Table Name 3, thus the Intermediate Linking Table (Table Name 2):
  2. Table Name 1.[StatID]{1} ==> Table Name 2.[StatID]{MANY}
  3. Table Name 3.[GrpID]{1} ==>  Table Name 2.[GrpID](MANY}
Dec 4 '07 #3

Denburt
Expert 100+
P: 1,356
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.
Dec 4 '07 #4

Denburt
Expert 100+
P: 1,356
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
  1. 'The following scenario would indicate a MANY to MANY Relationship between Table Name 1 and Table Name 3, thus the Intermediate Linking Table (Table Name 2):
  2. Table Name 1.[StatID]{1} ==> Table Name 2.[StatID]{MANY}
  3. Table Name 3.[GrpID]{1} ==>  Table Name 2.[GrpID](MANY}
Yes I have the relationships set up I even have a number of queries that I use to view the data, yet the Oracle database is password protected. I do not want the password information stored in the linked table or in any queries where it can be easily accessed, I am willing to store this information in VBA since it will be more difficult for someone to discover it. I currently have a routine that runs at a specific time each day so I need to avoid any prompts. Is this a little more clear? Maybe I just walked out and left my brain on the counter again, so sorry.
Dec 4 '07 #5

FishVal
Expert 2.5K+
P: 2,653
The table is linked via MySQL ODBC connector.
MySQL database is password-protected. This password is stored in DSN. It never appears in Access database. Connection string for the table looks as following
Expand|Select|Wrap|Line Numbers
  1. ODBC;DATABASE=products;DSN=products_mysql;OPTION=0;PORT=0;SERVER=localhost;
  2.  
Dec 4 '07 #6

Denburt
Expert 100+
P: 1,356
ok I am still looking at a few possibilities but I don't see any light at this time. I am sure there must be some way to better handle an issue like this so I will continue to persist. I did find an interesting article on recordsets with a reference to datasources. More updates to be posted soon.


"A recordset can join two or more tables from the same data source, but not tables from different data sources."

http://msdn2.microsoft.com/en-us/lib...wy(VS.80).aspx
Dec 4 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
If this is not a stupid question why does it have to be only one recordset. Why can't you create 3 separate recordsets (or two if you join the sql tables in a view)?

When I have to manipulate difficult data and one query just won't do the job then I create multiple recordsets and handle them in nested loops.
Dec 11 '07 #8

Denburt
Expert 100+
P: 1,356
No yours is not a stupid question. Mine was ;) I am just trying to squeeze every little bit of response time I can out of this particular DB so I am trying a number of different things to see what works best. and yes your method is the way it is usually done in VBA. The query in question if just a particularly difficult one to work with and will take quite a bit of coding to achieve the results like you mentioned. Thanks
Dec 20 '07 #9

jaxjagfan
Expert 100+
P: 254
If you have a Windows User group your users belong to like Acct_Dept, then
have your Oracle DBA assign this group as Trusted to the Oracle tables. Or create a group account for the application on the network (Network Admin would need to do this if you don't have authority). The the network admin add your users to the group account. Oracle Admin - assign this new group with Trusted rights to the Oracle tables you want to link to. (I assume you are only reading and not updating the Oracle data). You can then link the tables with no passwords required. When the app is opened and the link is established it would use the security rights of the user group that person belongs to.

I do this with a lot of my SQL Server tables.
Dec 20 '07 #10

Denburt
Expert 100+
P: 1,356
If you have a Windows User group your users belong to like Acct_Dept, then
have your Oracle DBA assign this group as Trusted to the Oracle tables. Or create a group account for the application on the network (Network Admin would need to do this if you don't have authority). The the network admin add your users to the group account. Oracle Admin - assign this new group with Trusted rights to the Oracle tables you want to link to. (I assume you are only reading and not updating the Oracle data). You can then link the tables with no passwords required. When the app is opened and the link is established it would use the security rights of the user group that person belongs to.

I do this with a lot of my SQL Server tables.
This would probably be the optimal route if I could just get the guys to do something... I have submitted request upon request and I do my best to send an email on a weekly basis checking on the status of my numerous requests yet again and again I find myself looking for other alternatives (several requests have gone unanswered for a year or more). Thanks a heap for your suggestion though and if I ever do get the permissions I am requesting I can probably make that work.
Dec 20 '07 #11

jaxjagfan
Expert 100+
P: 254
This would probably be the optimal route if I could just get the guys to do something... I have submitted request upon request and I do my best to send an email on a weekly basis checking on the status of my numerous requests yet again and again I find myself looking for other alternatives (several requests have gone unanswered for a year or more). Thanks a heap for your suggestion though and if I ever do get the permissions I am requesting I can probably make that work.
You need to send an email to the personnel doing this work (or supposed to be) asking about the status. Include your manager and theirs in the email. Explain the importance of the request. It might anger someone going over their head but you have customers who are waiting on your application. Make sure your message is politically correct so as not to smear your own name.

I don't know why but as a contractor traveling to various companies, I have consistently found Oracle admins to be less responsive than SQL Server admins.
Dec 21 '07 #12

Denburt
Expert 100+
P: 1,356
Yeah I find it interesting that people are allowed to drag things out like they do but hey. Like you said follow up with the right procedures explaining things in detail and keep on moving and waiting. Things do get done here but as you said:

I don't know why but as a contractor traveling to various companies, I have consistently found Oracle admins to be less responsive than SQL Server admins.
and I thought that was just my perception (generally speaking of course)....

Thanks again for the input.
Dec 21 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.