How do I change the data type in a table using a macro or VB code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to import an CSV file that contains a column of dates in the
format "dd mmm yy" into Access, but if I define the Access field data type as
Date/time the data is not imported for that column. If I define the Access
Data Type as text it is, but I need the Access table to be date/time. I
would like to automate this process & not have to remember to change it each
time.
 
It is always a good idea to import into its own table (so
everything comes in) and then use APPEND or UPDATE queries
to move that data to the tables where it really goes. What
I like to do is modify the import table structure to add ID
field(s), fill them out where records match and also add a
text field so when matching record are updated, the text
field in the import table specified what was changed.

For record where the ID is not filled, the records are appended.

this enables you to use data conversion functions so that
nothing (like dates) are lost

'~~~~~~~~~~~
Sub AddField_ID( _
pTablename As String, _
pFieldname As String)

'Crystal
'strive4peace2007 at yahoo dot com
' 5-22-06

'NEEDS REFERENCE
'Microsoft DAO Library

On Error GoTo Proc_Err

Dim Db As DAO.database
Dim Tdf As DAO.TableDef

Set Db = CurrentDb


Set Tdf = Db.TableDefs([pTablename])

With Tdf
.Fields.Append .CreateField([pFieldname], dbLong)
.Fields.Append .CreateField("ImportNotes", dbText, 255)

End With

Db.TableDefs.Refresh
DoEvents

'you may wish tom comment this out
MsgBox pFieldname & " and ImportNotes " _
& " have been added to " & pTablename _
, , "Done"

Proc_Exit:
On Error Resume Next
Set Tdf = Nothing
Set Db = Nothing
Exit Sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " AddIDField"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit

End Sub
'~~~~~~~~~~~~~


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
 
Back
Top