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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|