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

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

sudheer
0
sudheerkns
Asked:
sudheerkns
  • 3
  • 3
2 Solutions
 
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
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..

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

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