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.

 

[Norie]

Are 1 and 2 both for the data you want to get?

Or is 2 for something you need after you have the data?

What method are you actually using to get the data?

Is it the built in Data Query?

 

[Squall09]

Hi Norie,

Yes, both #1 and #2 are for data I want to get. #1 is when query is run, it adds more columns in excel rather than replace the existing one.

#2 is something during extraction process, I want to be able to condition/filter the data I extract from the database. I know I can do this manually thru the query, but I want to be able to pick up information (filter condition) from excel worksheet, feed into macro which runs the query.

Method wise, I am testing out both row by row and built in data query thru recorded macro. However, the problem I encounter is for either, I can't pick up data where for example, I want Single+Any number of cars.

 

[Norie]

What I meant was what method did you use to get the data when you recorded the query?

Did you just goto Data>Get external data... or use the Data tab on later versions of Excel?

 

[Squall09]

yes, that's the method I used, and recorded macro from that (very messy code I must say, typical of recording macro).

Excel version is 2003. thanks.

 

[Norie]

There are other methods you can use, eg ADO, to get the data.

It would take a little more to setup but once you have that all you need would be
the correct SQL statement.

If you need to use criteria from the worksheet to only pull specific data you can do that by incorporating the values for the criteria in the SQL.

Can you give more details on what data you are pulling and from where?

eg the name of the database, the table with the data etc

If you post the recorded macro then we should be able to see some of that.

 

[Squall09]

I have used the ADO method, but stuck at my problem #2, I can't extract if one of the criteria is empty.

I can't really post too much information/data as the information is sensitive. So I will provide simplified example:

Data Source = Access Database; Table Name = TestTable1
Data Table:
ID Country Status
1 USA Single
2 USA Single
3 USA Married
4 UK Single
5 UK Married

Irrespective whether I use the ADO or query method, I am able to extract for example USA + Single giving ID 1+2. Using the ADO method, my codes are:

If RS.Fields(1).Value = "USA" Then
If RS.Fields(2).Value = "Single" Then
For i = 0 To Num_Fields - 1
Range("A6").Offset(Start_Row + 1, i) = RS.Fields(i).Value
Next i
Start_Row = Start_Row + 1
End If
End If

However, the problem I have with both methods is that I can't extract ALL Singles (records 1+2+4). I tried by using

If RS.Fields(2).Value = "*" Then

it picks up no records.

-----

The seperate problem of #1, when I use query method, is only in output phase, where somehow the query/macro inserts column "left" of column A, and pastes the data into the newly inserted columns.

Many thanks once again.

 

[Norie]

Where's the SQL for the recordset?

Are you just returning all records from the table?

If you want to return the records that satisfy particular criteria you need to use something like this for the SQL.

strSQL=  "SELECT * FROM TestTable "

' add criteria If required 
strSQL=strSQL &  "WHERE Country ='USA' AND Status ='Single'"

' code to open recordset RS

For i = 0 To Num_Fields - 1

   Range("A6").Offset(Start_Row + 1, i) =   RS.Fields(i).Name 

Next i

Range("B6").CopyFromRecordset RS

That's sort of pseudo-code, and the criteria values are hard coded but that can be easily fixed.

 

[Squall09]

Hi Norie, thanks for your help, but I used the following code instead of strSQL and it seem to work.

RS.Open "Select * from " & TableName

I manage to solve the problem of wildcard issue, so most of my problem is solved - so now resorting to ADO method as oppose to query method since I can't solve why the excel data query won't replace existing data when it paste data into the worksheet. Thanks for your help anyway