Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Read Access Database row by row from Excel VBA

This is a discussion on Read Access Database row by row from Excel VBA within the Excel Questions forums, part of the Question Forums category; Hi all, First time post, bit nervous, but I struggle to find exactly what I need over the internet and ...

  1. #1
    New Member
    Join Date
    Jul 2011
    Posts
    8

    Default Read Access Database row by row from Excel VBA

    Hi all,

    First time post, bit nervous, but I struggle to find exactly what I need over the internet and hoping someone can help me out here.

    ** What I want to do **
    What I have is an Access database Table, and I wish to write a macro in Excel where I could read/copy one row of the Database at a time, do some Excel calculation, and append the calculated result back to the database, then loop through row by row of the database.

    Secondly, I also wish to access specific row based on some condition/filter and do the same calculation and append the result as above.

    ** What I manage to find **
    I manage to find various codes which enables me to copy the full database, or from particular row, but nothing that is row by row or by filter.

    The only filtering codes I manage to find is using SQL method via Excel's data->query tool, but I believe this will be very slow if I were to loop many rows of data.

    I was wondering is what I want possible without the use of SQL query? Or I have to resort with SQL query?

    Many thanks for your assistance.

  2. #2
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    23,126

    Default Re: Read Access Database row by row from Excel VBA

    Why do you want to avoid queries? They will almost certainly be a lot faster than any other method.

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,220

    Default Re: Read Access Database row by row from Excel VBA

    Why not just do the whole thing in Access?

    If you do need to use Excel why not import all the rows/records, do the calculations and then update the records in Access?
    If posting code please use code tags.

  4. #4
    New Member
    Join Date
    Jul 2011
    Posts
    8

    Default Re: Read Access Database row by row from Excel VBA

    Thanks for quick response

    1. Avoiding query since if I need to run a query for each record (which I have a lot, more than excel's number of rows), I think it would be slow.

    2. As per 1, I have too many rows of data, hence can't import all in one go. I understand I can group them (in which I will need query), which is my alternative backup for now, but I wish to know if there is anyway I can do row by row before I go to my alterternatives.

    I guess for now I am trying out what I can/can't do, and thus determine if I need resort other means. Thanks.

  5. #5
    Board Regular
    Join Date
    May 2011
    Location
    London
    Posts
    1,779

    Default Re: Read Access Database row by row from Excel VBA

    Something Like this should be enough to get you mind working

    Code:
     
    Dim objAcess as Object
     
    Set objAccess = CreateObject("Access.Application")
    objAccess.OpenCurrentDatabase "C:\yourpath\yourdb.mdb", False
     
     
     
    Dim RS As DAO.Recordset
     
     
     
        Set RS = objAccess.OpenRecordset("Select * from Yourtable")
        If Not RS.EOF Then
            RS.MoveFirst
     
            Do
             with RS
                .edit
    !field1=2*3
                .update
            RS.MoveNext
     
            Loop Until RS.EOF
            RS.Close

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    East Sussex
    Posts
    23,126

    Default Re: Read Access Database row by row from Excel VBA

    If you are doing the same calculation on each of them, the point of the query is to update them all in one go, rather than processing line by line, which will be the slow part.

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,220

    Default Re: Read Access Database row by row from Excel VBA

    If you can't import the data to Excel surely the best place to work with it is Access?

    What calculations are you doing?

    Can they only be done in Excel?
    If posting code please use code tags.

  8. #8
    Board Regular Ruddles's Avatar
    Join Date
    Aug 2010
    Location
    Glevum Castra, Britannia
    Posts
    5,473

    Default Re: Read Access Database row by row from Excel VBA

    I would echo the above remarks: instinct tells me that updating a table in Access with a single query should be much faster than writing some homebrew VBA to do it one line at a time.
    Last edited by Ruddles; Jul 13th, 2011 at 09:24 AM.
    R.
    ---
    On two occasions I have been asked, 'Pray, Mr Babbage, if you put into the machine wrong figures, will the right answers come out?'
    I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.

  9. #9
    New Member
    Join Date
    Jul 2011
    Posts
    8

    Default Re: Read Access Database row by row from Excel VBA

    Thanks all, I will give it a try.

  10. #10
    New Member
    Join Date
    Jul 2011
    Posts
    8

    Default Re: Read Access Database row by row from Excel VBA

    Hi all,

    I manage to get both row by row and via Query to work, so thanks for your help.

    However, I am encountering two issues I hope I can get some tips:

    1. Using the SQL query method, I used record macro method, which frankly the code is long and messy (any one have a cleaner set of codes would be greatly appreciated). The problem I am trying to figure out is I want data to be insert from Cell A6, but the macro keeps the old data, and insert columns, and add data to the left of previous extracted data. How can I fix this?

    2. I want the macro to pick up certain condition from an input in the worksheet, as well as no-condition at times. It's all fine if I put in the condition, but what if I want all the data without condition? I tried the wildcard symbol "*", but it doesn't seem to work. Any help would be great, thanks.

    To clarify, here's an example:

    Status = Single or Married or Any
    Cars = 1, 2, 3 or Any

    So I want the macro to pick up for example Single + 2 Cars is fine.

    But Single + Any Number of cars I can't get it to work with wildcard "*" in the VBA codes.

    I know this is fine if I do things manually (by set no condition on Cars), but I need this to work so the user can simply input information in the worksheet and extract necessary information. Further to that, we might think to loop through macro and say extract Single+1, then 2, then 3 then Any.

    Apologies if this is unclear... I can elaborate further if required. Thanks heaps for your assistance.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com