Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changing Field Type with VBA (Access 2002)

    Can I easily change a field type in an imported table using vba - or is this a really complex procedure?

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Field Type with VBA (Access 2002)

    need more details. what file type are you trying to change, and what is the context? is the imported table this from an application? for example, are you trying to import a table to Access from, say, Excel? If you are trying to import data to an Access table from X application, the import wizard should handle the data transfer. If you can see an import the data, it's of little consequence what the originating file format was in general but may be very important in terms of preserving a column data structure in import (for example, leading zeroes can import badly if at all and some data sources have fixed width fields, which need to be dealt with on import).

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing Field Type with VBA (Access 2002)

    See <post#=227451>post 227451</post#> by <!profile=MarkD>MarkD<!/profile>, but take heed of Steve Skelton's remarks.

  4. #4
    New Lounger
    Join Date
    Oct 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Field Type with VBA (Access 2002)

    The nature of the problem is this: the data is downloaded as *.prn which I change to *.txt to be able to import into Access.
    When I view the *.txt file in excel two of the columns are definitely formatted as dates (I can change using DD-MM-YY etc) but on importing using the wizard these fields always come in as text.
    If I use an import specification which forces the fields to be imported as dates they get deleted as an import error.
    However, if I let the wizard import these fields as text and then manually change the field type in the table design they are converted to dates with no problems or deletions - which implies that they are dates all along and that the wizard imports them incorrectly. Is it possible that there is some option that I am missing which causes the import wizard to behave in this way?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing Field Type with VBA (Access 2002)

    The wizard is able to import dates from text files correctly (although you must set the Date/Time type interactively or through an import spec.) Is there something funny with the dates?
    If you like, you can post a small portion of the text file (replace sensitive data with dummy data) to let Loungers investigate the problem.

  6. #6
    New Lounger
    Join Date
    Oct 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changing Field Type with VBA (Access 2002)

    I've made a breakthrough in this - if I open the *.txt in excel and then save and close still as *.txt the fields are imported as Date correctly. If I don't do this procedure then the date fields are imported as text.

    The strange thing is that in another db which works on the same skeletal structure I don't have this problem at all and can import this data with no issues. I have tried copying all elements of the problematic db into a new container and seeing whether that will work but it doesn't.

    Now I guess I need to I open and save the text file using vb as part of my import macro - I presume that this is possible?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Changing Field Type with VBA (Access 2002)

    You can start Excel from Access in code; this is called Automation. I would start by recording a macro in Excel to open, save and close the text file; you can either call this macro from Access, or use the code in Access.

    Moderator <!profile=WendellB>WendellB<!/profile> has a tutorial on Automation on his website (see his profile), with useful links. If you do a search for Excel.Application in this forum, you will find examples of automating Excel from Access.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •