Anything other than recordset?

[Fherrera]

Hi, i'm making a script in vba to automate the data gathering of quite a bit of information and putting it into respective excel sheets. Vba in Excel, using Office 97. The script connects to Access 97 to run some SQL and then return values.

It seems (from what I've seen and other than the EXTERNAL DATA->Run SQL command) that the only way to get information from access is to do it recordset by recordset or row by row. Is it possible to just say, run SQL statement and place it startin on cell A2, BAM! get to work?

I hope so but I somehow doubt it. I think the constant filling up cells in excel is what's causing such a huge slow down in the script vs just doing it in access (which is itself pretty slow, but comparatively speaking)

Thanks

Frank

 

[SkipVought]

Frank,

I think that if it were done row by row, it would REALLY be sloooooooooo

http://wwww.

Post the SQL.

Could you select a sub-set of data and work with it?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Fherrera]

Hi Skip,

Sub testDAODatabaseFrank()

    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    
    Dim curCol As Integer, curRow As Integer
    curCol = 1
    curRow = 2
    
    'create workspace, name is anything, admin is to log in to db and password is ""
    Set ws = DBEngine.CreateWorkspace("FrankWS", "Admin", "")
    
    'open database
    Set db = ws.OpenDatabase("D:\test.mdb", dbUseJet)
    
    ' set Access query and define the paramater
    Set qdf = db.QueryDefs("Query4")
    qdf.Parameters("qTag") = "Frank"
    
    'run query into recordset RS
    Set rs = qdf.OpenRecordset(dbOpenForwardOnly) 'dbOpenDynaset)
    
    'parse through recordset
    ' cant use MoveLast and MoveFirst b/c dbopenforward only (for performance)
'    rs.MoveLast
'    If rs.RecordCount > 0 Then
'    rs.MoveFirst
        Do Until rs.EOF()
            
            Cells(curRow, curCol).Value = rs("TAG")
            Cells(curRow, curCol + 1).Value = rs!Date
            Cells(curRow, curCol + 2).Value = rs!Value
            
            'update counters
            curRow = curRow + 1
            rs.MoveNext
            
        Loop
 '   End If
    
    
    'close everything and clear memory
    rs.Close
    qdf.Close
    db.Close
    ws.Close
    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing
    Set ws = Nothing
    
End Sub

Here's not the code i'm using, but the same idea. I used this to start the testing. When I say row by row..i'm assuming the recordset, rs in this case gets values of one row returned from the query then I have to manually insert them into excel cells. Hence, processing row by row??

I could be mistaken...

Frank

 

[SkipVought]

Try CopyFormrecordset method

For iCols = 0 to rs.Fields.Count - 1
    ws.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
ws.Range(ws.Cells(1, 1),  _
    ws.Cells(1, rs.Fields.Count)).Font.Bold = True
ws.Range("A2").CopyFromRecordset rs

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884