Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2014
    Posts
    89

    Unanswered: Merging Excel Sheet into Access

    To start off I have an online survey that produces an exported excel sheet. All the data ever collected is in this excel sheet you can not cut it off at a point and start over.

    I had a access database that was started in order to have all the data in one place so we can send emails, mark people as admins, send out mail with mailing addressees and so on.

    Problem:
    I need to import that excel sheet into the main table PCOMAIN so that the access program has all the relevant data. Because the excel sheet also has older data already put in how can I merge them together? I tried adding the sheet in then running a duplicate query but the issue there was you have no choice on which duplicate it removes.

    For Instance I might have David Lafko and my email in the database with no other info. But I then completed the survey and put in a ton of info. Obviously when I put the excel data in access I want the David Lafko that has all the info not the one with some of the info.
    I also simply tried to copy and past from excel to access and that would not work at all. Would only load one block at a time.


    My goal then:
    TO be able to upload the excel sheet into access and merge the data together so there are no duplicates but the name with the most info stays in access. I do this fairly regularly so I did think to just link the excel spreadsheet to access but then prob 2 came into play and you can not update any of the data in there so again I can not mark someone as an admin or not. If there are any suggestions I really need some help here.

    I am still learning access so please be gentle with your answers and provide me a step by step if possible
    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    what I would do is link the excel file as a table

    then create a program in input each record but first check for dups before added Current record

    Sub Add_Excel_To_Table
    Dim exrst as records
    Dim tbrst as records
    Dim DB as database

    set db = currentdb
    set exrst = db.openrecordset("execltablename")
    set tbrst = db.openrecordset("tablewithdatainit")

    do until exrst.eof
    isitdup = Dcount("*","tablewithdatainit","what make it dup record")

    if isitdup =0 then
    tbrst.addnew
    tbrst("feildname") = exrst("feildname")
    ....
    ....
    ....
    tbrst.update
    end if

    exrst.movenext
    loop
    set db = nothing
    set exrst = nothing
    set tbrst = nothing

    End Sub
    Last edited by myle; 02-03-15 at 19:43. Reason: spelling
    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.

  3. #3
    Join Date
    Apr 2014
    Posts
    89
    Deff Clear as mud but I like how you said what I need to put in each thing. My question is this, If this sees it in the database already it is throwing out the data from the excel sheet or the database?

    I will try this in the am and come back to you with the problems I have LOL>..

  4. #4
    Join Date
    Apr 2014
    Posts
    89
    So this is what I did The survey has 20 Fields and the basic duplicate would be the last name. So here is the code but I am not sure where to put it and I get a compellation error when I tired to put it in the VBA Code under the rest of it.

    Sub Duplicate()
    Sub Add_Excel_To_Table()
    Dim exrst As records
    Dim tbrst As records
    Dim DB As Database

    Set DB = CurrentDb
    Set exrst = DB.OpenRecordset("Survey Responses")
    Set tbrst = DB.OpenRecordset("PCOMAIN")

    Do Until exrst.EOF
    isitdup = DCount("*", "PCOMAIN", "LastName")

    If isitdup = 0 Then
    tbrst.AddNew
    tbrst("LastName") = exrst("LastName")
    ....
    ....
    ....
    tbrst.Update
    End If

    exrst.MoveNext
    Loop
    Set DB = Nothing
    Set exrst = Nothing
    Set tbrst = Nothing

    End Sub

    End Sub

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    That Dcount is wrong

    HE have fix it


    Code:
    Sub Duplicate()
        Dim exrst As records
        Dim tbrst As records
        Dim DB As Database
        
        Set DB = CurrentDb
        Set exrst = DB.OpenRecordset("Survey Responses")
        Set tbrst = DB.OpenRecordset("PCOMAIN")
        
        Do Until exrst.EOF
           isitdup = DCount("*", "PCOMAIN", "LastName='" & exrst("LastName") & "'")
            If isitdup = 0 Then
                tbrst.AddNew
                tbrst("LastName") = exrst("LastName")
                '20 lines one for each feild 
                tbrst.Update
            End If
            
            exrst.MoveNext
        Loop
        Set DB = Nothing
        Set exrst = Nothing
        Set tbrst = Nothing
        
    End Sub
    You need to copy the code into a module.
    Last edited by myle; 02-04-15 at 19:50. Reason: spelling
    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.

  6. #6
    Join Date
    Apr 2014
    Posts
    89
    So I made a module how do you activate it to run? When I push play I get an error saying Compile Error User-Defined Type not defined. Highlights the sub duplicate.

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Quote Originally Posted by dlafko View Post
    So I made a module how do you activate it to run? When I push play I get an error saying Compile Error User-Defined Type not defined. Highlights the sub duplicate.
    in a form add a botton and in the Event ON click bit

    just
    call Duplicate

    Code:
    Private Sub Command10_Click()
    Call Duplicate
    End Sub
    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
  •