Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1313
  • Last Modified:

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

  • 3
  • 3
2 Solutions
Can you paste your vba tabel create code here, please?
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. :((
According to Microsoft this is a problem:

You will need to import other than using TransferText if you need the primary key.
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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

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

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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now