January 8, 2009 at 8:10 pm
Hi all,
Here is the deal. I'm in Access 2007.
I have table 'cases' with a field 'notes' (memo)
I have table 'time_actvy' with a field 'tm_comment' (memo) linked to the cases table by 'case_no'
I am trying to concatenate all of the time activity comments into the cases notes field separated by a crlf
Here is what I am trying in Access:
UPDATE cases
INNER JOIN tm_actvy
ON cases.cs_caseno=tm_actvy.cs_caseno
SET cases.cs_notes=cases.cs_notes & chr(13) & chr(10) & tm_actvy.tm_comment
WHERE tm_actvy is not null
Edit:
When I run this query, it fails because it keeps running up against the 2gb file limit. Problem is, the source file is only like 700mb. Extracting a single field out and appending it into another 500mb file should result in only a gb or a little more at the most. There is so much data I cant quite tell what is going on. Could I be in some kind of loop filling the file up with duplicate data?
January 19, 2009 at 11:05 am
Only strange thing I see in your query is that tm_actvy appears in both as a table name and a column name, is that correct?
Other thing is that you should consider if you want to move this database to SQL Server Express. That can already store up to 4GB and is free. Front end can still be in the Access environment with linked tables or through ADODB code.
Hey, what other answer could you expect on a SQL Server forum?
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
February 1, 2009 at 10:20 am
gabe,
By default Access runs queries in a transaction, so it uses 2x the space that you would expect it to.
First, try setting your query property UseTransaction = No
If that doesn't work, try running your query from VBA like this (this should all be on one line):
DoCmd.RunSQL "UPDATE cases INNER JOIN tm_actvy ON cases.cs_caseno=tm_actvy.cs_caseno SET cases.cs_notes=cases.cs_notes & chr(13) & chr(10) & tm_actvy.tm_comment WHERE tm_actvy is not null"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply