Re: Manipulate Oracle with Access97

From: Pedro Gil <pmpg_NOSPAM_98_at_Hotmail.com>
Date: 2000/04/12
Message-ID: <38f43f46.2298906@news.telepac.pt>#1/1

Hi Phil,

I do have a example right at hand, but it use ADO instead of ODBC, if you wish I could try and make a small example with ODBC later.

Function CreateTable_Person()
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strConnect As String
Dim strCommand As String

  Set cnn = New ADODB.Connection
  With cnn
    .Provider = "MSDAORA"
    strConnect = "Data Source=testes;User ID=**;Password=**"     .ConnectionString = strConnect
    .Open
  End With    

  Set cmd = New ADODB.Command
  With cmd
    Set .ActiveConnection = cnn
    .CommandType = adCmdText
    'SQL to delete the table

      strCommand = "DROP TABLE person"
      Debug.Print strCommand
      .CommandText = strCommand
      .Execute
    'SQL to Create the table
      strCommand = "CREATE TABLE person "
      strCommand = strCommand & "(ssn NUMBER(9) PRIMARY KEY,fname
VARCHAR2(15),lname VARCHAR2(20))"
      Debug.Print strCommand
      .CommandText = strCommand
      .Execute
    'SQL to insert some demo values
      strCommand = "INSERT INTO person
VALUES(555662222,'Sam','Goodwin')"
      Debug.Print strCommand
      .CommandText = strCommand
      .Execute

    'SQL to Commint the values (I don't known if I really do need this?)

      strCommand = "COMMIT"
      Debug.Print strCommand
      .CommandText = strCommand
      .Execute

  End With
  'Clean Up
  cnn.Close
  Set cnn = Nothing
  Set cmd = Nothing
End Function
On Wed, 12 Apr 2000 08:59:14 GMT, "Phil" <phg3113_at_hotmail.com> wrote:

>Hi everybody,
>
>I'm looking for some explanations and some examples (code) to create, drop,
>fill ... tables in an Oracle Database through an Access97 application .
>
>All I can do for the moment is to list the tables
>
>...
> Dim wrkODBC As Workspace
> Dim OracleDB As DATABASE
> Dim Tb As TableDef
> Dim s As String
>
> Set OracleDB = dbengine(0).OpenDatabase _
> ("", , False, "ODBC;UID=userX;PWD=a_pass;DSN=DWH Production")
>
> For Each Tb In OracleDB.TableDefs
> Debug.Print Tb.Name
> Next
>
> s = "Create Table rszdwh.AAAAAAA (CodeIntern Number(7), Temps
>Varchar2(8), LAN11 Number (4,0)"
> s = s & " , MAN11 Number (4,0)"
> s = s & " , SECRSOC Number (4,0)"
> s = s & " , VAL1 Number(10)"
> s = s & " ) storage (initial 6M next 2048k pctincrease 0) pctfree 1 ;"
> OracleDB.Execute s ' DOESN'T WORK!!!
> OracleDB.Close
> wrkODBC.Close
>...
>
>but I can't create a new table ('ODBC Call Failed')
>
>I'd like some help please (doc file or internet link...)
>
>Thanks in advance
>
>Phil
>
>
>
Received on Wed Apr 12 2000 - 00:00:00 CDT