Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB 6
| Search | Today's Posts | Mark Forums Read
Pro VB 6 For advanced Visual Basic coders working in version 6 (not .NET). Beginning-level questions will be redirected to other forums, including Beginning VB 6.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 8th, 2003, 08:14 AM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Changing Datatype In Access Database

Is there a way to change the datatype of a field in an Access database table through VB code? I've found a why to do it by adding a new field to the table, copying the data from the field I want to change to the new field, deleting the old field and renaming the new field. This works except the new field is now the last field on the table. I want the field to stay in the same place in the table. Is there an easy way to do this in VB?
 
Old October 8th, 2003, 08:34 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 39
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You need to reference the msadox.dll in your project. You then have access to the table structure. You should then be able to alter the data type. Is changing the data type a good idea, you may loose existing data if it will not convert to the changed format.

Cheers

Byron
 
Old October 8th, 2003, 08:43 AM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your reply. In development, I've already changed the datatype in an existing database as I did my testing and now I need the application to perform the same change to the user's existing database. I have a reference to Microsoft ActiveX Data Objects 2.0 Library already but since I've never had to change the datatype before, I don't know the syntax for coding the datatype change. I tried:

db.Recordset.Fields.Item("LineTotal").Type = adInteger

But I get the message "Operation is not allowed in this context". What am I missing?
 
Old October 8th, 2003, 08:49 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 39
Thanks: 0
Thanked 0 Times in 0 Posts
Default

As far as I'm aware, that property is read only. I dont think you can change the properties using the ADO classes, you need the adox classes which go down to structure level. In the references it is labelled Microsoft ADO EXT.2.5 for DDL and Security.
 
Old October 8th, 2003, 09:08 AM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks. I'd never used ADOX before. Do you have a sample of code showing how to reference and change the datatype of a field in a table?
 
Old October 8th, 2003, 09:33 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 39
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Below is an example I used to change the field name but the principle is the same for type.

I have used a class for the connection as I use various databases, however you can probably simplify this for your example.

You will need an ado and adox reference in the project.

Hope this helps.



Private Function ChangeDisplayType(fieldname As String, fldType As Long) As Boolean

    Dim objCol As ADOX.Column
    Dim objCat As ADOX.Catalog
    Dim strsql As String
    Dim DefTable As String
    Dim sType As String

On Error GoTo errorhandler:


    Select Case fldType
        Case Is = 0
            sType = "List_Box"
        Case Is = 1
            sType = "Combo_Box"
        Case Is = 2
            sType = "Check_Box"
        Case Is = 3
            sType = "Single"
    End Select

    tablename = "CustomersData"

    'Open connection
    OpenConnection False, True

    Set objCat = New ADOX.Catalog
    objCat.ActiveConnection = mADOConnJet.Connection
    'rename field
    objCat.Tables(tablename).Columns(exstgFieldName).N ame = fieldname
    ChangeDisplayType = True

Exit Function
errorhandler:
    ChangeDisplayType = False
    Select Case err.Number
        Case Else
            MsgBox err.Number & " " & err.Description
    End Select

End Function


Private Sub OpenConnection(SysDB As Boolean, openReadWrite As Boolean)

    Dim datapath As String

On Error GoTo errorhandler:

    If SysDB Then
        datapath = SelectedSYSFilePAth
    Else
        datapath = SelectedFilePAth
    End If


    'Open connection to database
    Set mADOConnJet = New CADOConnJet
    With mADOConnJet
      .USERID = "Admin"
      .Password = ""
      .CursorLocation = adUseServer
      .DataSource = datapath
      .JetVersion = 400
      If openReadWrite Then
        .Mode = adModeReadWrite
      Else
        .Mode = adModeRead
      End If
      .OpenConnection
    End With

Exit Sub
errorhandler:
    Select Case err.Number
        Case Else
            MsgBox err.Number & " " & err.Description
    End Select

End Sub

***********************
Class Code
***********************

Option Explicit

' Class : CADOConnJet
' Description : Class to support client/server operations using
' the ADO feature in VB 6.0 This class
' sets up an ADO connection object for use with a
' Microsoft Jet database
' Source : Total Visual SourceBook 2000

' Variables for static property data
Private WithEvents m_Connection As ADODB.Connection
Private m_JetVersion As EnumADOConnJetVersion

' Generic ADO connection properties
Private m_strProvider As String
Private m_strDataSource As String
Private m_strUserID As String
Private m_strPassword As String

' Jet-specific extended properties
Private m_strSystemDBName As String
Private m_strRegistryPath As String
Private m_strDatabasePassword As String

' Connection attributes
Private m_Attributes As ADODB.XactAttributeEnum
Private m_lngCommandTimeout As Long
Private m_lngConnectionTimeout As Long
Private m_CursorLocation As ADODB.CursorLocationEnum
Private m_IsolationLevel As ADODB.IsolationLevelEnum
Private m_Mode As ADODB.ConnectModeEnum
Private m_strConnectString As String

Public Enum EnumADOConnJetVersion
  acjVersion351 = 351
  acjVersion400 = 400
End Enum

Private Const mcstrJetProvider351 As String = "Microsoft.Jet.OLEDB.3.51"
Private Const mcstrJetProvider400 As String = "Microsoft.Jet.OLEDB.4.0"

' Events raised by ADO reflected to users of this class
Public Event BeginTransComplete( _
  ByVal TransactionLevel As Long, _
  ByVal pError As ADODB.Error, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pConnection As ADODB.Connection)

Public Event CommitTransComplete( _
  ByVal pError As ADODB.Error, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pConnection As ADODB.Connection)

Public Event ConnectComplete( _
  ByVal pError As ADODB.Error, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pConnection As ADODB.Connection)

Public Event Disconnect( _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pConnection As ADODB.Connection)

Public Event ExecuteComplete( _
  ByVal RecordsAffected As Long, _
  ByVal pError As ADODB.Error, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pCommand As ADODB.Command, _
  ByVal pRecordset As ADODB.Recordset, _
  ByVal pConnection As ADODB.Connection)

Public Event InfoMessage( _
  ByVal pError As ADODB.Error, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pConnection As ADODB.Connection)

Public Event RollbackTransComplete( _
  ByVal pError As ADODB.Error, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pConnection As ADODB.Connection)

Public Event WillConnect( _
  ConnectionString As String, _
  USERID As String, _
  Password As String, _
  Options As Long, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pConnection As ADODB.Connection)

Public Event WillExecute( _
  Source As String, _
  CursorType As ADODB.CursorTypeEnum, _
  LockType As ADODB.LockTypeEnum, _
  Options As Long, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pCommand As ADODB.Command, _
  ByVal pRecordset As ADODB.Recordset, _
  ByVal pConnection As ADODB.Connection)

Private Sub Class_Initialize()
  ' Set initial values to defaults which may be overridden
  ' with property settings
  ' Source: Total Visual SourceBook 2000

  m_JetVersion = acjVersion351
  m_strProvider = mcstrJetProvider351
  m_strUserID = "Admin"
  m_strPassword = ""
  m_Attributes = 0
  m_lngCommandTimeout = 30
  m_lngConnectionTimeout = 15
  m_CursorLocation = adUseServer
  m_IsolationLevel = adXactCursorStability
  m_Mode = adModeUnknown

  ' Create the connection object
  Set m_Connection = New ADODB.Connection

End Sub

Private Sub Class_Terminate()
  ' Release resources used by the class
  ' Source: Total Visual SourceBook 2000

  CloseConnection
  Set m_Connection = Nothing

End Sub

Public Property Get Attributes() As ADODB.XactAttributeEnum
  ' Comments : Returns the Connection attribute flags used to open the
  ' connection to the database.
  '
  '

  Attributes = m_Attributes

End Property

Public Property Let Attributes(ByVal eValue As ADODB.XactAttributeEnum)
  ' eValue: Set this property to one of the enumerated values in the
  ' XactAttributeEnum enumeration.
  ' Source: Total Visual SourceBook 2000

  m_Attributes = eValue

End Property

Public Property Get CommandTimeout() As Long
  ' Comments : Returns the value of the CommandTimeout property of the
  ' connection.
  '

  CommandTimeout = m_lngCommandTimeout

End Property

Public Property Let CommandTimeout(ByVal lngNewValue As Long)
  ' lngNewValue: Set this property to the number of seconds for the
  ' CommandTimeout property of the connection.
  ' Source: Total Visual SourceBook 2000

  m_lngCommandTimeout = lngNewValue

End Property

Public Property Get Connection() As ADODB.Connection
  ' Returns: a pointer to the local Connection object that was
  ' created by this class
  ' Source: Total Visual SourceBook 2000

  Set Connection = m_Connection

End Property

Public Property Get ConnectionTimeout() As Long
  ' Comments : Returns the value of the ConnectionTimeout property of the
  ' connection.
  '

  ConnectionTimeout = m_lngConnectionTimeout

End Property

Public Property Let ConnectionTimeout(ByVal lngNewValue As Long)
  ' lngNewValue: Set this property to the number of seconds for the
  ' ConnectionTimeout property of the connection.
  ' Source: Total Visual SourceBook 2000

  m_lngConnectionTimeout = lngNewValue

End Property

Public Property Get ConnectString() As String
  ' Comments : Returns the value of the ConnectString property of
  ' the connection. This property is a combination of the
  ' values supplied as properties of this class, and additional
  ' values supplied after the connection is made.
  '

  ConnectString = m_strConnectString

End Property

Public Property Get CursorLocation() As ADODB.CursorLocationEnum
  ' Comments : Returns the value of the CursorLocation property of
  ' the connection.
  '

  CursorLocation = m_CursorLocation

End Property
Public Property Let CursorLocation(ByVal eValue As ADODB.CursorLocationEnum)
  ' eValue: Set this property to one of the enumerated values in the
  ' CursorLocationEnum enumeration.
  ' Source: Total Visual SourceBook 2000

  m_CursorLocation = eValue

End Property

Public Property Get DatabasePassword() As String
  ' Comments : Returns the text of database-level password used to
  ' create the connection to the Jet database.
  '
  '

  DatabasePassword = m_strDatabasePassword

End Property

Public Property Let DatabasePassword(ByVal strValue As String)
  ' strValue: Set this property to the database-level password used to
  ' create the connection to the database. Notice that the
  ' database-level password is not the same as the user password.
  ' A database-level password is used instead of or in addition
  ' to Jet workgroup-based security.
  ' Source: Total Visual SourceBook 2000

  m_strDatabasePassword = strValue

End Property

Public Property Get DataSource() As String
  ' Comments : Returns the string containing the path to the Jet database
  ' used to create the connection
  '
  '

  DataSource = m_strDataSource

End Property

Public Property Let DataSource(ByVal strValue As String)
  ' strValue: Set this property to the fully-qualified path to the
  ' the Microsoft Jet database file.
  ' Source: Total Visual SourceBook 2000

  m_strDataSource = strValue

End Property

Public Property Get IsolationLevel() As ADODB.IsolationLevelEnum
  ' Comments : Returns the value of the IsolationLevel property of
  ' the connection.
  '

  IsolationLevel = m_IsolationLevel

End Property

Public Property Let IsolationLevel(ByVal eValue As ADODB.IsolationLevelEnum)
  ' eValue: Set this property to one of the enumerated values in the
  ' IsolationLevelEnum enumeration.
  ' Source: Total Visual SourceBook 2000

  m_IsolationLevel = eValue

End Property

Public Property Get JetVersion() As EnumADOConnJetVersion
  ' Comments : Returns the version number of the Jet OLE DB
  ' driver used to create the connection
  '
  '

  JetVersion = m_JetVersion

End Property

Public Property Let JetVersion(ByVal eValue As EnumADOConnJetVersion)
  ' eValue: Set the version number of the Jet OLE DB driver desired
  ' to create the connection
  ' Source: Total Visual SourceBook 2000

  On Error GoTo PROC_ERR

  m_JetVersion = eValue

  ' Also set the actual corresponding connection string argument
  Select Case m_JetVersion
    Case acjVersion351
      m_strProvider = mcstrJetProvider351
    Case acjVersion400
      m_strProvider = mcstrJetProvider400
  End Select

PROC_EXIT:
  Exit Property

PROC_ERR:
  MsgBox "Error: " & err.Number & ". " & err.Description, , _
    "JetVersion"
  Resume PROC_EXIT

End Property

Public Property Get Mode() As ADODB.ConnectModeEnum
  ' Comments : Returns the value of the Mode property of
  ' the connection.
  '

  Mode = m_Mode

End Property

Public Property Let Mode(ByVal eValue As ADODB.ConnectModeEnum)
  ' eValue: Set this property to one of the enumerated values in the
  ' ConnectModeEnum enumeration.
  ' Source: Total Visual SourceBook 2000

  m_Mode = eValue

End Property

Public Property Get Password() As String
  ' Comments : Returns the text of the password used to log into
  ' the database.
  '
  '

  Password = m_strPassword

End Property

Public Property Let Password(ByVal strValue As String)
  ' strValue: Set this property to the password used to log into
  ' the the Jet database. Defaults to '', which is valid for
  ' unsecured Jet databases.
  ' Source: Total Visual SourceBook 2000

  m_strPassword = strValue

End Property

Public Property Get Provider() As String
  ' Comments : Returns the string containing the Jet OLE DB provider
  ' used to create the connection
  '
  '

  Provider = m_strProvider

End Property

Public Property Let Provider(ByVal strValue As String)
  ' strValue: Set the Provider name string of the Jet OLE DB driver desired
  ' to create the connection. Defaults to the constant value
  ' contained in mcstrJetProvider351. If the JetVersion property
  ' is set, this property is also set automatically.
  ' Source: Total Visual SourceBook 2000

  m_strProvider = strValue

End Property

Public Property Get RegistryPath() As String
  ' Comments : Returns the text of the registry key location where Jet
  ' initialization information is stored.
  '
  '

  RegistryPath = m_strRegistryPath

End Property

Public Property Let RegistryPath(ByVal strValue As String)
  ' strValue: Set this property to the registry key location where Jet
  ' database initialization information is stored. See the Jet
  ' documentation for information on valid settings in this
  ' location.
  ' Source: Total Visual SourceBook 2000

  m_strRegistryPath = strValue

End Property

Public Property Get SystemDBName() As String
  ' Comments : Returns the fully-qualified path to the Microsoft Jet
  ' workgroup security database.
  '
  '

  SystemDBName = m_strSystemDBName

End Property

Public Property Let SystemDBName(ByVal strValue As String)
  ' strValue: Set this property to the fully-qualified path to the
  ' Microsoft Jet workgroup security database. Used if the
  ' database is secured. If the database is not secured, leave
  ' this property at its default value, which is a blank
  ' string.
  ' Source: Total Visual SourceBook 2000

  m_strSystemDBName = strValue

End Property

Public Property Get USERID() As String
  ' Comments : Returns the string containing the user name used to
  ' log into the Jet database.
  '
  '

  USERID = m_strUserID

End Property

Public Property Let USERID(ByVal strValue As String)
  ' strValue: Set this property to the name of the user used to log into
  ' the Jet database. Defaults to 'Admin', which is valid for
  ' unsecured Jet databases.
  ' Source: Total Visual SourceBook 2000

  m_strUserID = strValue

End Property

Public Sub CloseConnection()
  ' Comments : Closes the current connection to the Jet database
  ' Parameters: None
  ' Returns : Nothing
  '
  '
  On Error GoTo PROC_ERR

  If m_Connection.State = adStateOpen Then
    m_Connection.Close
  End If

PROC_EXIT:
  Exit Sub

PROC_ERR:
  MsgBox "Error: " & err.Number & ". " & err.Description, , _
    "CloseConnection"
  Resume PROC_EXIT

End Sub

Public Sub OpenConnection()
  ' Comments : Opens the ADODB Connection to the specified Microsoft
  ' Jet database, using the properties of this class to
  ' control parameters of the connection
  ' Parameters: None
  ' Returns : Nothing
  '
  '
  On Error GoTo PROC_ERR

  ' Close any existing connection created with the class
  CloseConnection

  ' Construct a connect string using the class properties
  BuildConnectString

  ' Connect to the database
  With m_Connection
    .Attributes = m_Attributes
    .CommandTimeout = m_lngCommandTimeout
    .ConnectionString = m_strConnectString
    .ConnectionTimeout = m_lngConnectionTimeout
    .CursorLocation = m_CursorLocation
    .IsolationLevel = m_IsolationLevel
    .Mode = m_Mode
    .Provider = m_strProvider

    .Open

    ' Get modified version of ConnectionString
    m_strConnectString = .ConnectionString

  End With

PROC_EXIT:
  Exit Sub

PROC_ERR:
  MsgBox "Error: " & err.Number & ". " & err.Description, , _
    "OpenConnection"
  Resume PROC_EXIT

End Sub

Private Sub BuildConnectString()
  ' Comments : Builds up the string used as the ConnectString property
  ' of the class, which is the value used to open the connection.
  ' Parameters: None
  ' Returns : Nothing
  '
  '
  On Error GoTo PROC_ERR

  Dim strConn As String

  ' Required for all connections
  strConn = strConn & "User ID=" & m_strUserID & ";"
  strConn = strConn & "Password=" & m_strPassword & ";"
  strConn = strConn & "Data Source=" & m_strDataSource & ";"

  ' Required if the database is secured
  If m_strSystemDBName <> "" Then
    strConn = strConn & "Jet OLEDB:System database=" & m_strSystemDBName & ";"
  End If

  ' Required if the user is using a registry entry to control the
  ' Jet database engine settings.
  If m_strRegistryPath <> "" Then
    strConn = strConn & "Jet OLEDB:Registry Path=" & m_strRegistryPath & ";"
  End If

  ' Required if a database-level password, rather than Jet security is used
  If m_strDatabasePassword <> "" Then
    strConn = strConn & _
      "Jet OLEDB:Database Password=" & m_strDatabasePassword & ";"
  End If

  ' Remove trailing semi-colon
  m_strConnectString = Left$(strConn, Len(strConn) - 1)

PROC_EXIT:
  Exit Sub

PROC_ERR:
  MsgBox "Error: " & err.Number & ". " & err.Description, , _
    "BuildConnectString"
  Resume PROC_EXIT

End Sub

Private Sub m_Connection_BeginTransComplete( _
  ByVal TransactionLevel As Long, _
  ByVal pError As ADODB.Error, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pConnection As ADODB.Connection)
  ' Comments : The private local ADODB.Connection variable raises connection-
  ' related events. This proc simply passes the BeginTransComplete
  ' events on to the user of this class
  ' Parameters: TransactionLevel - Contains the new transaction level of
  ' the BeginTrans that caused this event.
  ' pError - An Error object. It describes the error that
  ' occurred if the value of EventStatusEnum is
  ' adStatusErrorsOccurred; otherwise it is not set.
  ' adStatus - An EventStatusEnum status value. When any
  ' of these methods is called, this parameter is set to
  ' adStatusOK if the operation that caused the event was
  ' successful, or adStatusErrorsOccurred if the operation failed.
  ' pConnection - The Connection object for which this event
  ' occurred
  ' Returns : Nothing
  '
  '
  RaiseEvent BeginTransComplete( _
    ByVal TransactionLevel, _
    ByVal pError, _
    adStatus, _
    ByVal pConnection)

End Sub

Private Sub m_Connection_CommitTransComplete( _
  ByVal pError As ADODB.Error, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pConnection As ADODB.Connection)
  ' Comments : The private local ADODB.Connection variable raises connection-
  ' related events. This proc simply passes the CommitTransComplete
  ' events on to the user of this class
  ' Parameters: pError - An Error object. It describes the error that
  ' occurred if the value of EventStatusEnum is
  ' adStatusErrorsOccurred; otherwise it is not set.
  ' adStatus - An EventStatusEnum status value. When any
  ' of these methods is called, this parameter is set to
  ' adStatusOK if the operation that caused the event was
  ' successful, or adStatusErrorsOccurred if the operation failed.
  ' pConnection - The Connection object for which this event
  ' occurred
  ' Returns : Nothing
  '
  '
  RaiseEvent CommitTransComplete(ByVal pError, adStatus, ByVal pConnection)

End Sub

Private Sub m_Connection_ConnectComplete( _
  ByVal pError As ADODB.Error, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pConnection As ADODB.Connection)
  ' Comments : The private local ADODB.Connection variable raises connection-
  ' related events. This proc simply passes the ConnectComplete
  ' events on to the user of this class
  ' Parameters: pError - An Error object. It describes the error that
  ' occurred if the value of EventStatusEnum is
  ' adStatusErrorsOccurred; otherwise it is not set.
  ' adStatus - An EventStatusEnum status value. When any
  ' of these methods is called, this parameter is set to
  ' adStatusOK if the operation that caused the event was
  ' successful, or adStatusErrorsOccurred if the operation failed.
  ' pConnection - The Connection object for which this event
  ' occurred
  ' Returns : Nothing
  '
  '
  RaiseEvent ConnectComplete(ByVal pError, adStatus, ByVal pConnection)

End Sub

Private Sub m_Connection_Disconnect( _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pConnection As ADODB.Connection)
  ' Comments : The private local ADODB.Connection variable raises connection-
  ' related events. This proc simply passes the Disconnect
  ' events on to the user of this class
  ' Parameters: adStatus - An EventStatusEnum status value. When any
  ' of these methods is called, this parameter is set to
  ' adStatusOK if the operation that caused the event was
  ' successful, or adStatusErrorsOccurred if the operation failed.
  ' pConnection - The Connection object for which this event
  ' occurred
  ' Returns : Nothing
  '
  '
  RaiseEvent Disconnect(adStatus, ByVal pConnection)

End Sub

Private Sub m_Connection_ExecuteComplete( _
  ByVal RecordsAffected As Long, _
  ByVal pError As ADODB.Error, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pCommand As ADODB.Command, _
  ByVal pRecordset As ADODB.Recordset, _
  ByVal pConnection As ADODB.Connection)
  ' Comments : The private local ADODB.Connection variable raises connection-
  ' related events. This proc simply passes the ExecuteComplete
  ' events on to the user of this class
  ' Parameters: RecordsAffected - The number of records affected by the
  ' command.
  ' pError - An Error object. It describes the error that
  ' occurred if the value of EventStatusEnum is
  ' adStatusErrorsOccurred; otherwise it is not set.
  ' adStatus - An EventStatusEnum status value. When any
  ' of these methods is called, this parameter is set to
  ' adStatusOK if the operation that caused the event was
  ' successful, or adStatusErrorsOccurred if the operation failed.
  ' pCommand - The Command object, if any, that was executed.
  ' pRecordset - A Recordset object. The result of the execution.
  ' This recordset may be empty.
  ' pConnection - A Connection object. The connection on which
  ' the command was executed.
  ' Returns : Nothing
  '
  '
  RaiseEvent ExecuteComplete( _
    ByVal RecordsAffected, _
    ByVal pError, _
    adStatus, _
    ByVal pCommand, _
    ByVal pRecordset, _
    ByVal pConnection)

End Sub

Private Sub m_Connection_InfoMessage( _
  ByVal pError As ADODB.Error, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pConnection As ADODB.Connection)
  ' Comments : The private local ADODB.Connection variable raises connection-
  ' related events. This proc simply passes the InfoMessage
  ' events on to the user of this class
  ' Parameters: pError - An Error object. It describes the error that
  ' occurred if the value of EventStatusEnum is
  ' adStatusErrorsOccurred; otherwise it is not set.
  ' adStatus - An EventStatusEnum status value. When any
  ' of these methods is called, this parameter is set to
  ' adStatusOK if the operation that caused the event was
  ' successful, or adStatusErrorsOccurred if the operation failed.
  ' pConnection - The Connection object for which this event
  ' occurred
  ' Returns : Nothing
  '
  '
  RaiseEvent InfoMessage(ByVal pError, adStatus, ByVal pConnection)

End Sub

Private Sub m_Connection_RollbackTransComplete( _
  ByVal pError As ADODB.Error, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pConnection As ADODB.Connection)
  ' Comments : The private local ADODB.Connection variable raises connection-
  ' related events. This proc simply passes
  ' the RollbackTransComplete events on to the user of this class.
  ' Parameters: pError - An Error object. It describes the error that
  ' occurred if the value of EventStatusEnum is
  ' adStatusErrorsOccurred; otherwise it is not set.
  ' adStatus - An EventStatusEnum status value. When any
  ' of these methods is called, this parameter is set to
  ' adStatusOK if the operation that caused the event was
  ' successful, or adStatusErrorsOccurred if the operation failed.
  ' pConnection - The Connection object for which this event
  ' occurred
  ' Returns : Nothing
  '
  '
  RaiseEvent RollbackTransComplete(ByVal pError, adStatus, ByVal pConnection)

End Sub

Private Sub m_Connection_WillConnect( _
  ConnectionString As String, _
  USERID As String, _
  Password As String, _
  Options As Long, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pConnection As ADODB.Connection)
  ' Comments : The private local ADODB.Connection variable raises connection-
  ' related events. This proc simply passes the WillConnect
  ' events on to the user of this class
  ' Parameters: ConnectionString - A String containing connection
  ' information for the pending connection.
  ' UserID - A String containing a user name for the
  ' pending connection.
  ' Password - A String containing a password for the
  ' pending connection.
  ' Options - A Long value that indicates how the provider
  ' should evaluate the ConnectionString.
  ' adStatus - An EventStatusEnum status value. When any
  ' of these methods is called, this parameter is set to
  ' adStatusOK if the operation that caused the event was
  ' successful, or adStatusErrorsOccurred if the operation failed.
  ' pConnection - The Connection object for which this event
  ' occurred
  ' Returns : Nothing
  '
  '
  RaiseEvent WillConnect( _
    ConnectionString, _
    USERID, _
    Password, _
    Options, _
    adStatus, _
    pConnection)

End Sub

Private Sub m_Connection_WillExecute( _
  Source As String, _
  CursorType As ADODB.CursorTypeEnum, _
  LockType As ADODB.LockTypeEnum, _
  Options As Long, _
  adStatus As ADODB.EventStatusEnum, _
  ByVal pCommand As ADODB.Command, _
  ByVal pRecordset As ADODB.Recordset, _
  ByVal pConnection As ADODB.Connection)
  ' Comments : The private local ADODB.Connection variable raises connection-
  ' related events. This proc simply passes the WillExecute
  ' events on to the user of this class
  ' Parameters: Source - A String containing an SQL command or a stored
  ' procedure name.
  ' CursorType - A CursorTypeEnum containing the type of cursor
  ' for the recordset that will be opened.
  ' LockType - A LockTypeEnum containing the lock type for the
  ' recordset that will be opened.
  ' Options - A Long value of options that can be used to
  ' execute the command or open the recordset.
  ' adStatus - An EventStatusEnum status value that may be
  ' adStatusCantDeny or adStatusOK when this method is called.
  ' If it is adStatusCantDeny, this method may not request
  ' cancellation of the pending operation.
  ' pCommand - The Command object for which this event
  ' notification applies.
  ' pRecordset - The Recordset object for which this event
  ' notification applies.
  ' pConnection - The Connection object for which this event
  ' notification applies.
  ' Returns : Nothing
  '
  '
  RaiseEvent WillExecute( _
    Source, _
    CursorType, _
    LockType, _
    Options, _
    adStatus, _
    ByVal pCommand, _
    ByVal pRecordset, _
    ByVal pConnection)

End Sub
 
Old October 8th, 2003, 10:03 AM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for all the code! If I execute the following code, it works:

Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = dbConnect
cat.Tables("Items").Columns("LineTotal").Name = "LineTotal2"
Set cat = Nothing

However, if I change the code to modify the datatype:

Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = dbConnect
cat.Tables("Items").Columns("LineTotal").Type = adInteger
Set cat = Nothing

I get the error message "Operation is not allowed in this context". Is there something else I need to do before I change the datatype?
 
Old October 8th, 2003, 10:14 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

from the msdn

Type Property (Column)
Indicates the data type of a column.

Settings and Return Values
Sets or returns a Long value that can be one of the DataTypeEnum constants. The default value is adVarWChar.

Remarks
This property is read/write until the Column object is appended to a collection or to another object, after which it is read-only.

that mean that you have to do the same that you were doing before.. only now with adox...

HTH...

Gonzalo Bianchi
 
Old October 8th, 2003, 10:40 AM
Authorized User
 
Join Date: Jun 2003
Location: , Quebec, Canada.
Posts: 78
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try doing it with SQL and ADO. You query should look like this:
Code:
ALTER TABLE Items ALTER COLUMN LineTotal INTEGER
Note: This only work with Database in format Jet 4.0. And if I remember correctly, you can change for a bigger Type but not a smaller Type

St├ęphane Lajoie
 
Old October 8th, 2003, 11:57 AM
Authorized User
 
Join Date: Oct 2003
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for all your help! I finally found the ALTER COLUMN command in the Microsoft Knowledge base and that works perfectly! Thanks again!




Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing database at runtime joerage BOOK: Professional Crystal Reports for VS.NET 3 December 15th, 2006 05:55 PM
Changing column datatype abelmirma Access VBA 1 January 16th, 2006 07:25 PM
Changing Database for Crystal Reports pavel Pro VB 6 4 December 5th, 2005 01:58 PM
Changing Database. rupen Classic ASP Basics 3 July 12th, 2005 06:02 PM
Changing Datatype In Access Database fastcorvette Access 2 October 8th, 2003 11:53 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.