asked on

Retreiving data from an Access Database using Microsoft Excel and VB macros

I've recently been given a project to fix which pulls data from tables in an Access database and pastes them into an Excel worksheet.

The problem I'm having is that it only copies some of the data. There are 1700 records in the table of the databse but when pasted into Excel only pastes around 1500 rows. The following code is used to import the cases but there doesn't appear to be any set range that stops it importing all of the records.

'=================== Import Cases from Access ======================
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer

rst.Open Source:="Table_Cases", ActiveConnection:=cnn, CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdTable

Worksheets("Escalation Workspace").Select 'Activate

Range("A3").CopyFromRecordset rst

My main question would be is there any limit to the amount of data that can be pulled from Access?

Many thanks

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

leclairm

This is the CopyFromRecordset syntax.

returnValue = SomeRange.CopyFromRecordset(Recordset, MaxRows, MaxColumns)

CopyFromRecordset returns the number of rows copied. So you can assign it to a variable to compare against the number of records in your recordset.

Is this a new excel sheet or are you pasting it into an existing one??

leonstryker

See if the missing rows contain NULL values. Excel does not like NULLS.

Leon