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

Posted on 1999-06-22
Medium Priority
Last Modified: 2013-12-25
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.

Question by:jeffarnold
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 12

Expert Comment

ID: 1494702
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.



Author Comment

ID: 1494703
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.
LVL 12

Accepted Solution

mark2150 earned 100 total points
ID: 1494704
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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question