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.Cr eateTableDef 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.Cr eateTableDef 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 programmaticall y, 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 programmaticall y, 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 programmaticall y 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 programmaticall y 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.Cr eateTableDef 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.Cr eateTableDef 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.