Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text files data into Access tables

    I hope someone can help me with this:
    I've populated a table in the db1.mdb file attached starting from a text file(also attached)
    using the procedure outlined below (which I'm pretty sure is not the culprit, I've just included it for good measure).
    All the records from the text files are imported correctly but I don't understand why, when I open the table in
    datasheet view, the records appear out of chronological order unlike the text file.
    Could someone please shed light?
    <font face="BrushScript BT"><font color=ff6666>Ciao</font face=brush></font color=ff6666>

    Private Sub Command19_Click()
    On Error GoTo Err_Command19_Click
    Dim linea As Integer, mychar, gnum as string
    linea = 1
    Set cnn = CurrentProject.Connection
    rst.CursorLocation = adUseClient
    rst.Open "MEDIEGIO", cnn, adOpenKeyset, adLockOptimistic
    Open "c:vaxCASKWH9700.DAT" For Input As #1
    Do While Not EOF(1)
    Line Input #1, mychar
    If linea = 1 Then
    ElseIf linea = 2 Then
    Else
    If Mid(mychar, 7, 2) = 0 Then
    gnum = "20" & CStr(Mid(mychar, 7, 2))
    rst.AddNew
    rst!Anno = CVar(gnum)
    rst!Mese = Mid(mychar, 10, 3)
    rst!Giorno = Mid(mychar, 14, 2)
    If Mid(mychar, 17, 10) <> 0 Then rst!cASSIGLIO = Mid(mychar, 17, 10)
    rst.Update

    Else
    If Mid(mychar, 17, 10) = 0 Then
    rst.AddNew
    If Mid(mychar, 5, 1) = " " Then
    gnum = "19" & CStr(Mid(mychar, 7, 2))
    rst!Anno = CVar(gnum)
    Else
    rst!Anno = Mid(mychar, 5, 4)
    End If
    rst!Mese = Mid(mychar, 10, 3)
    rst!Giorno = Mid(mychar, 14, 2)
    rst.Update
    Else
    rst.AddNew
    If Mid(mychar, 5, 1) = " " Then
    gnum = "19" & CStr(Mid(mychar, 7, 2))
    rst!Anno = CVar(gnum)
    Else
    rst!Anno = Mid(mychar, 5, 4)
    End If
    rst!Mese = Mid(mychar, 10, 3)
    rst!Giorno = Mid(mychar, 14, 2)
    rst!CASSIGLIO = Mid(mychar, 17, 10)
    rst.Update
    End If
    End If
    End If
    linea = linea + 1
    Loop
    Close #1
    rst.Close
    Exit_Command19_Click:
    Exit Sub

    Err_Command19_Click:
    MsgBox Err.Description
    Resume Exit_Command19_Click

    End Sub
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Text files data into Access tables

    Did you put an index on the table? That would change the display order. You don't ordinarly rely on input order anyhow.
    Charlotte

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text files data into Access tables

    But isn't it better to have a table which, when in datasheet view, shows the data in neat chronological order so that, when I print it, I can quickly check whether a certain date is present?
    Anyway, as you can see from db1.mdb, I did gave the table an index because I was trying to achieve the purpose of my post but I still can't; you can see the records belonging to the year 1999 in the Mediegio table are particularly messed up.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text files data into Access tables

    Doesn't matter how they are sorted in the table, like Charlotte said. Recommend you use a query to view records - you can then adjust the sorting any way you'd like.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text files data into Access tables

    I don

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Text files data into Access tables

    Definitely. Datasheet views are for quick looks by developers and aren't intended to be printed out, because they should be largely unreadable without a crib sheet to tell you want the keys translate to. With a query, you can join the necessary lookups so that the result is readable and present the records in any order you please.

    Tables HAVE no natural order except that there is a beginning and end to the table. It doesn't matter what order the records are in because that order changes depending on the indexes you apply and invoke and the relationships you create among datasets. That is one of the fundamentals of relational design, and you'll get yourself in trouble if you ever try to rely on the "natural" order of the data.

    Even if everything actually is in chronological order, you'll still have indexes on it, so don't waste time worrying about getting the records entered in the correct order. Just make sure your indexes are properly applied.
    Charlotte

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text files data into Access tables

    Right, I've prepared a query (see Mediegio Query in the attached file) but still (check 1999) the records are not in chronological order. Note that if I sort the table by month, Ago is the first month displayed and that's not in chronological order.
    Attached Files Attached Files

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No beating around the bush

    You're 100% right, the only feeble excuse I can offer is that I was mostly concerned about the order in which the table records were in.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Text files data into Access tables

    Are the months stored as text or numbers? If you store them as text, the sort will put them in alphabetical order on the months.
    Charlotte

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text files data into Access tables

    Month is text.
    That's why I was trying to get the table in chronological order. Is there a way to get the table records in chronological order like in the text file?

  11. #11
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text files data into Access tables

    Check this file. I added a table with the name of the months and an order column. Then i combined the two tables in qselOrdered to get the order you want. Most of the objects are in English as my knowledge of the italian language is primitive.
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Text files data into Access tables

    I prefer not to store months as text for this very reason. Storing them as integers from 1 to 12 also gives you the flexibility to change the formatted appearance easily by linking a lookup table to your query or using a combo box on a form. Numeric months sort properly and generally make life easier for the harassed programmer. I usually have a tlkpMonth table that has a PK of 0 to 12 (I use 0 for "undetermined" or "unknown") and several fields that contain alternate text values to use in different places, like "Jan" and "January".
    Charlotte

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text files data into Access tables

    I've seen linking a lookup table to the Mediegio table in my query put in practice by PaulK in his reply(that's what you meant by "linking a lookup table to your query ", isn't it?) but what about the other piece of advice you give in your post,"using a combo box on a form"? How do I use a combo box to sort the records in Mediegio so that they display in chronological order?

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Bless ya!

    That's simply perfect, thanks Paul <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  15. #15
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text files data into Access tables

    Look at the SQl string in the SQL view in the query I created. A combobox's row source can be several things two of which are a query or an SQL string. You can use the same query for the row source of a combobox on a form or any part of it. If you don't want a particular column to display you can set it's column width to 0".

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •