Import parent/child from txt file in MS Access - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Import parent/child from txt file in MS Access

SSgtGlassSSgtGlass Posts: 3Member
I am creating an inventory database where I need to import data from a text file created by a barcode scanner. The software associated with the scanner saves scanner data as a text file that looks something like this:

1234561235
12335
12365
45645
1234567895
12398
45659
4567890123
12397

The 10 digit codes are the barcodes for the location where the equipment is. The following 5 digit codes are the barcodes of the equipment in the location scanned above.
I would like to set up an import parent child relationship so that the equipment is associated with the location. I have limited access/coding experience but I pick things up quickly.
Let me know if you guys have any ideas. I realize this could get complicated.


Much thanks,
SSgt Jonathan Glass
USAF



Comments

  • dokken2dokken2 Posts: 532Member
    : I am creating an inventory database where I need to import data from
    : a text file created by a barcode scanner. The software associated
    : with the scanner saves scanner data as a text file that looks
    : something like this:
    :
    : 1234561235
    : 12335
    : 12365
    : 45645
    : 1234567895
    : 12398
    : 45659
    : 4567890123
    : 12397
    :
    : The 10 digit codes are the barcodes for the location where the
    : equipment is. The following 5 digit codes are the barcodes of the
    : equipment in the location scanned above.
    : I would like to set up an import parent child relationship so that
    : the equipment is associated with the location. I have limited
    : access/coding experience but I pick things up quickly.
    : Let me know if you guys have any ideas. I realize this could get
    : complicated.
    :
    :
    : Much thanks,
    : SSgt Jonathan Glass
    : USAF
    :
    :
    :
    :
    I think is fairly simple. one way would be to-
    import the entire text file into a table
    run a query to append for the 10-digit parent records
    run another append query for the 5-digit child records

    try this code, put in a module, then Run Sub/Userform in visual basic-

    Option Compare Database

    'requires microsoft dao 3.51/3.6 object library [under tools/references]
    '
    'required tables-
    't0_import - with field1 as text
    't1_parent - with field1 as text
    't2_child - with field1 as text, field2 as text
    '
    Sub import()
    Dim db As DAO.Database, rs As DAO.Recordset
    Dim tmp As String
    Dim myParent As String
    Dim myChild As String

    'error handler
    On Error GoTo erh

    'open table/recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("t0_import", dbOpenSnapshot)
    '1st record
    rs.MoveFirst
    'turn off access messages [append records]
    DoCmd.SetWarnings False

    'loop thru all imported records
    Do While Not rs.EOF
    tmp = "" & rs!field1

    If Len(tmp) = 10 Then
    'append parent
    myParent = tmp

    SQL = "INSERT INTO t1_parent ( field1 )" & _
    " SELECT " & myParent & " AS expr1;"
    DoCmd.RunSQL SQL
    Else
    'append child
    myChild = tmp
    SQL = "INSERT INTO t2_child ( field1, field2 )" & _
    " SELECT " & myParent & " AS expr1, " & myChild & " AS expr2;"
    DoCmd.RunSQL SQL
    End If

    rs.MoveNext
    Loop

    xit:
    'turn on access messages
    DoCmd.SetWarnings True
    'release resources
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

    erh:
    MsgBox Err.Description, vbCritical, Err.Number
    Resume xit
    End Sub



    note- this only works if your barcode data is numeric, if its a string then need to concatenate double quotes around data to insert-

    tmp = chr(34) & rs!field1 & chr(34)
  • SSgtGlassSSgtGlass Posts: 3Member
    This does work fantastic however my barcode data is alpha-numeric and I am having trouble with the using the code you included with the note.

    Thanks for all of your work on this.

    SSgt Glass
  • dokken2dokken2 Posts: 532Member
    : This does work fantastic however my barcode data is alpha-numeric
    : and I am having trouble with the using the code you included with
    : the note.
    :
    : Thanks for all of your work on this.
    :
    : SSgt Glass
    :

    the key is that alpha data needs to be in quotes [single or double] in order to perform the Sql INSERT

    therefore, look at the [tmp...] line and replace with the one at bottom, should solve the problem

    'loop thru all imported records
    Do While Not rs.EOF
    tmp = "" & rs!field1 'numeric-only

    tmp = chr(34) & rs!field1 & chr(34) 'alpha-characters
  • SSgtGlassSSgtGlass Posts: 3Member
  • etkietki Posts: 5Member
    Jonathon,

    You should really do something a bit more slick, and more convenient.

    Why don't you try writing a simple socket server in Python (should be very easy), and use the PyWin32 extension library to make a connection to your Access database using the Python ADO library provided.

    This way you could have your barcode reader possibly communicating wirelessly to a server receiving your barcode data, and automatically entering it into Access in real-time, i.e. read streaming data instead of file data.

    You could also connect your barcode reader to the serial port, and read the serial port in Python + do the ADO stuff I mentioned.

    If you want to talk to me about this approach feel free, send me an email through here first. I support your kind of work.

    Kind regards, David


    : I am creating an inventory database where I need to import data from
    : a text file created by a barcode scanner. The software associated
    : with the scanner saves scanner data as a text file that looks
    : something like this:
    :
    : 1234561235
    : 12335
    : 12365
    : 45645
    : 1234567895
    : 12398
    : 45659
    : 4567890123
    : 12397
    :
    : The 10 digit codes are the barcodes for the location where the
    : equipment is. The following 5 digit codes are the barcodes of the
    : equipment in the location scanned above.
    : I would like to set up an import parent child relationship so that
    : the equipment is associated with the location. I have limited
    : access/coding experience but I pick things up quickly.
    : Let me know if you guys have any ideas. I realize this could get
    : complicated.
    :
    :
    : Much thanks,
    : SSgt Jonathan Glass
    : USAF
    :
    :
    :
    :

Sign In or Register to comment.