SQL Join between a local table and an external one, using connect string?

[Zwoker]

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.

Set InitialRst = New ADODB.Recordset
InitialRst.Open SQLstring, connForDB, adOpenStatic, adLockReadOnly

As you would expect the SQLstring contains my SQL command, and the connForDB is the connection object to the Oracle database.

Thanks in advance for any help or suggestions.

[FishVal]

Hi, Zwoker.

..... 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.

....
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.
....

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

[Zwoker]

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.

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.

[FishVal]

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?

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.

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?

Linking external table via creating TableDef object is exactly the same as linking using wizard. Does it work with the latter?

[Zwoker]

You are already doing login programmatically opening ADO connection. Is this not the same?

Yes, it probably is the same. I build the connection string using a usercode and password stored (encrypted) in a local table, and use that with some some constants and the correct ODBC DSN name for the database I want (there are several options). Just to be sure we are talking about the same thing, this is the code I use:

connStrForDB = "Provider=MSDASQL.1;Password=" & pPassword & ";Persist Security Info=False;User ID=" & pUsercode & ";Data Source=" & DSNname & ";DBA=R;"
Set connForDB = New ADODB.Connection
connForDB.ConnectionString = connStrForDB
connForDB.CommandTimeout = 0
connForDB.CursorLocation = adUseClient
connForDB.Open

From then on I use the connForDB as a parameter when doing the reads of data from the Oracle tables - as you can see in my first post.

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.

There is no user log in - That is one of the requirements that stopped me using the reasonably simple external table declarations in MS Access, and switch to a connection string where I can control the login usercode/password. From the users perspective when they run the MDE they just see a form appear, choose whatever criteria they want from the pick-lists, and click the "Go" button. A little while longer (too long at the moment) the report appears in preview mode. If they are happy they click the Print button in Access and exit the MDE.

Linking external table via creating TableDef object is exactly the same as linking using wizard. Does it work with the latter?

Okay, I used to use the wizard linked tables when I thought that all users would have their own Oracle database logins, but no longer. But what you say is good news, as everything was much simpler when using the SQL over the linked tables. I was able to do joins etc... on all my data, treating it all as local tables whether they were or not.

There is an example of the CreateTableDefs in the help that shows an ODBC connected database being accessed. It looks a little confusing, but I'll try copying it and changing the connection string parameters, table names etc... to match mine, and see how it goes. I'm still unsure if I can include the DAO code in with my ADO? Or will I have to convert any of my ADO code to the DAO syntax?

Thanks.

[ADezii]

Hi, Zwoker.



To the best of my knowledge it = True.



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

Hello FishVal, I'm not exactly sure if this is what you are referring to, so I'll just drop it in your lap. The code is as simple as you can get for Linking an External Table within Access, just call the Sub-Routine and pass it 2 Arguments. It works fine to Link an Access Table, but to adapt it to an External Oracle Table would need some adjustment on the Connect Property. If this is not what you were referring to, forget I dropped by (LOL).

Public Sub LinkTable(strTable As String, strSourceDB As String)
'This Function will DELETE any Table attachments and then Re-Attach them to
'completely insure the reliability of the attached Tables.
   '  Parameters:
   '    strTable - Name of the Table to be Linked
   '    strSourceDB - Fully qualified Path and Filename of the Source DB
 
On Error Resume Next   'IGNORE ERRORS - necessary!
 
Dim ws As Workspace
Dim db As Database
Dim tdf As TableDef
 
Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
 
'DELETE the Link if it already exists
  db.TableDefs.Delete strTable
 
'CREATE a new Link
  Set tdf = db.CreateTableDef(strTable)
 
'SET the Properties of the new Link and Append to TableDefs Collection
  tdf.SourceTableName = strTable
  tdf.Connect = ";DATABASE=" & strSourceDB
  db.TableDefs.Append tdf
End Sub

[Zwoker]

Hi ADezii,

Thanks for the code. It was me, not FishVal, that started all this. :)

On the assumption I can get the code to work with my external Oracle tables, does it mean that I could treat the result just as a local table, and use DoCmd.RunSQL to run my SQL code, without worrying about the ultimate source of the data? That would let me do what I wanted, which was have joins between two tables - One a "true" local MS Access table, and the other an external Oracle table.

Thanks.

[ADezii]

Hi ADezii,

Thanks for the code. It was me, not FishVal, that started all this. :)

On the assumption I can get the code to work with my external Oracle tables, does it mean that I could treat the result just as a local table, and use DoCmd.RunSQL to run my SQL code, without worrying about the ultimate source of the data? That would let me do what I wanted, which was have joins between two tables - One a "true" local MS Access table, and the other an external Oracle table.

Thanks.

To the best of my knowledge, you are correct on your assumption in that once the Oracle Table is successfully Linked, it can be treated as though it was a Local Table with exceptions. A typical exception is that you cannot make Design Changes to the Oracle Table from within Access.

When I directed this to FishVal, I was referring to his Post #2 namely:

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.

[Zwoker]

Hi ADezii,

I spent a few minutes modifying your code to get it to connect to the Oracle database, then ran it to connect to a couple of tables. It appears to work fine. The real test will be using it to do the join that I want between the local and external tables and see if it works.

Thanks for all your (and FishVal's) help in this.

Once I have a confirmed working version I'll post my modified version of your block of code back here so that in the unlikely event that anyone else needs to do this odd combination of data activity, they can have a working example.


Regards,
Zwoker.

[ADezii]

Hi ADezii,

I spent a few minutes modifying your code to get it to connect to the Oracle database, then ran it to connect to a couple of tables. It appears to work fine. The real test will be using it to do the join that I want between the local and external tables and see if it works.

Thanks for all your (and FishVal's) help in this.

Once I have a confirmed working version I'll post my modified version of your block of code back here so that in the unlikely event that anyone else needs to do this odd combination of data activity, they can have a working example.


Regards,
Zwoker.

Excellant idea Zwoker, kindly post your code for all to see and learn, I'm particularly interested in the String used for the Connect Property.

[Zwoker]

Hi all,

Below is my modified version of ADezii's code. For me it connects to an Oracle database via an ODBC connection.

Public Sub LinkTable(strTable As String, SchemaName As String, DSNname As String)
 
  ' This function will create a local instance of an external ODBC linked table.
 
  ' It will DELETE any Table attachments and then Re-Attach them to
  ' completely ensure the reliability of the attached Tables.
 
  '  Parameters:
  '    strTable   - Name of the Table to be Linked
  '    SchemaName - Schema (database) name in the Oracle Database.
  '    DSNname    - DSN (ODBC) name to use for connection.
 
  ' It is assumed that pUsercode & pPassword have already been set up elsewhere.
 
  On Error Resume Next   'IGNORE ERRORS - necessary!
 
  Dim ws As Workspace
  Dim db As Database
  Dim tdf As TableDef
 
  Set ws = DBEngine.Workspaces(0)
  Set db = ws.Databases(0)
 
  'DELETE the Link if it already exists
  db.TableDefs.Delete strTable
 
  'CREATE a new Link
  Set tdf = db.CreateTableDef(strTable)
 
  'SET the Properties of the new Link and Append to TableDefs Collection
  tdf.SourceTableName = strTable
  tdf.Connect = "ODBC;DATABASE=" & SchemaName & ";UID=" & pUsercode & ";PWD=" & pPassword & ";DSN=" & DSNname
  db.TableDefs.Append tdf
 
End Sub

If you leave the strings pUsercode and pPassword empty, then it prompts for a usercode/password via a dialog box.

An example of calling the code is:

LinkTable "CUST", TablePrefix, DSNname

Where the TablePrefix & DSNname strings have already been set up elsewhere - I have several different Oracle databases I need to read data from to do the task, so they are parameterised.

If anyone has any questions, please ask.



<Ramble Mode>
Just to clarify its purpose, 99% of people probably don't need to do this – they can use the little external table wizard to link their Oracle tables in MS Access, and use them in VBA code as if they are tables that are really local to MS Access. This is simple and easy to do. The issue I had with this method is simply one of security. I wasn't going to be allowed to distribute my solution, even as an MDE, if it meant that every user has to have their own usercode and password to log into the Oracle database (and having users have a generic one is a definite no-no). So I needed a way of programmatically logging in. The connection string approach worked fine for that - I included an encrypted usercode and password in a static table in the database, and passed them (decrypted) as part of the connection string parameters. All went well until I had a situation where I had a local table that I needed to do a Join with one of the external Oracle tables. SQL used with the connect string approach just doesn't seem to have the syntax to reference a local table within the SQL code... So here we are with a comprise. I have a local declared table using the above code, but one that uses a programmatic usercode/password to create the linkage, which doesn't prompt the user when it runs. The best of both worlds, I hope.
</Ramble Mode>

[ADezii]

Hi all,

Below is my modified version of ADezii's code. For me it connects to an Oracle database via an ODBC connection.

Public Sub LinkTable(strTable As String, SchemaName As String, DSNname As String)
 
  ' This function will create a local instance of an external ODBC linked table.
 
  ' It will DELETE any Table attachments and then Re-Attach them to
  ' completely ensure the reliability of the attached Tables.
 
  '  Parameters:
  '    strTable   - Name of the Table to be Linked
  '    SchemaName - Schema (database) name in the Oracle Database.
  '    DSNname    - DSN (ODBC) name to use for connection.
 
  ' It is assumed that pUsercode & pPassword have already been set up elsewhere.
 
  On Error Resume Next   'IGNORE ERRORS - necessary!
 
  Dim ws As Workspace
  Dim db As Database
  Dim tdf As TableDef
 
  Set ws = DBEngine.Workspaces(0)
  Set db = ws.Databases(0)
 
  'DELETE the Link if it already exists
  db.TableDefs.Delete strTable
 
  'CREATE a new Link
  Set tdf = db.CreateTableDef(strTable)
 
  'SET the Properties of the new Link and Append to TableDefs Collection
  tdf.SourceTableName = strTable
  tdf.Connect = "ODBC;DATABASE=" & SchemaName & ";UID=" & pUsercode & ";PWD=" & pPassword & ";DSN=" & DSNname
  db.TableDefs.Append tdf
 
End Sub

If you leave the strings pUsercode and pPassword empty, then it prompts for a usercode/password via a dialog box.

An example of calling the code is:

LinkTable "CUST", TablePrefix, DSNname

Where the TablePrefix & DSNname strings have already been set up elsewhere - I have several different Oracle databases I need to read data from to do the task, so they are parameterised.

If anyone has any questions, please ask.



<Ramble Mode>
Just to clarify its purpose, 99% of people probably don't need to do this – they can use the little external table wizard to link their Oracle tables in MS Access, and use them in VBA code as if they are tables that are really local to MS Access. This is simple and easy to do. The issue I had with this method is simply one of security. I wasn't going to be allowed to distribute my solution, even as an MDE, if it meant that every user has to have their own usercode and password to log into the Oracle database (and having users have a generic one is a definite no-no). So I needed a way of programmatically logging in. The connection string approach worked fine for that - I included an encrypted usercode and password in a static table in the database, and passed them (decrypted) as part of the connection string parameters. All went well until I had a situation where I had a local table that I needed to do a Join with one of the external Oracle tables. SQL used with the connect string approach just doesn't seem to have the syntax to reference a local table within the SQL code... So here we are with a comprise. I have a local declared table using the above code, but one that uses a programmatic usercode/password to create the linkage, which doesn't prompt the user when it runs. The best of both worlds, I hope.
</Ramble Mode>

Congratulations, and thanks for sharing your success with us.

[Zwoker]

Congratulations, and thanks for sharing your success with us.

Thanks.

For those very few reading all this that have some interest in the topic, another ramble starts below... *grin*

I have found that the Joins between the "true" local table and the locally declared, but really external, table doesn't work too badly. I had expected the times to be poor, as the local table is here on my PC and the external table is in an Oracle database on the other side of the world.
But it turned out to not be as bad as I thought - my test local table has around 550 records, and the resultset produced from running the SQL join returned around 3,300 records, and it did it in around 13 seconds.

But, when I added a subquery to get only the latest record from the external table (the external is a date-based transaction table, the local a master table) the response time slowed down massively - It took around 10 minutes to come back. I checked the subquery using an alternative master table in the Oracle database, that happens to have the same keys for the Join, and it only took around 10 seconds. This would tend to indicate that the ODBC translation mechanism for the local style SQL syntax is not very efficient, in MS Access 2003.

So I'll keep the basic mechanism, but do away with subqueries. I'll just process the data in VBA to keep the records I want.

And unless someone has any questions on this somewhat specific and odd topic, I'll leave it at that. :)

[ADezii]

Thanks.

For those very few reading all this that have some interest in the topic, another ramble starts below... *grin*

I have found that the Joins between the "true" local table and the locally declared, but really external, table doesn't work too badly. I had expected the times to be poor, as the local table is here on my PC and the external table is in an Oracle database on the other side of the world.
But it turned out to not be as bad as I thought - my test local table has around 550 records, and the resultset produced from running the SQL join returned around 3,300 records, and it did it in around 13 seconds.

But, when I added a subquery to get only the latest record from the external table (the external is a date-based transaction table, the local a master table) the response time slowed down massively - It took around 10 minutes to come back. I checked the subquery using an alternative master table in the Oracle database, that happens to have the same keys for the Join, and it only took around 10 seconds. This would tend to indicate that the ODBC translation mechanism for the local style SQL syntax is not very efficient, in MS Access 2003.

So I'll keep the basic mechanism, but do away with subqueries. I'll just process the data in VBA to keep the records I want.
And unless someone has any questions on this somewhat specific and odd topic, I'll leave it at that. :)

It took around 10 minutes to come back. I checked the subquery using an alternative master table in the Oracle database, that happens to have the same keys for the Join, and it only took around 10 seconds.

1. How did the use of the Alternative Master Table differ from the Primary Master? I'm not exactly sure what you mean
2. Was the Alternative Table dynamically linked also? I'm assuming it was not.
3. If it has the rquivalent Join Keys, why the 60X degradation in processing time?
4. Thanks for the consideration.

[Zwoker]

How did the use of the Alternative Master Table differ from the Primary Master? I'm not exactly sure what you mean

The two master tables are different sets of data, that happen to have the same main keys in common. Joining the alternative table to the transaction table was a non-meaningful thing to do in a business sense, but since the actual data in the keys was similar, it was a meaningful test for timings and record counts etc... The number of records found using the Join, with the alternative master, was similar.

Was the Alternative Table dynamically linked also? I'm assuming it was not.

You are correct, the alternative master is an Oracle table, rather than the local table that I was working with. So I was joining and subquerying on two external tables.

If it has the equivalent Join Keys, why the 60X degradation in processing time?

That's the question. I can only assume its a combination of having MS Access do whatever parsing it does on the SQL (the syntax for a DoCmd.RunSQL is different from the SQL I pass to the Oracle database when using the connect string method), and on the nature of each local table record having to be sent to the Oracle database to evaluate the Oracle transaction table Join - but this wasn't an issue until I added the subquery. The Subquery, which runs fine between the two external tables, is the heart of the issue when using the local table as the left side of the Join, whatever the cause really is.

Thanks for the consideration.

You're welcome.

I have found most of what I have had to do to be far more complicated and confusing than I expected it to be. I keep having the expectation that what I want to do must be commonly done, and therefore should be easy to find good examples online. I keep getting disappointed. The one good thing that has come out of this is I now know a great deal more about VBA, connection strings, SQL and various related items. My only ongoing fear is that since I'm largely self-taught, that there could really be simple ways of doing things that I have found complicated solutions for...

[ADezii]

The two master tables are different sets of data, that happen to have the same main keys in common. Joining the alternative table to the transaction table was a non-meaningful thing to do in a business sense, but since the actual data in the keys was similar, it was a meaningful test for timings and record counts etc... The number of records found using the Join, with the alternative master, was similar.

You are correct, the alternative master is an Oracle table, rather than the local table that I was working with. So I was joining and subquerying on two external tables.

That's the question. I can only assume its a combination of having MS Access do whatever parsing it does on the SQL (the syntax for a DoCmd.RunSQL is different from the SQL I pass to the Oracle database when using the connect string method), and on the nature of each local table record having to be sent to the Oracle database to evaluate the Oracle transaction table Join - but this wasn't an issue until I added the subquery. The Subquery, which runs fine between the two external tables, is the heart of the issue when using the local table as the left side of the Join, whatever the cause really is.


You're welcome.

I have found most of what I have had to do to be far more complicated and confusing than I expected it to be. I keep having the expectation that what I want to do must be commonly done, and therefore should be easy to find good examples online. I keep getting disappointed. The one good thing that has come out of this is I now know a great deal more about VBA, connection strings, SQL and various related items. My only ongoing fear is that since I'm largely self-taught, that there could really be simple ways of doing things that I have found complicated solutions for...

My only ongoing fear is that since I'm largely self-taught, that there could really be simple ways of doing things that I have found complicated solutions for...

That's why we're all here...