Randem Systems Support Board

If you use the field dbtype. You can select OracleDatabase. That is what you use for Oracle. Or you can use ODBCDatabase and set up a DSN to connect.

Raphl,
Thanks for the zip file which I have unzipped and ran.
For access database It connect and confirmed it is okay.
The script being executed (which was not commented out ) is as below
 
.dbName = Access2000.mdb
    .ServerName = App.Path
    .Active_DB = dbType.AccessJet4Database
    .ServerSideCursor = False
    .ODBCSystemName = Access2000           ' The actual ODBC driver name
    .SystemName = My Application
    .SqlTimeout = 600       ' Minimum time before we timeout on a SQL query
    .UserName = Admin
    .Password =  
    .UniCode = False
    .Transactions = True
    .ConnType = cnType.Auto
 
Of course the access database was as supplied in the zip file. The problem I have is I was looking for a script similar to above for oracle but it is not one of those supplied.
I hope I am not asking a stupid question. Which script do I use to connect to oracle. I have oracle 9i installed on my PC  Thanks

Raphl,
Below is all I got under command 1 button. You can see that the script for Oracle is not there or I am looking in the wrong place

'   This project shows how one would open and access the chosen database. It is the same
'   for each database supported. The control uses ADO to connect to the database
'   with some DAO access points

Dim SQL As String
Dim dbTable As ADODB.Recordset
Dim RecordsAffected As Long
Dim dbDateFormat As String

Command1.Enabled = False
Command2.Enabled = False

Set RSD = RSDatabase1

With RSD
     
    '//////////////////////////////////////////////////////////////////////////
    '   JET / ODBC DSN Less / ODBC - Access 2000 and up
    '   (Can handle Access 97 if MDAC 2.8 and Jet 4.0 SP 8 is installed)
    '   get them at https://randemsystems.com/os-updater-support
    '//////////////////////////////////////////////////////////////////////////

  '  .dbName = Access2000.mdb
  '  .ServerName = App.Path
  '  .Active_DB = dbType.AccessJet4Database
  '  .ServerSideCursor = False
  '  .ODBCSystemName = Access2000           ' The actual ODBC driver name
  '  .SystemName = My Application
  '  .SqlTimeout = 600       ' Minimum time before we timeout on a SQL query
  '  .UserName = Admin
  '  .Password = 
  '  .UniCode = False
  '  .Transactions = True
  '  .ConnType = cnType.Auto

    '//////////////////////////////////////////////////////////////////////////
    '   JET / ODBC DSN Less / ODBC - Access 97
    '   (ODBC / ODBC DSN Less connection handles Access 97 and up, OLE DB does not)
    '//////////////////////////////////////////////////////////////////////////
     
'    .dbName = Access97.mdb
'    .ServerName = App.Path
'    .Active_DB = dbtype.AccessJet351Database
'    .ODBCSystemName = Access97           ' The actual ODBC driver name
'    .ServerSideCursor = False
'    .SystemName = My Application
'    .SqlTimeout = 600       ' Minimum time before we timeout on a SQL query
'    .UserName = Admin
'    .Password = 
'    .UniCode = False
'    .Transactions = True
'    .ConnType = cntype.OLEDB
     
    '//////////////////////////////////////////////////////////////////////////
    '   OLE DB / ODBC DSN Less / ODBC - SQL Server
    '//////////////////////////////////////////////////////////////////////////
     
    .dbName = c:\Program Files\Microsoft SQL Server\MSSQL\master.mdf        ' Your database name
    .ServerName = OLUWOLE               ' Name/ip address of the server where the SQL database resides
    .Active_DB = dbType.SQLServerDatabase
    .ServerSideCursor = True
    .ODBCSystemName = SQLSERVER           ' The actual ODBC driver name
    .SystemName = My Application
    .UserName = administrator
    .Password = 
    .UniCode = True
    .Transactions = True
    .ConnType = cnType.ODBCDSNLess

    '//////////////////////////////////////////////////////////////////////////
    '   ODBC / ODBC DSN Less - DBASE IV
    '   Does not support transactions
    '//////////////////////////////////////////////////////////////////////////
     
'    .dbName = CustMast.dbf
'    .ServerName = App.Path
'    .Active_DB = dbtype.DBaseIV
'    .ServerSideCursor = False
'    .ODBCSystemName = DBaseIV           ' The actual ODBC driver name
'    .SystemName = My Application
'    .SqlTimeout = 600       ' Minimum time before we timeout on a SQL query
'    .UserName = Admin
'    .Password = 
'    .UniCode = False
'    .Transactions = True
'    .ConnType = cntype.OLEDB
     
    '//////////////////////////////////////////////////////////////////////////
    '   OLE DB / ODBC DSN Less / ODBC - MySQL
    '   Only the ODBC DSN Less Supports Transactions
    '//////////////////////////////////////////////////////////////////////////
     
'    .dbName = test                        ' Your database name
'    .ServerName = ACE                    ' Name/ip address of the server where the SQL database resides (localhost for local PC)
'    .Active_DB = dbtype.MySQL
'    .ServerSideCursor = True
'    .ODBCSystemName = 
'    .SystemName = My MySQL
'    .SqlTimeout = 600                       ' Minimum time before we timeout on a SQL query
'    .UserName = 
'    .Password = 
'    .UniCode = True
'    .Port = 3306                            ' 3306 Default. You can leave to connect to local DB
'    .dbOption = 131072                      ' 131072 for remote database, 16834 for local database
'    .Transactions = True
'    .ConnType = cntype.Auto
     
    '-------------------------------------------------------------------------------
    ' log files will be stored in \dblogs\ of the .LogPath with a file name of
    ' Database Type_Connection type_yyyymmdd. Where yyyy is the current year, mm is the current month
    ' and dd is the current day.
    '
    ' Generate helpful statements in a log file that can help debug your code.
    '-------------------------------------------------------------------------------
         
    .LogDebug = True    ' User messages should be wrritten here (.dbg)
             
    .LogSQL = True      ' Logs SQL statements executed by .ExecuteSQL (.log)
     
    .LogTrans = True    ' Logs when transaction start and stop (.trn)
     
    .LogPath = App.Path ' Set location of logs
     
    '-------------------------------------------------------------------------------
    '   Check to see if the database that we selected is supported.
    '   Meaning that we have date/time routines for it.
    '-------------------------------------------------------------------------------
     
    .CheckSupportedDatabase
     
    '   Open the database and login to it.
     
    .OpenDatabase
    If Not .LoggedIn Then
       MsgBox Cannot log into Database, vbCritical
       GoTo RtnExit
    End If
     
    '-------------------------------------------------------------------------------
    '   SQL statements that return recordsets should be handled in this manner
    '-------------------------------------------------------------------------------
     
    SQL = select * from users
'    SQL = select * from custmast.dbf
    Set dbTable = New ADODB.Recordset
    dbTable.Open SQL, .dbCurrent, adOpenStatic, adLockOptimistic, adCmdText
     
    'Show some data from table to let user know we have it
    MsgBox Retrieved data from table & vbCrLf & dbTable.Fields(0).Name & :  & dbTable.Fields(0).Value & vbCrLf & dbTable.Fields(1).Name & :  & dbTable.Fields(1).Value
    ' Yada Yada Yada
     
    '-------------------------------------------------------------------------------
    '   Return date in correct database format for the database selected
    '   Databases have different formats for date
    '   If the database is not supported, a date will return but may not be
    '   in the correct format for the database you are using.
    '-------------------------------------------------------------------------------
     
    dbDateFormat = .GetDatabaseDate(Now)
     
    dbTable.Close
     
    '-------------------------------------------------------------------------------
    '   SQL statements that do not return recordsets should be handled in this manner
    '   RecordsAffected returns the number of records affected by the SQL statement.
    '   A return of zero records affected does not mean that the SQL statement
    '   did not execute, you must check the .Error condition.
    '
    '   Start transaction processing (Some providers do not support transactions)
    '-------------------------------------------------------------------------------
     
    .BeginTrans     ' If provider errors, transactions will be forced off
     
    SQL = Update users set user_id = 4
    .ExecuteSQL SQL, RecordsAffected      ' If .LogSQL = True this statement will be written to log
     
    '   Check for error condition on return from any SQL statement that
    '   does not return a recordset.
     
    If .Error  0 Then
        .RollbackTrans  ' Get rid of the transaction
        MsgBox .ErrorMsg
    Else
        .CommitTrans  '   End and commit the transaction
        MsgBox Update: OK & vbCrLf & Database Operational
    End If
         
     
    Set dbTable = Nothing
    .CloseDatabase
     
RtnExit:

     
    Command1.Enabled = True
    Command2.Enabled = True
     
End With