Results 1 to 7 of 7
  1. #1
    Tenmakk is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    35

    Is there a way to populate fields in access from fields in excel?

    Is there a way to populate fields in Access from fields in Excel in Office 2010? If so, what do I need to do? I'm not very good with Access programming. I've looked all over the net and couldn't find anything.

    Thanks,
    Brad

  2. #2
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    The easiest way is to link to the Excel spreadsheet, and then use queries to transfer the data. But, if the spreadsheet is fairly complex, with merged cells and the like, it can get tricky.

    Can you give more details about what you want to do?

    John

  3. #3
    Tenmakk is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    35
    Quote Originally Posted by John_G View Post
    The easiest way is to link to the Excel spreadsheet, and then use queries to transfer the data. But, if the spreadsheet is fairly complex, with merged cells and the like, it can get tricky.

    Can you give more details about what you want to do?

    John
    Well, I'm doing data entry. There are several basic fields in Access that I need to enter into from Excel. Things like "Address", "Phone Number", "Date Joined", "Money Donated", etc. I just thought it would be great if I could have Access take some of the information from Excel to populate some of the fields.

    Thanks

  4. #4
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    You might be able to do that with VBA, though I've never tried using a linked Excel spreadsheet as a recordset. I know DLookup works (just tried it), so if your spreadsheet contains a value that uniquely identifies each row, then a series of DLookup's to populate form fields might do it for you.

    HTH

    John

  5. #5
    Tenmakk is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    35
    OK. Thanks. I guess I was looking for a "magic button" that I could press to populate Access fields from each Excel row. I'll just do it the old fashioned way and will look at DLookup when I have time.

  6. #6
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Two ideas, then:

    If you just want to initialize an Access table with data from the Excel, create the blank table, then make an Append Query to transfer the data from Excel to Access
    or
    If you do have fields that are common to to each, and can uniquely identify the rows, and have some data in the Access table, try joining the two in an Update query, and update Access from the corresponding fields in excel.

    Just some ideas!

    John

  7. #7
    Tenmakk is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Posts
    35
    OK. Thanks. I'll work on this and reply with results.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using random fields in Excel to populate Access table
    By cryswater in forum Import/Export Data
    Replies: 3
    Last Post: 05-14-2013, 02:50 PM
  2. Replies: 1
    Last Post: 06-04-2012, 12:43 AM
  3. Replies: 12
    Last Post: 05-07-2012, 12:41 PM
  4. Connecting Fields in Access and Excel
    By bigmacholmes in forum Access
    Replies: 3
    Last Post: 11-04-2011, 12:28 PM
  5. Replies: 0
    Last Post: 06-05-2009, 10:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums