A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access Database size limits linking to SQL server



 
 
Thread Tools Display Modes
  #1  
Old November 2nd, 2009, 02:08 PM posted to microsoft.public.access.forms
Mustanggt89 via AccessMonster.com
external usenet poster
 
Posts: 3
Default Access Database size limits linking to SQL server

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

--
Message posted via http://www.accessmonster.com

  #2  
Old November 2nd, 2009, 03:14 PM posted to microsoft.public.access.forms
Brendan Reynolds
external usenet poster
 
Posts: 1,241
Default Access Database size limits linking to SQL server


"Mustanggt89 via AccessMonster.com" u52205@uwe wrote in message
news:9e837cd053bfd@uwe...
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.

--
Brendan Reynolds

  #3  
Old November 2nd, 2009, 11:06 PM posted to microsoft.public.access.forms
Dale Fye
external usenet poster
 
Posts: 2,651
Default Access Database size limits linking to SQL server

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



"Mustanggt89 via AccessMonster.com" wrote:

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

--
Message posted via http://www.accessmonster.com

.

  #4  
Old November 3rd, 2009, 01:11 AM posted to microsoft.public.access.forms
Mustanggt89 via AccessMonster.com
external usenet poster
 
Posts: 3
Default Access Database size limits linking to SQL server

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?

Dale Fye wrote:
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


--
Message posted via http://www.accessmonster.com

  #5  
Old November 3rd, 2009, 01:32 AM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Access Database size limits linking to SQL server

On Tue, 03 Nov 2009 00:11:56 GMT, "Mustanggt89 via AccessMonster.com"
u52205@uwe wrote:

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.
--

John W. Vinson [MVP]
  #6  
Old November 3rd, 2009, 03:44 AM posted to microsoft.public.access.forms
Mustanggt89 via AccessMonster.com
external usenet poster
 
Posts: 3
Default Access Database size limits linking to SQL server

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 wrote:
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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

  #7  
Old November 3rd, 2009, 04:49 AM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Access Database size limits linking to SQL server

On Tue, 03 Nov 2009 02:44:42 GMT, "Mustanggt89 via AccessMonster.com"
u52205@uwe wrote:

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...!
--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:37 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.