Chg field fr 'text' to 'number'

[Guest]

Hello everyone! Using A02 on XP. Have client Excel files that are exported
from Access and after client edits are imported back into my DB in a
'Revised' table. Need to run a compare of the two tables linking on [ID].
Problem is the exported file formats all the fields as 'General' and when I
import the edited file the AutoNumber field [ID] is brought in as 'text'
rather than 'number'. Is there a way to include in one of my button clicks
on my form something that will open that table and change the data type on
just that field from 'text' to 'number'?

Hope I've described it well enough. Thanks in advance for any and all help
or advice.

 

[Douglas J Steele]

You could link to Excel (rather than Importing), and create a query based on
the imported table that uses the Val function (or CLng) around ID to convert
it to a number. You could either use that query to compare to the existing
table, or you could use the query as the basis for a Make Table query.

 

[chris.nebinger]

What kind of number: You could run the following SQL statement:

ALTER TABLE TableName ALTER COLUMN [ID] LONG


Now, you can execute that by saving it as a query, and running the
query, or by running the SQL on a DoCMD.RunSQL or CurrentDB.Execute
statements.

Like, under the code for the button click event:

CurrentDB.Execute " ALTER TABLE TableName ALTER COLUMN [ID] LONG"


Chris Nebinger

 

[Guest]

Chris, Chris, Chris...I Luv U! It works! Just one problem though and hoping
you can help.

I have a button that renames the table to be contract specific and I put
your wordage just before the renaming.

CurrentDb.Execute " ALTER TABLE Census1Revised ALTER COLUMN [ID] LONG"
DoCmd.CopyObject , [RunThisOne] & "Revised", acTable, "Census1Revised"

It works but I'm getting an error that Debug shows highlighting the
DoCmd.CopyOpbject line. Both commands work but the error is:

Run Time Error 3313: Cannot place this validation expression on this field.

Any way to stop it? Error trap? Think I did one once. Or should I just
put the commands to separate buttons? Would prefer one-stop clicking.

Lemeno? Thx!

 

[chris.nebinger]

I can only think of a few things:

1: The SQL is failing. Try changing it to:
CurrentDb.Execute " ALTER TABLE Census1Revised ALTER COLUMN [ID] LONG"
, dbFailOnError

2: What is [RunThisOne]? I don't use braces that often in code, so I'm
100% sure what it is? Is it a variable, field, or form control?

3: when you run the alter column, it will convert the data. Depending
on the number of rows, it is possible that it's taking a long time, but
the code keeps running. Try adding a delay:

dim dteEnd as Date
dteEnd = dateadd("n",5,now())
CurrentDb.Execute " ALTER TABLE Census1Revised ALTER COLUMN [ID] LONG"
, dbFailOnError
Do until Now > dteEnd
DoEvents
Loop
DoCmd.CopyObject , [RunThisOne] & "Revised", acTable, "Census1Revised"



Chris Nebinger

 

[Guest]

Hello again Chris. When I use your delay the rename doesn't happen at all.
Tried 2 buttons but now my rename doesn't work without the error. Didn't
give it before. Tried a new button, same Error 3313. My table is there
([RunThisOne] is a field on my form with the contract number in it) and named
correctly. My field in the table has been altered from text to a number.
Record count never tops a few hundred. I think I'll just trap that error
unless you can think of something else I should try. Thanks SO much for your
help. Love to learn new things.

 

[chris.nebinger]

I really didn't think the delay would help, and it would probably work
after 5 minutes, but often times just adding that delay solves these
little bugs.

As I understand it, you want to rename the table from Census1Revised to
Me.RunThisOne & "Revised", correct? If so, then you could do a:

DoCmd.Rename Me.RunThisOne & "Revised",acTable ,"Census1Revised"

This would not give you an additional copy, but would rename the table.


Chris Nebinger