Pass Through Make Table

[alex]

Pass Through Make Table

Hello,

I have a pass through query object; I also have a make table query
object based off the pass through query. This works just fine to make
an Access table with ORACLE data from my pass through query.

My problem/issue: I must get this into VBA.

Can anyone tell me how to put the pass through and make table queries
into VBA?

The ODBC Connect Str looks like this:
ODBC; DSN=SERVER;UID=LOGIN;PWN=MYPSWRD;SERVER=SERVERNAME

Thanks,
alex

 

[Dirk Goldgar]

Pass Through Make Table

Hello,

I have a pass through query object; I also have a make table query
object based off the pass through query. This works just fine to make
an Access table with ORACLE data from my pass through query.

My problem/issue: I must get this into VBA.

Can anyone tell me how to put the pass through and make table queries
into VBA?

The ODBC Connect Str looks like this:
ODBC; DSN=SERVER;UID=LOGIN;PWN=MYPSWRD;SERVER=SERVERNAME

Do you mean that, for some reason, you must delete the stored queries and
have them only in VBA? If so, why? If not, then you can just execute the
make-table query, by name, from VBA like this:

CurrentDb.Execute, "YourmakeTableQuery", dbFailOnError

 

[alex]

Do you mean that, for some reason, you must delete the stored queries and
have them only in VBA?  If so, why?  If not, then you can just execute the
make-table query, by name, from VBA like this:

    CurrentDb.Execute, "YourmakeTableQuery", dbFailOnError

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)

Hi Dirk,
Thanks for your response.
Yes, I need to delete my two queries and put them into VBA (at least
my pass through). Reason: my pass through query holds a password to
the ORACLE tables. When the db gets rolled out as an mde, the
password will not be viewable and the user will only have access to
the data that was returned by the pass through!

alex

 

[Dirk Goldgar]

Shouldn't that be "PWD=", not "PWN="?

Yes, I need to delete my two queries and put them into VBA (at least my
pass through). Reason: my pass through query holds a password to the
ORACLE tables. When the db gets rolled out as an mde, the password will
not be viewable and the user will only have access to the data that was
returned by the pass through!

You can incorporate the connect string in the query SQL, as in this example:

'------ start of example code ------
Dim strConnect As String
Dim strSQL As String

strConnect = "ODBC;DSN=SERVER;UID=LOGIN;PWD=MYPSWRD;SERVER=SERVERNAME"

strSQL = _
"SELECT Field1, Field2, Field3 " & _
"INTO YourLocalTable " &
"FROM [" & strConnect & "].SourceTableOrViewName"

CurrentDb.Execute strSQL, dbFailOnError
'------ end of example code ------

This example doesn't include any WHERE clause. You could have such a
clause, but I suspect that the criteria would be evaluated on the client
side, not passed to the server, and thus eliminate some of the benefit of a
pass-through query. For that reason, I'd recommend creating a view in
Oracle that returns the records you want, and run your make-table query
against that. If you can't do that, it may be necessary to work with
recordsets to accomplish your goals.

Note that, even with this arrangement, the password will be present as a
literal string in the MDE. You would probably want to encrypt it there, and
only decrypt it in code when you're about to use it.

 

[alex]

Shouldn't that be "PWD=", not "PWN="?

Yes, I need to delete my two queries and put them into VBA (at least my
pass through).  Reason:  my pass through query holds a password to the
ORACLE tables.  When the db gets rolled out as an mde, the password will
not be viewable and the user will only have access to the data that was
returned by the pass through!

You can incorporate the connect string in the query SQL, as in this example:

'------ start of example code ------
    Dim strConnect As String
    Dim strSQL As String

    strConnect = "ODBC;DSN=SERVER;UID=LOGIN;PWD=MYPSWRD;SERVER=SERVERNAME"

    strSQL = _
        "SELECT Field1, Field2, Field3 " & _
        "INTO YourLocalTable " &
        "FROM [" & strConnect & "].SourceTableOrViewName"

    CurrentDb.Execute strSQL, dbFailOnError
'------ end of example code ------

This example doesn't include any WHERE clause.  You could have such a
clause, but I suspect that the criteria would be evaluated on the client
side, not passed to the server, and thus eliminate some of the benefit ofa
pass-through query.  For that reason, I'd recommend creating a view in
Oracle that returns the records you want, and run your make-table query
against that.  If you can't do that, it may be necessary to work with
recordsets to accomplish your goals.

Note that, even with this arrangement, the password will be present as a
literal string in the MDE.  You would probably want to encrypt it there, and
only decrypt it in code when you're about to use it.

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)

Dirk,
That worked great...I can't believe you did it with so few lines of
code! I've been searching for hours and looking at pages of code
(with multiple lines of ADO and DAO references that made my head
hurt).
Two quick things:
Since the password is in code in the mde, do you still think it's
susceptible to viewing. I thought it was nearly impossible to view
the code in an mde.
Second, how do you refresh the table object names; i.e., after I make
the table, I have to move away from the table object page and click
back to see the new table (that's not a big deal, however).

Thanks again for your help,
alex

 

[Dirk Goldgar]

That worked great...I can't believe you did it with so few lines of code!

That method of referring to an external database is something someone showed
me a while back. You hang around the newsgroups long enough, you pick these
things up.

Since the password is in code in the mde, do you still think it's
susceptible to viewing. I thought it was nearly impossible to view

the code in an mde.

The *code* is compiled and hence not readily legible, but string literals
have to be stored somewhere in the file, and can be read with a hex editor.
How secure do you need this to be? If you want to protect the security of
any password, you have to encrypt it wherever it is stored. Now, you could
use some simple encryption scheme, and/or break the password up into little
pieces with other junk between then the pieces so that no one just
inspecting the code can read the password without knowing (or figuring out)
your trick. That may well be enough security for your purpose, but it isn't
maximally secure.

Second, how do you refresh the table object names; i.e., after I make the
table, I have to move away from the table object page and click back to
see the new table (that's not a big deal, however).

Try executing the line,

RefreshDatabaseWindow

after creating the table.

 

[Douglas J. Steele]

You'd be surprised at what you can see in an MDE just by opening it in
Notepad...
--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Two quick things:
Since the password is in code in the mde, do you still think it's
susceptible to viewing. I thought it was nearly impossible to view
the code in an mde.

Second, how do you refresh the table object names; i.e., after I make
the table, I have to move away from the table object page and click
back to see the new table (that's not a big deal, however).

Thanks again for your help,
alex

 

[alex]

You'd be surprised at what you can see in an MDE just by opening it in
Notepad...
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)



Two quick things:
Since the password is in code in the mde, do you still think it's
susceptible to viewing.  I thought it was nearly impossible to view
the code in an mde.

Second, how do you refresh the table object names; i.e., after I make
the table, I have to move away from the table object page and click
back to see the new table (that's not a big deal, however).

Thanks again for your help,
alex

Thanks guys for your help and insight...learned something again!
alex