473,326 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Linked tables from multiple databases

Denburt
1,356 Expert 1GB
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
12 3968
FishVal
2,653 Expert 2GB
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
8,834 Expert 8TB
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
1,356 Expert 1GB
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
1,356 Expert 1GB
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
2,653 Expert 2GB
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
1,356 Expert 1GB
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
14,534 Expert Mod 8TB
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
1,356 Expert 1GB
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
254 Expert 100+
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
1,356 Expert 1GB
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
254 Expert 100+
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
1,356 Expert 1GB
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

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

Similar topics

6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
3
by: Raghuraman | last post by:
Hi, I was thinking abt linked servers deals only with multiple servers physically inter-connected. Now i come accross that we can link our non-sqlserver databases also. It is thru the...
5
by: pinballjim | last post by:
Hello everyone, I'm looking for a simple way to create a local copy of a linked table. I've got a database that links about 10 tables from other databases. This works fine on my machine, but I...
2
by: Internet Arrow Limited | last post by:
Hi, I have a requirement to write an access application that must run under access97 and access2K. Some users will use Acess2K to access data that will also be accessed by Access97 users. The...
3
by: G rumpy O ld D uffer | last post by:
This is probably a 'Low-Level' question to all the ACCESS experts but I've only been using ACCESS for a couple of weeks. I've been given 30+ (and counting) separate 'Weekly' Databases which all...
2
by: Robert McGregor | last post by:
Hi all, I've got a Front End / Back End database that was working just fine. One day i opened the FE to find that if I tried to open one of the linked tables from the database window, nothing...
2
by: Vern Shellman | last post by:
We've got a form in Access 97 SR-2 that works fine with local tables. The pertinent VB code populating a combo box looks like this: Private Function ShowMOFInfo() Dim db As Database Dim rec...
0
by: Stu | last post by:
I have a few FrontPAGE 2002 Webs running on Windows 2003 Servers and IIS. My webs use ASP (classic) pages that display data pulled from the Access databases using the FrontPage database results...
7
by: bill.brennum | last post by:
Hi, Have a number of Access Databases that I inherited and want to zip a few of them. My concern is that other active Microsoft Applications may be linking to the database or its tables. Is...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.