MS Access Linked Table Bloat (1 Viewer)

[WayneRyan]

I have a coworker with a Local Access 2007 table and an identical table linked to SQL Server 2000.

The SQL Server table is empty and the Access table has 1,500,000 rows.

If he opens Windows Task Manager and watches while this executes:

CurrentDb.Execute "Insert Into SqlServerTable Select * From AccessTable"

The Task Manager will show the process growing rapidly until it reaches 2GB and then it crashes.

If the Append operation is broken down into multiple steps, the memory is not reclaimed after
each step and the process still crashes.

The short-term workaround is to iterate through a DAO recordset, appending to an ADO recordset
column-by-column. This doesn't incur any "memory bloat", but works about as SLOWLY as expected ...
About 1 million rows per hour.

We did get the data over, but this effort will need to be done periodically.

I'm quite sure that DTS would offer better performance.

Any other thoughts or ideas?

Thanks,
Wayne

 

[mdlueck]

If he opens Windows Task Manager and watches while this executes:

CurrentDb.Execute "Insert Into SqlServerTable Select * From AccessTable"

The Task Manager will show the process growing rapidly until it reaches 2GB and then it crashes.

Well that scenario you paint, Access must front-end the query to SQL Server as that can not possibly execute as a Pass-Through query. I would guess, then, that Access is caching the data headed to SQL Server, never releasing it as it is busy with your one command.

I have an application which downloads data from an IBM iSeries AS/400 running DB2/400 and publishes the data to SQL Server 2008 R2. The process transfers about 130 MB of records. The download runs in 35 seconds, publishing the same data to SQL Server via Stored Procedures wrapping with ADO.Command / ADO.Parameters objects tasks about 2.5 Hr. The syntax in VBA is as follows:

Using VBA ADO objects to execute a Stored Procedure (INSERT)
http://www.access-programmers.co.uk/forums/showthread.php?t=216860#post1104120

 

[WayneRyan]

Michael,

Thanks for responding.

I think you're correct that Access keeps requesting more buffer space, but
never gives any of it back. Even appending several tables doesn't change
a thing. It never returns the memory and eventually dies.

This process might take place once-a-month. So, the work-around of an hour
and a half isn't too bad. At least I don't have to do it.

For a long-term solution, I think that something like DTS has to be used.

Other large datasets (BCP data) work okay when moved to the server first,
but this is Access data and the Server doesn't run Access.

I know the Server "manually" imports Access data quite rapidly which
brings me back to DTS ...

Thanks,
Wayne

 

[SQL_Hell]

Agreed, I would go the DTS route.

I would also put the dts on a job and execute the job from Access, that way it will be asynchronous

 

[WayneRyan]

SQL_Hell,

Thanks, DTS was definitely the answer.

Sorry that it took 1 MONTH to respond.

Thanks,
Wayne