About Geoff Expertise I specialise in database analysis and design, SQL and database queries using QBE and VBA. In my work, I use MS Access together with MS SQL Server as ETL (Extraction - Transformation - Loading) tools for migrating data between business ERP systems and data stores. My forte is building bespoke functions and applications.
See my
website for example apps and downloads
Experience I am a chartered engineer with 30 years of engineering and business experience, member of the BCS and have been working specifically in database applications, including SQL Server (v7/8/2000) for the last 9 or so years. I previously taught a course in Database Analysis and Design, but am now a freelance consultant and systems analyst.
Commercial database design and development work undertaken.
Question How do I create a MS Access macro that will change a field's data type from text to a date/time value without going through the "design view" of the table.
Answer Hi Michael
I have written a small function which will do this: to run this from a macro, simply paste this into a new module and then use the RunCode command referencing this function.
Caveate: There is no error handling in the code, and no input data validation.....
I hope this helps
Kind regards
Geoff
Function ChangeFieldDataType()
Dim db As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
'Ensure there is a reference to MS DAO v xx
'Data types
'Type = 8 = "Date/Time"
'Type = 10 = "Text"
'Edit the table name and field name required.
Set db = CurrentDb
For Each tdf In db.TableDefs
If tdf.Name = "tblYourTableName" Then
For Each fld In tdf.Fields
If fld.Name = "YourFieldName" Then
fld.type = 10
Exit Sub
End If
Next
End If
Next
