DoCmd.Transfer text not creating Primary key

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...

sudheer
sudheerknsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

niblickCommented:
Can you paste your vba tabel create code here, please?
0
sudheerknsAuthor Commented:
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. :((
0
niblickCommented:
According to Microsoft this is a problem:
http://support.microsoft.com/default.aspx?scid=kb;en-us;182002

You will need to import other than using TransferText if you need the primary key.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sudheerknsAuthor Commented:
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..

sudheer
0
niblickCommented:
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.
readAnother:
    Loop
   
    'DoCmd.SetWarnings 1
    'DeleteFile (strInputFileName)
    MsgBox ("Done")

Exit_ACV_Click:
    Close #1
    rst.Close
    Set dbs = Nothing
    Exit Sub

Err_ACV_Click:
    MsgBox Err.Description
    Resume Exit_ACV_Click
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sudheerknsAuthor Commented:
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.

sudheer
0
stevbeCommented:
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

Steve
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.