Excel Macro to retreive access data 1

[JohnAcc]

Hi There:

I get a run time error when i try and run the following code:

Sub GetTable()
Dim Db As Database
Dim Rs As Recordset
Dim Ws As Object
Dim i As Integer
Dim Path As String
Set Ws = Sheets("Sheet1&quot
Path = "c:\omnisxls\comp20031.mdb"
Ws.Activate
Range("A1&quot.Activate
Selection.CurrentRegion.Select
Selection.ClearContents
Range("A1&quot.Select
Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True)
Set Rs = Db.OpenRecordset("commissions&quot
For i = 0 To Rs.Fields.Count - 1
Ws.Cells(1, i + 1).Value = Rs.Fields(i).Name
Next i
Ws.Range(Ws.Cells(1, 1), Ws.Cells(1, Rs.Fields.Count)).Font.Bold = True
Ws.Range("A2&quot.CopyFromRecordset Rs ' I get the error here
Sheets("Sheet1&quot.Select
Range("A1&quot.Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1&quot.Select
Rs.Close
Db.Close
End Sub


The error msg says:
Method 'copy from recorset' of object 'range' failed

The data was imported into access from an excel file using the import method.

Any ideas ?

Rgds

John

 

[SkipVought]

Hi John,

I ran you code, substituting one of my databases, and it ran fine.

The only thing that I can think of is that you are exceeding the ROW LIMIT for Excel. I reformatted your code, without Activate/Select. CHeck out the CopyFromRecordset additional parameter...

Sub GetTable()
    Dim Db As Database
    Dim Rs As Recordset
    Dim i As Integer
    Dim Path As String
    
    Path = "C:\Documents and Settings\metzgerc\My Documents\ExcludeList.mdb"
    With Sheet1
        .Range("A1").CurrentRegion.ClearContents
        
        Set Db = Workspaces(0).OpenDatabase(Path, ReadOnly:=True)
        Set Rs = Db.OpenRecordset("Usage")
        For i = 0 To Rs.Fields.Count - 1
           .Cells(1, i + 1).Value = Rs.Fields(i).Name
        Next i
        Range(.Cells(1, 1), .Cells(1, Rs.Fields.Count)).Font.Bold = True
        With .Range("A2")
            .CopyFromRecordset Rs, 65535 ' I get the error here
            .CurrentRegion.Columns.AutoFit
        End With
    End With
    Rs.Close
    Db.Close
End Sub

Hope it works!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[JohnAcc]

Cheers Skip! There was 120,000 rows!

Rgds, John