• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

Execute method (to execute sql command remotely w/ODBC)

I need to run a sql command on a remote database.  I've written a program that works ok as long as the user has the sqlnet driver and sqlplus on their client.  I would like to eliminate the need for sqlplus.  Any ideas how I can use sqlnet/ODBC to execute an sql command on a remote database?  Ex command:
'alter user testuser identified by testpw'
How can I execute this remotely on the Oracle db (using ODBC)?  Any ideas welcome.

Update to question (additional info):
1) I know ODBC may be used to connect to the remote db.  I have the following associated with a command button (for a test):
    Dim MyDataBase As Database
    Set MyDataBase = OpenDatabase(mtmj, False, False, ODBC)
    Dim SQL As String
    SQL = "ALTER user testid identified by testpw"
    MyDataBase.Execute SQL, dbFailOnError
This code succesfully opens and closes the db.  I'm able to login and establish a connection.  Unfortunately the sql statement fails.  VB returns an error that indicates the 'alter table' command is invalid.  Is there an error in my statement? Alter user is not supported?  Error with how I open the db?  Any help at all is greatly appreciated.
I want to used sqlnet/odbc because they already exist on all of the client machines.  I do not want to install another piece of software if at all possible.

  • 2
1 Solution
You need to put a dbSQLpassthrough statement on your .Execute line. (It might be dbODBCpassthrough) Anyway, without that, the .Execute is trying to do it locally instead of passing it out to the server.


jeffarnoldAuthor Commented:
Thank you for the input.  I've been trying to get this information for a couple of weeks.  I've tried the SQLpassthrough approach with no luck.  I'm sure because of a syntax error.  Have you done this?  Do you have any additional information to offer?  Below is the piece of code I'm using.  I must run an ALTER USER command.  If you have any insight into this problem, it would be greatly appreciated.

Private Sub Command3_Click()
    Dim Db As Database, Qd As QueryDef
    Set Db = OpenDatabase(mtmj, False, False, ODBC)
    Set Qd = Db.CreateQueryDef("")
    Qd.Connect = "odbc;dsn=mtmj;uid=mtpmgr;pwd=admin_1;database=mtmj;"
    UpdateDb = True
    Qd.SQL = "update task set debug_level=9"
End Sub

Thanks for your time.
Yeah, I do ODBC to Oracle every day. Here is some sample code that works.

Set comn = DBEngine.Workspaces(0).OpenDatabase("", False, False, "ODBC;DSN=SUNR;UID=common_owner;PWD=c1mm2n;database=DAYP")
Set PscDb = DBEngine.Workspaces(0).OpenDatabase("", False, False, "ODBC;DSN=PSC;UID=PSC_owner;pwd=PSC;")

Sql = "select * from common_owner.dnis"
Set rs = comn.OpenRecordset(Sql, dbOpenSnapshot, dbSQLPassThrough)

Sql = "insert into PSC_owner.call_info " & _
             "(id, phone, call_date, sys_oper, spoke_to) values (" & _
             SeqID & ", '" & _
             Phone & "'," & _
             "to_date('" & Now & "','mm/dd/yy HH:MI:SS AM'),'" & _
             LoginName & "','" & _
             fix_quote(CallerName) & "')"

PscDb.Execute Sql, dbSQLPassThrough

These were lifted from operational code.



Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now