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
Changing Datatype In Access Database
