Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2007
    Posts
    18

    Unanswered: Specifying & Accessing an array in MS Access

    Hi.

    I have an Excel spreadsheet with 11 columns. To do what I want to do, this file will be converted to a flatfile. I want to read each record in the file and lookup on the database, the name in the 11th column to see if the name is already loaded into the dtabase.

    Does anyone know how I can do this and, can you give me the code to define the array and loop through the array to reach column 11?

    Thanks.

    Access Newbie.

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Access does not use arrays to read separate columns in a table. You can use the name of the field that contains the data from the 11th column. I assume you want to process this flat file for any dups, then add the none dups to the master table in the database?

  3. #3
    Join Date
    Apr 2007
    Posts
    18
    Thanks... Like I said, I am a newbie. I used the column headings as fields separated by commas on the input statement.

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    ok What I would do is can you LINK the Text file to the Database

    if so know you have it in one systems

    Right know the Code

    open a new module.

    [CODE]
    Sub readit()

    Dim db As Database ' create a database object
    Dim RS As Recordset ' create a recordset object
    Dim rsnew As Recordset
    Set db = CurrentDb

    Set RS = db.OpenRecordset("the linktable name")
    Set rsnew = db.OpenRecordset("table name of new data going in to")
    Do Until RS.EOF
    checkfield = RS.Fields("the name of the 11th column") 'this will put the value in to checkfield

    countit = DCount("*", "tabletocheck", "fieldto check='" & checkfield & "'")

    If countit = 0 Then
    rsnew.AddNew
    rsnew.feild("feildname") = RS.Fields("feildname")
    '...
    '...
    '...
    rsnew.Update
    End If
    RS.MoveNext
    Loop
    RS.Close
    rsnew.Close
    Set RS = Nothing
    Set rsnew = Nothing

    End Sub

    [\CODE]
    code off top of head needs testing that should point you down the right track
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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