+ Reply to Thread
Results 1 to 9 of 9

Search an Access database from Excel

  1. #1
    Registered User
    Join Date
    03-24-2017
    Location
    sds
    MS-Off Ver
    sd
    Posts
    9

    Search an Access database from Excel

    I have a very heavy MS Access database of 2 columns across 4m rows. Importing it to a workbook is not possible considering the number of rows.

    I am looking to search for values in column 1 from an xls spreadsheet, and retrieve the corresponding value in column 2. A sort of VLOOKUP from excel to access.

    Is this doable?

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,050

    Re: Search an Access database from Excel

    It would make more sense to put your values in a database table and outer join it to your existing table - this would be the quickest and easiest way (depending how many values ou have in Excel)

  3. #3
    Registered User
    Join Date
    03-24-2017
    Location
    sds
    MS-Off Ver
    sd
    Posts
    9

    Re: Search an Access database from Excel

    Absolutely - but as I said, the database contains 4 million rows.

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,050

    Re: Search an Access database from Excel

    And? Sorry, I don’t understand your point.

  5. #5
    Registered User
    Join Date
    03-24-2017
    Location
    sds
    MS-Off Ver
    sd
    Posts
    9

    Re: Search an Access database from Excel

    No problem - it is not an easy question. From what I understand, it can be done by linking an Excel file to an Access table.
    One can then use that table in a query to return what is needed.
    It is just beyond my capabilities really:

    stackoverflow.com/questions/5122582/run-access-query-from-excel-and-pass-paramerts-to-the-query

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,050

    Re: Search an Access database from Excel

    Actually, I understood the problem perfectly, it’s straightforward. I don’t understand the relevance of 4 million rows, the quantity of rows is an irrelevance.

    The link you’ve posted isn’t about linking an excel file to an access table, it’s about querying an access database from excel. That’s certainly possible, but it’s not really a good solution to your requirement - a better solution would be to just use access.

    Dining it as per the link would be very slow and inefficient, one could do it more efficiently, but the resulting VBA would be much more extensive.

    Databases are built for joining datasets together, it’s better to use the right tool for the job.

    A better approach than using VBA would be using the Get and Transform tools in Excel, but again it wouldn’t be as effieicnt as just using access as you’re building complexity and abstractions.
    Last edited by Kyle123; 02-12-2019 at 11:22 AM.

  7. #7
    Registered User
    Join Date
    03-24-2017
    Location
    sds
    MS-Off Ver
    sd
    Posts
    9

    Re: Search an Access database from Excel

    Fair enough.
    I am not very familiar with Access: can it ahandle formulas like in excel?

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,185

    Re: Search an Access database from Excel

    Hi Tony,

    4 million rows is no problem for Power Query. See:

    https://www.youtube.com/watch?v=vchsUEHXvZo
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  9. #9
    Registered User
    Join Date
    03-24-2017
    Location
    sds
    MS-Off Ver
    sd
    Posts
    9

    Re: Search an Access database from Excel

    Thanks Marvin but it does not work - not even with Excel 365.
    The day is found here in the 352mb ZIP file:
    download.companieshouse.gov.uk/en_output.html

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1