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 (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
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
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.