Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Need help with syntax

    I am trying to write a routine that will update a user's ID in the Oracle dba_users table. I have a table in Access that I spin through, using the user ID and the password in this table in the "Alter User" SQL statement.

    The problem is that I keep getting a message saying there is a syntax problem with the Alter statement. I don't know if I can even run this type of SQL from Access.

    Code:
    Public Sub AlterUser()
    
    ' This will spin through each and every record in the tbl_Users table and will update
    ' the dba_users table in Oracle with the newly created passwords.
    
    'Defining variables
    Dim cnOra As ADODB.Connection
    Dim rsOra As ADODB.Recordset
    
    Set cnOra = New ADODB.Connection
    Set rsOra = New ADODB.Recordset
    
    Dim db As Database
    Dim SQLstr As String
    Dim rst As DAO.Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tbl_Users")
    
    cnOra.Open "driver={Oracle in OraHome92};" & "dbq=NATT1;" & "uid=tmmgr;pwd=manutm1"
    
    rst.MoveFirst
    
    'Run the alter user query for all users
    Do Until rst.EOF
        
        SQLstr = "alter user " & rst!ManuUserName & " identified by " & rst!Password1 & ";"
        
        db.Execute SQLstr
        
        rst.MoveNext
    Loop
    
    rsOra.Close
    cnOra.Close
    Set rsOra = Nothing
    
    End Sub

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    I don't know PL\SQL and most users on here won't either. I think that (assuming you know the vb code is fine and the alter... statement is wrong) you will get more joy in the Oracle forum for this

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •