Access Database size limits linking to SQL server

[Mustanggt89 via AccessMonster.com]

I have a database that was 1.8 Gig in size, I moved all the larger tables to
SQL Server and linked them to my Access Database. Now when I run the Access
Database to append my table on SQL server I still get an error message that
the SQL can not be run becasue of size of the table will be greater than 2
Gig? Does Access use a temp file the append the data that is causing this
error? I have 1 gig of ram on my PC and 4 gig of virtual ram to run on. This
is with Access 2007 and SQL server 2005. I've deleted all my temp files and
folders before running and compress my entire disk and still get this error.
The table has a little over 750,000 rows on it and takes a few hours to
normally run. I guess I'm looking for a way to get this to run faster and at
least finish the Append query.

Thanks

 

[Brendan Reynolds]

I have a database that was 1.8 Gig in size, I moved all the larger tables
to
SQL Server and linked them to my Access Database. Now when I run the
Access
Database to append my table on SQL server I still get an error message
that
the SQL can not be run becasue of size of the table will be greater than 2
Gig? Does Access use a temp file the append the data that is causing this
error? I have 1 gig of ram on my PC and 4 gig of virtual ram to run on.
This
is with Access 2007 and SQL server 2005. I've deleted all my temp files
and
folders before running and compress my entire disk and still get this
error.
The table has a little over 750,000 rows on it and takes a few hours to
normally run. I guess I'm looking for a way to get this to run faster and
at
least finish the Append query.

I would try creating a new, empty Access database, importing just the one
table from the old database into the new one, then link the SQL Server table
and do the append. If you can successfully append that table, then create
another new, empty Access database and do the same thing with the next
table.

 

[Dale Fye]

If you have not compacted your database since you moved the larger tables to
SQL Server, you probably need to do that. Back it up first.

 

[Mustanggt89 via AccessMonster.com]

I have compacted the DB several times the ACCESS portion is 3,256 KB in size,
but when the SQL rus and uses the linked SQL server table, it is building a
temp file that is over 2 gig. Is there anyway to not use the temp file, or
committ the records every so often to the file so the temp file doesn't get
to large?

If you have not compacted your database since you moved the larger tables to
SQL Server, you probably need to do that. Back it up first.

----
HTH
Dale

I have a database that was 1.8 Gig in size, I moved all the larger tables to
SQL Server and linked them to my Access Database. Now when I run the Access

[quoted text clipped - 9 lines]

Thanks

 

[John W. Vinson]

I have compacted the DB several times the ACCESS portion is 3,256 KB in size,
but when the SQL rus and uses the linked SQL server table, it is building a
temp file that is over 2 gig. Is there anyway to not use the temp file, or
committ the records every so often to the file so the temp file doesn't get
to large?

If you're appending data from one linked SQL table into another linked SQL
table, consider using a "Passthrough" query so it's all handled on the server.
This will not generate a (local) temp table and will not be subject to Access
size limits.

 

[Mustanggt89 via AccessMonster.com]

John,

Thanks, does that also work if one table is on Sql Server and the others are
in Oracle but are also linked in.

 

[John W. Vinson]

John,

Thanks, does that also work if one table is on Sql Server and the others are
in Oracle but are also linked in.

Unfortunately, probably not at all easily, especially if your Access database
is the only link between the two servers. I'm sure it's possible to have
SQL/Server get at Oracle tables and vice versa, but I've not had any
experience doing so!

good luck... you're going to need it...!