Read Access Database row by row from Excel VBA

[Squall09]

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.

 

[RoryA]

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

 

[Norie]

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?

 

[Squall09]

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.

 

[CharlesChuckieCharles]

Something Like this should be enough to get you mind working

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

 

[RoryA]

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.

 

[Norie]

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?

 

[Ruddles]

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.

 

[Squall09]

Thanks all, I will give it a try.

 

[Squall09]

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.