Copy imported data to access table 1

[Oliversmo]

I have a script written to import a fixed text file into an access table, but while the script will acknowledge the row count and add those rows to the database table, it does not post the fields to the table. (All rows/fields are blank.)
What am I missing in this script to copy the information that it reads to the table fields?

Private Sub Enabler_Import()
'This will be called from the Main form

Dim db As Database
Dim rstEnabler As Recordset

'Declare strings for extracting from text file
Dim sLine As String
Dim sCompany As String
Dim sCycle_date As String
Dim sPlan As String
Dim sPolicy As String
Dim sTax_qualifier As String
Dim sTrx As String
Dim sTrx_desc As String
Dim sAcct As String
Dim sMisc As String
Dim sDR As String
Dim sCR As String
Dim sState As String
Dim sMemo As String
Dim sFund As String
Dim sRev As String
Dim sCat As String

Set db = CurrentDb()
Set rstEnabler = CurrentDb.OpenRecordset("Enabler", dbOpenTable)

Open "c:\Enabler\enabler.seq" For Input As 1

Do While Not EOF(1)
Line Input #1, sLine

' Declare field variables/widths

If Len(sLine) > 200 Then

sCompany = Mid(sLine, 1, 3)
sCycle_date = Mid(sLine, 4, 8)
sPlan = Mid(sLine, 12, 10)
sPolicy = Mid(sLine, 22, 15)
sTax_qualifier = Mid(sLine, 50, 4)
sTrx = Mid(sLine, 54, 4)
sTrx_desc = Mid(sLine, 58, 30)
sAcct = Mid(sLine, 88, 25)
sMisc = Mid(sLine, 113, 40)
sDR = Mid(sLine, 153, 16)
sCR = Mid(sLine, 169, 16)
sState = Mid(sLine, 185, 2)
sMemo = Mid(sLine, 187, 2)
sFund = Mid(sLine, 240, 4)
sRev = Mid(sLine, 244, 1)
sCat = Mid(sLine, 245, 2)

rstEnabler.AddNew

End If

rstEnabler.Update

Loop

Close 1

rstEnabler.Close

MsgBox " Records have been imported", , "Enabler.seq Record Importer"

End Sub

 

[THoey]

You are placing the data into strings, but never moving the strings into the table fields. I would say you could probably skip the string loading and just do your addnew, do your loading of your table fields, ie, fieldname = MID(sLine, 245, 2), and then do your update.

Hope that helps...

Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[Oliversmo]

I attempted to move the rst###.Update statement directly below the loading of the fields but I receive an "Update or cancel Update without AddNew" error.

 

[Mongooses]

Oliver - after you state ***.AddNew() you must specify what to add and to which field to add it. Something like

rstEnable.Fields("tblFieldName&quot = sCompany
rstEnable.Update

However I would suggest that you use an array to store your data in the SAME order they are in the table. This will allow you to use a loop to add the data to the table and will save you some typing. For example
DataElement(0) = Mid(sLine, 1, 3)
DataElement(1) = Mid(sLine, 4, 8)
DataElement(2) = Mid(sLine, 12, 10) etc...

then you can save the data as follows

rstEnable.AddNew
For iNdx = 0 To rstEnable.Fields.Count - 1
rstEnable.Fields(iNdx) = DataElement(iNdx)
Next
rstEnable.Update

HTH,
JC

 

[THoey]

I would suggest:

Private Sub Enabler_Import()
'This will be called from the Main form

Dim db As Database
Dim rstEnabler As Recordset
Dim sLine As String

Set db = CurrentDb()
Set rstEnabler = CurrentDb.OpenRecordset("Enabler", dbOpenTable)

Open "c:\Enabler\enabler.seq" For Input As 1

Do While Not EOF(1)
   Line Input #1, sLine

   ' Declare field variables/widths
   If Len(sLine) > 200 Then

      rstEnabler.AddNew

      table.fieldname1 = Mid(sLine, 1, 3)
      table.fieldname2 = Mid(sLine, 4, 8)
      table.fieldname3 = Mid(sLine, 12, 10)
      table.fieldname4 = Mid(sLine, 22, 15)
      table.fieldname5 = Mid(sLine, 50, 4)
      table.fieldname6 = Mid(sLine, 54, 4)
      table.fieldname7 = Mid(sLine, 58, 30)
      table.fieldname8 = Mid(sLine, 88, 25)
      table.fieldname9 = Mid(sLine, 113, 40)
      table.fieldname10 = Mid(sLine, 153, 16)
      table.fieldname11 = Mid(sLine, 169, 16)
      table.fieldname12 = Mid(sLine, 185, 2)
      table.fieldname13 = Mid(sLine, 187, 2)
      table.fieldname14 = Mid(sLine, 240, 4)
      table.fieldname15 = Mid(sLine, 244, 1)
      table.fieldname16 = Mid(sLine, 245, 2)

      rstEnabler.Update

   End If

Loop

Close 1

rstEnabler.Close

MsgBox " Records have been imported", , "Enabler.seq Record Importer"

End Sub

That should do it... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[THoey]

woops, forgot to mention that you need to replace the table.fieldname# with whatever your fields are called. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[Oliversmo]

That has resolved all problems and the table information is importing properly
Thanks...