Update all rows in a large database using Access 2002, MDAC 2.7, Jet 4.0 
Author Message
 Update all rows in a large database using Access 2002, MDAC 2.7, Jet 4.0

Hi all,

I have an Access db of approx. 5 mil rows occupying 1.1 gig of disk space
which I created. Unfortunately, I mistakenly specified a date/time field as
String (instead of DateTime) when designing the table, and the date/times
are not all in the same format, so they naturally don't sort right.. Now I
cannot change the field type to DateTime because I get the error msg that
Access can't change the data type because "There isn't enough disk space or
memory" (in spite of that I have 768 meg of memory, and 12 gig of free disk
space on the drive where the db resides).

Well, ok. I may be stuck with String, but what's the best way of rendering a
uniform format programatically? Is it possible to use a Recordset, and do an
"Update" on each row after reformatting the date/time value? But a Recordset
with 5 mil rows -- is that even possible? Or should I consider using a
command object Execute with command text like "Update mytable Set fDate =
'Format(fDate, "yyyy-mm-dd hh:mm:ss")'"? (I'm hoping that functions can be
used in SQL statements...)

I'd appreciate a little guidance on this, as I'm not at all sure. Thanks in
advance. And sorry my questions had to be so long...

-Lew



Sun, 22 May 2005 07:22:55 GMT  
 Update all rows in a large database using Access 2002, MDAC 2.7, Jet 4.0
Create a make table query that has all the fields of  your table, EXCEPT,
replace the field with the text dates with a field of the same name
containing a function to convert the text to a date value -

Date: DateValue ( [My Table]![TextualDateField] )


Quote:
> Hi all,

> I have an Access db of approx. 5 mil rows occupying 1.1 gig of disk space
> which I created. Unfortunately, I mistakenly specified a date/time field
as
> String (instead of DateTime) when designing the table, and the date/times
> are not all in the same format, so they naturally don't sort right.. Now I
> cannot change the field type to DateTime because I get the error msg that
> Access can't change the data type because "There isn't enough disk space
or
> memory" (in spite of that I have 768 meg of memory, and 12 gig of free
disk
> space on the drive where the db resides).

> Well, ok. I may be stuck with String, but what's the best way of rendering
a
> uniform format programatically? Is it possible to use a Recordset, and do
an
> "Update" on each row after reformatting the date/time value? But a
Recordset
> with 5 mil rows -- is that even possible? Or should I consider using a
> command object Execute with command text like "Update mytable Set fDate =
> 'Format(fDate, "yyyy-mm-dd hh:mm:ss")'"? (I'm hoping that functions can be
> used in SQL statements...)

> I'd appreciate a little guidance on this, as I'm not at all sure. Thanks
in
> advance. And sorry my questions had to be so long...

> -Lew



Sun, 22 May 2005 12:28:32 GMT  
 Update all rows in a large database using Access 2002, MDAC 2.7, Jet 4.0
Hi


Quote:
> Create a make table query that has all the fields of  your table, EXCEPT,
> replace the field with the text dates with a field of the same name
> containing a function to convert the text to a date value -

> Date: DateValue ( [My Table]![TextualDateField] )

[cut]

Quote:
> > I have an Access db of approx. 5 mil rows occupying 1.1 gig of disk space
> > which I created. Unfortunately, I mistakenly specified a date/time field
> as
[cut]
> > memory" (in spite of that I have 768 meg of memory, and 12 gig of free
> disk
> > space on the drive where the db resides).

> > Well, ok. I may be stuck with String, but what's the best way of rendering
> a
> > uniform format programatically? Is it possible to use a Recordset, and do
> an
> > "Update" on each row after reformatting the date/time value? But a
> Recordset

[cut]

1) Create a new field in your DB with the right specification.

2.) Do a Query with the following SQL Statement

   "UPDATE TableName SET TableName.NewFieldName = OldFieldName;"

But there is a limit with access. An accessdb cannot become bigger than proximally 2GB
including all system tables and so on.

Unfortunately I can't find the link from MS where this is written.

-Christian



Sun, 22 May 2005 19:23:40 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Access 2002, AD 2.7, VB 6.0 Enterprise retrieval

2. Access 2002 DB, Jet 4.0, VB6.0 Ent

3. Does MDAC 2.7 Support RDO ?

4. MDAC 2.7 THAI

5. WinXP, MDAC 2.7 and Oracle8i connection issue

6. WISE MDAC 2.7

7. MDAC 2.7 silent install

8. GUID problem in MDAC 2.7

9. MDAC 2.5 or 2.7

10. Upgrding to MDAC 2.7

11. MDAC 2.7

12. MDAC 2.7???

 

 
Powered by phpBB® Forum Software