Reformat a table using VBA

[Fritz24]

I have a table in an Access database in which I need to change some of the fields from number format to date/time (long time) format. I realise I can do this manually but it needs to be done automatically as part of an automated process. I've tried using sendkeys but it isn't a satisfactory solution.

Does anyone know how I could do this in VBA?

Thanks in advance.

 

[smozgur]

Hi Fritz24,

You can use the following code in VBA. It is from MS article (modified) applied for Access 97 but it works in later versions. Actually logic is creating a new temporary field by using given data type then copying all data from original field and removing the original one and finally renaming the temporary one by using the original field name.

Please open your database and goto VBA. Point to Insert->Module to insert a new module and copy and paste the following code in this new module.

Change parameters in Test module ("tablename", "fieldname") by using actual names and execute the Test module by using F5 while you are in VBA module (or another method you prefer).

Warning : Please backup your original database before executing an ALTER query .

Sub Test()
    If AlterFieldType("tablename", "fieldname", "DATE") = True Then
        MsgBox "Field type has been succesfully changed!"
    Else
        MsgBox "Failed!"
    End If
End Sub

Function AlterFieldType(strTable As String, strField As String, strNewType As String) As Boolean
Dim db As Object
Dim qryDef As Object

    On Error GoTo ErrHandler
    ' Create a dummy QueryDef object.
    Set qryDef = CurrentDb.CreateQueryDef("", "SELECT * FROM " & strTable)
    
    ' Add a temporary field to the table.
    qryDef.SQL = "ALTER TABLE [" & strTable & "] ADD COLUMN tmpFieldName " & strNewType
    qryDef.Execute
    
    ' Copy the data from old field into the new field.
    qryDef.SQL = "UPDATE DISTINCTROW [" & strTable & "] SET tmpFieldName = [" & strField & "]"
    qryDef.Execute
    
    ' Delete the old field.
    qryDef.SQL = "ALTER TABLE [" & strTable & "] DROP COLUMN [" & strField & "]"
    qryDef.Execute
    
    ' Rename the temporary field to the old field's name.
    CurrentDb.TableDefs("[" & strTable & "]").Fields("tmpFieldName").Name = strField
    ' Clean up.
    
ErrHandler:
    If Not Err Then
        AlterFieldType = True
    End If
End Function

I hope it helps.
Suat

 

[smozgur]

Actually easier method with Connection object:
(Change strTable and strField variables with actual names)

Sub ChangeDataType()
Dim cnn As Object
Dim strSQL As String
Dim strTable As String
Dim strField As String

    strTable = "tablename"
    strField = "fieldname"
    
    Set cnn = CurrentProject.Connection
    strSQL = "ALTER TABLE [" & strTable & "] ALTER COLUMN [" & strField & "] DATE"
    cnn.Execute strSQL
End Sub

Please use this method instead.

You can refer the following link for more information:
http://support.microsoft.com/?kbid=210304

Suat

 

[Fritz24]

I shall give that a try - thanks

 

[smozgur]

You're welcome!

Suat

 

[Fritz24]

I'm having trouble getting this to work. I keep getting the error message 'Variable Not Defined' and it highlights CurrentProject. I'm using Access 97.

 

[smozgur]

Then could you please try the first code that I pasted for Access 97 ?
(Access 97 doesn't support Connection object)
Suat