DoCmd.Transfer text not creating Primary key

Posted on 2004-11-10
Last Modified: 2008-02-01
I am trying to import a text file into a database table using a specifictaion i created. I was initially using the wizard to do this task. When i have written code to this using VBA, the resultant table created in the accees does not contain the primary key. whereas i specified 'let access decide the primary key when i defined the specification.

There is no problem in generating the primary key when i create the table using the wizrd but when i do it using VBA Acccess simply does not create the primary key

I am using Access 97...

Question by:sudheerkns
    LVL 5

    Expert Comment

    Can you paste your vba tabel create code here, please?

    Author Comment

    Call DoCmd.TransferText( _
                acImportDelim, _
                "Import_Leavers", _
                GetFileName(txtLeaversFilePath), _
                txtLeaversFilePath _

    Import_Leaver is a import specification i created
    It works fine with wizard but not with code. primary key is not created. :((
    LVL 5

    Expert Comment

    According to Microsoft this is a problem:;en-us;182002

    You will need to import other than using TransferText if you need the primary key.

    Author Comment

    Hi niblick,

    thanks for the posting. but then this is a serious problem wiht access. any suggestion on how to overcome this. Microsoft themselves say to avoid using the TransferText in code..

    LVL 5

    Accepted Solution

    Call up Bill and complain!

    This is how I handle the situation:

    Create a table in ACCESS and define all of your fields.

    Use the following code to import new data into the table:

    Private Sub ACV_Click()
    On Error GoTo Err_ACV_Click

        Dim dbs As Database, rst As Recordset

        Dim strInputFileName, InputString As String
        Dim strSSN, strFMP, strACV As String
        Dim i As Integer
        strInputFileName = "c:\folder\inputfile.txt"
        ' Return reference to current database.
        Set dbs = CurrentDb
        'use if you want to start with an empty table
        DoCmd.RunSQL ("Delete * from ACV")

        Open strInputFileName For Input As #1
        Set rst = dbs.OpenRecordset("ACV")
        'DoCmd.SetWarnings 0
        i = 0
        Do While Not EOF(1) ' Loop until end of file.
            strSSN = Left(InputString, 3)
            strSSN = strSSN & Mid(InputString, 5, 2)
            strSSN = strSSN & Mid(InputString, 8, 4)
            strFMP = Mid(InputString, 33, 2)
            strACV = Mid(InputString, 37, 1)
            rst.AddNew                   ' Add new record.
                rst(1) = strFMP & "/" & strSSN
                rst(2) = strACV
            rst.Update                   ' Save changes.
        'DoCmd.SetWarnings 1
        'DeleteFile (strInputFileName)
        MsgBox ("Done")

        Close #1
        Set dbs = Nothing
        Exit Sub

        MsgBox Err.Description
        Resume Exit_ACV_Click
    End Sub

    Author Comment

    Thanks niblick

    I was initially doing the same but it was taking too much of time for say 10K records even. It took 10 minutes.

    so i explore using the same wizard in code.

    intially it was working and now it stopped.

    LVL 39

    Assisted Solution

    you can create the key after the transfer text ...

    Dim dbs As DAO.Database
    Dim idx As DAO.Index
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field

    Set dbs = CurrentDb
    'or to do this to a different database ...
    ' Set dbs = DAO.DBEngine.OpenDatabase(CurrentProject.Path & "\CMO_BE.mdb")
    Set tdf = dbs.TableDefs("Import_Leavers")
    Set idx = tdf.CreateIndex("PrimaryKey")
    Set fld = idx.CreateField("MyFieldName")
    idx.Fields.Append fld
    idx.Primary = True
    tdf.Indexes.Append idx


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now