Read Access Database into Excel one record at a time

[Robert2100]

I'd like to read an Access Database file and be able to read the records one at a time (into an array if certain criteria are met). I can currently get the whole file or part of the file (using SQL) and drop this into an Excel worksheet, where I can then look for the data I'm after, but I'd like to be able to run through the records without having to place them in a worksheet (some of the files are 100K+ records, even after SQL filtering, and my method is slow).

I'm doing this because I want to be able look for key combinations of values in records. I've not been able to do this using SQL, because of the complexity and variability of the criteria.

My knowldege of using objects is poor! I tend to copy stuff. There is an example in the Excel VB HelpFile under the "OpenTextFile" Method which does what I want for text file, but I cannot get it to work with database files.

 

[bat17]

The following code will open an existing query (qryMark) and dump the data one row at a time in to a new Xl Spreadsheet. A bit primitive but it should give you the basics of what you need. The code assumes that there is an instance of XL running and will bomb out if there is not!
You will also need to set a reference to the "Microsoft Excel Object Libary" under Tools>References... in any Module.

Sub funOutPutToXL()
Dim xlApp As Excel.Application
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim strSql As String
Dim dbs As Database
Dim rstForm As Recordset
Dim qdf As QueryDef
Dim prm As Parameter
Dim lngRowCount As Long
Dim lngStartCol As Long

lngRowCount = 0
lngStartCol = 3

Set xlApp = GetObject(, "Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets("sheet1")

      ' Open dynaset-type Recordset object.
      Set qdf = dbs.QueryDefs("qryMark")
      For Each prm In qdf.Parameters
         prm.Value = Eval(prm.Name)
      Next prm
      
With xlSheet
      Set rstForm = qdf.OpenRecordset()
      If Not rstForm.EOF = True And Not rstForm.BOF = True Then
         rstForm.MoveFirst
         While Not rstForm.EOF
            .Cells(lngRowCount, lngStartCol) = rstForm![Mix]
            .Cells(lngRowCount, lngStartCol + 1) = rstForm![RMWT]
            .Cells(lngRowCount, lngStartCol + 2) = rstForm![Name]
            .Cells(lngRowCount, lngStartCol + 3) = rstForm![Qty]
            .Cells(lngRowCount, lngStartCol + 4) = rstForm![ALT_1]
            .Cells(lngRowCount, lngStartCol + 5) = rstForm![Name1]
            .Cells(lngRowCount, lngStartCol + 6) = rstForm![ALT_2]
            .Cells(lngRowCount, lngStartCol + 7) = rstForm![Name2]
            .Cells(lngRowCount, lngStartCol + 8) = rstForm![ALT_3]
            .Cells(lngRowCount, lngStartCol + 9) = rstForm![Name3]
            .Cells(lngRowCount, lngStartCol + 10) = rstForm![ALT_4]
            .Cells(lngRowCount, lngStartCol + 11) = rstForm![Name4]
            rstForm.MoveNext
            lngRowCount = lngRowCount + 1
         Wend
      End If
End With ' xlSheet
xlApp.Visible = True
Set rstForm = Nothing
End Sub

Shout if you need help with the details!

Peter

 

[mdmilner]

You should look at this:
http://www.mvps.org/access/modules/mdl0035.htm

Reading your post, you said:

I'm doing this because I want to be able look for key combinations of values in records. I've not been able to do this using SQL, because of the complexity and variability of the criteria.

I think this is where you really need help.
There are two approaches to working with data. Most like a mix of the two is what you need.

SQL only

SELECT * FROM tblName WHERE fieldname1 LIKE '*values*'

The Like Operator within the Parameter allows you to look for specific strings within a longer one. The Asterisks works as wildcards. You can throw in complex strings and I also specify to only use single character wildcards instead of an Asterisk.

The other, far slower method is to walk the recordset and look for matches within a given field using the InStr(fieldname,string_to_match_in_quotes) which returns the position of the match. Not matching returns a 0 allowing a simple If...Then...End If to determien if you have a match.

There are many 'walking the recordset' examples around but one I just posted was here: http://www.mrexcel.com/board2/viewtopic.php?t=81790

But, you said you have variable criteria.

The method to handle this is a mix. You write code allowing you to pass variables directly into the SQL query thus allowing it to change dynamically to meet you needs. You setup the SQL command once.

For example, a simple InputBox example

Dim dbs As DAO.Database
Dim qdf As QueryDef
Set dbs = CurrentDB()

returnValue = InputBox("Enter the String to Search For")

strSQL = "SELECT * FROM tblName "
strSQL = strSQL & "WHERE fldName Like '*" & returnValue & "*'"
Set qdf = dbs.CreateQueryDef(name_to_use, strSQL)
DoCmd.OpenQuery name_to_use, , acEdit

To manipulate the data instead, use:

Dim dbs As DAO.Database
Set dbs = CurrentDB()

returnValue = InputBox("Enter the String to Search For")

strSQL = "UPDATE tblName Set fieldName2 = fieldValue"
strSQL = strSQL & "WHERE fldName Like '*" & returnValue & "*'"
DoCmd.RunSQL strSQL

Hopefully, somewhere within these examples is the method you need to make your process easier. As a note only, you should use Option Explicit and declare variables like 'returnValue' explicitly. I took a shortcut and didn't post everything. Also, I didn't test run any of this, it's pseudocode and I may have typo'd something.

Mike

 

[Robert2100]

Thank you Peter and Mike for the comprehensive responses. I'll work through these and shout if I have any difficulty. Much appreciated.

 

[NateO]

Mike's on target here, SQL can probably get you what you want into a recordset (versus an array, but you can efficiently pass this object to Excel!).

Variable criteria is a minor issue, simply concatenate, e.g.,

http://www.mrexcel.com/board2/viewtopic.php?t=53716&start=4

If you want a more precise response, please, provide some specifics, what are looking at? Which field? Where in Excel does this end up in?