Error Message: "system resource exceeded" in Access when trying to append data

sql server express 2005
access 2003

I have an access database that I'm using as  place to append data into my sql server express tables(via linked).

I have a connection to a network sql server 2003 =   Table1
connection to sqlserver express 2005 database =   Table2

Through access I'm trying to append from table1 to table2...
both tables are sql server tables.

908,000 Table1

I have succeeded in appending data with less records...with no problem.

Question :  Is there something in sql server express2005 that I can tweak to succeed in appending large data.

I know I can use the dts wizard...
But doing it through access I can automate the process...

Any suggestions ?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

2 ideas:
1. try appending first to an access "Temporary Table" (empty access table) & from there append to the second table

2. Try appending in blocks- id up to 300,000 etc

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
IF your using an append query to do this, set the queries UseTransaction property to no.
FordraidersAuthor Commented:
Thanks but tried both..

jdettman, Ok...what does setting this proerty do...Never done that before ?
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<jdettman, Ok...what does setting this proerty do...Never done that before ?>>
  When you run a action query (Insert, Update, Delete), queries are set by default to use a transaction; do the whole query or rollback.  To do that, it takes locks out on everything it touches and run unit jornals all the changes (writes the records to a temp file).  
  Wehn you set use transaction to no, all that is bypassed.  Access/JET does things in pieces and doesn't consume anywhere near the same amount of resources.  But if you query fails for any reason, the changes are not rolled back.
  Setting that to no usually solves resource type problems with large queries.
FordraidersAuthor Commented:
jdettman, Still got the "system resource exceeded" error.

appending 980,000 records..
I think your problem is that, by using the Access database as your tool, you are reading the entire 980,000 rows of data into your local box before it tries to append them to the target table.
One option that you might want to explore is linking to the network SQL Server instance from your local instance or vice versa.  However, I am a bit confused by your reference to "SQL Server 2003" because SQL Server doesn't have a 2003 version.  Could it be that you are referencing a SQL Server 2000 instance?
You may want to consider upgrading your SQL Server 2005 to SQL Server 2008 because it has the more cvapabilities in this area.
You could check the following link and maybe get a better ability to do the transfer:
Also, is this a 1-time thing or do you need to do it periodically or every X time units (days, hours, weeks, whatever)?
Is this for a personal or a job situation?  Either way, the "Developer Edition" of SQL Server 2008 can be acquired for about $45 (from Amazon) and it has SSIS (among other cool things ;-) and if you are careful to only use the datatypes that SQL Server 2005 hase, you can transfer data between the two easily.
If you can get it, I would recommend the SS2008 Developer and using SSIS.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<jdettman, Still got the "system resource exceeded" error.>>
Bummer.  Only other thing I can think of is to make sure that the environemtn variables TMP and TEMP point to valid drives/directories and that the drive they point to has plenty of free disk space (3-4 GB's).
However it may be the 2GB database size limit that your bumping into as 8080_Diver suggested, which there is no way of getting around in a single operation.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
Thanks !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.