×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Copy imported data to access table

Copy imported data to access table

Copy imported data to access table

(OP)
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

RE: Copy imported data to access table

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.

RE: Copy imported data to access table

(OP)
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.

RE: Copy imported data to access table

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

rstEnable.Fields("tblFieldName") = 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

RE: Copy imported data to access table

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.

RE: Copy imported data to access table

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.

RE: Copy imported data to access table

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close