need to update a querie

[Dimitris]

Hello,

I have a table with 1700000 records. I have a field that is a text field and
I want to make it a numeric field. Since it has numbers entered.
I can't change the file type from text to number since I get the message
that there is not enough disk space to proceed to the change. I have been
told that this can be achieved with some kind of update.
Can someone please help me?
Thank you in advance.

Dimitris

 

[Douglas J. Steele]

Compact the database first, just in case there's free space available.

Try adding a new numeric field to your table, then using an Update query to
populate it from the existing text field. Once you know it's okay, delete
the text field, and compact your database again.

Under the covers, I believe Access is trying to create a new version of the
table then deleting the old one. With a table that large, it's probable that
you're exceeding the maximum size of the database while it's trying to do
that.

 

[Dimitris]

I still can't do it.
Again it tells me I don't have enough disk space.
Which by the way I do.
I have alot of free disk space. I'm wondering am I doing something wrong.
Can you please tell me what is the correct way to update it.
TableName=T2004
Textfield=In04

Should I update somehow in a new table I will create or try again here.
Thanks
Dimitris

 

[John Spencer]

First, how big is the current database? What version of Access are you
using?

You might be able to do this incrementally.
-- Compact the database
-- Add a new field - number type Long (if your present field has no
decimal portion)
-- Run an update query that selects only a limited number of records and
updates them.

UPDATE YourTable
SET [NewNumberField] = Val([TheTextField])
WHERE [NewNumberField] Is Null and Val([TheTextField]) < = 1000

-- Check the size of the database. Has it grown too much? If so, compact
and then repeat.

If not, then you could run something like the following in a loop in a VBA
function and check the size of your database after each iteration. When you
approach the maximum size for an Access database then exit the loop and
compact the the database.

UPDATE YourTable
SET [NewNumberField] = Val([TheTextField])
WHERE [TheTextField] IN
(SELECT TOP 1000 [TheTextField]
FROM YourTable
WHERE [NewNumberField] is NULL
ORDER BY [TheTextField])

If you can't construct the necessary VBA, post back.

 

[Douglas J. Steele]

How big is the MDB currently? (and what version of Access are you using?)

Access 2000 and newer does not support an MDB file of larger than 2 Gb
(Access 97 and earlier only support 1 Gb). If you're close to that, then
you're not going to be able to make the necessary change in your database.
What you could try doing is creating a new database and linking to the table
in your existing database. In the new database, create a copy of the table
(no data) with the numeric field rather than the text field. Write an Append
query (INSERT INTO) that takes the data from the linked table and populates
the new table. Once you know that table is okay, go back into your original
database, delete the table, then import the new table in.