Can anyone tell me how to change the data type of a field in a table created
with a make table query? The field is a binary and must be changed to text.
alternately does anyone know how to specify the field type when running a
make table query?
Thanks,
Sven
13  2905 
You can't change the data type of a field in a table. You have to create a new table
with your changes, load the data and drop the old table.
--
Danny J. Lesandrini dl*********@hot mail.com http://amazecreations.com/datafast
"Peter" <pe***@diel.co. za> wrote in message news:br******** **@ctb-nnrp2.saix.net. .. Can anyone tell me how to change the data type of a field in a table created
with a make table query? The field is a binary and must be changed to text.
alternately does anyone know how to specify the field type when running a
make table query?
Thanks,
Sven
Thanks I feared as much, but is there any way in a sql statement to specify
the data-types of the fields being added into a new table
"Danny J. Lesandrini" <dl*********@ho tmail.com> wrote in message
news:br******** ***@ID-82595.news.uni-berlin.de... You can't change the data type of a field in a table. You have to create
a new table with your changes, load the data and drop the old table.
--
Danny J. Lesandrini
dl*********@hot mail.com
http://amazecreations.com/datafast
"Peter" <pe***@diel.co. za> wrote in message
news:br******** **@ctb-nnrp2.saix.net. .. Can anyone tell me how to change the data type of a field in a table
created with a make table query? The field is a binary and must be changed to
text. alternately does anyone know how to specify the field type when running
a make table query?
Thanks,
Sven
On Wed, 10 Dec 2003 14:25:39 +0200, "Peter" <pe***@diel.co. za> wrote: Can anyone tell me how to change the data type of a field in a table created
with a make table query? The field is a binary and must be changed to text.
alternately does anyone know how to specify the field type when running a
make table query?
You need to alter it in your Query.
Presuming that your field is called bField in table Table1, your
"output" field could be something like
sField: Str([Table1].[bField])
I have to use this converting numeric values to string ones , and vice
versa.
Don't know if it will work with your case though...
Tym
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~
See http://www.ictis.net/no_spam.html for unsolicited email warning
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~
On Wed, 10 Dec 2003 05:39:05 -0700, "Danny J. Lesandrini"
<dl*********@ho tmail.com> wrote:
Actually, that's not quite true. I typically add a new field with the
correct datatype, run an update statement to copy over the data (if
possible), delete the old field, and rename the new field.
-Tom. You can't change the data type of a field in a table. You have to create a new table
with your changes, load the data and drop the old table.
"Danny J. Lesandrini" <dl*********@ho tmail.com> wrote in news:br746r$275 f$1
@ID-82595.news.uni-berlin.de: You can't change the data type of a field in a table.
In Access XP one can toggle the data type of a field as follows:
DAO.DBEngine(0) (0).Execute "ALTER TABLE " _
& "[D:\My Documents\Acces s\db1.mdb].tbl2002Transac tions " _
& "ALTER COLUMN fldDescription TEXT"
DAO.DBEngine(0) (0).Execute "ALTER TABLE " _
& "[D:\My Documents\Acces s\db1.mdb].tbl2002Transac tions " _
& "ALTER COLUMN fldDescription BINARY"
The table's being in an external database is incidental to this example. The
code can change the data type of fields in internal tables.
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
I knew that statement would bring comments, but the way I understand it,
even in SQL Server, you CANNOT change the type of f field. You're
given the impression that you are changing the datatype, but behind the
scenes it's building a new table, copying data and dropping the old table.
Does Access do the same when, in design view, you change a datatype?
I don't really know. Michka probably does, but if I had to guess, I'd say
that's what's happening. I say that because I know you can't use DAO to
change the datatype the property of a field. It doesn't allow you. Why?
Because you can't simply toggle a field type.
Again, all of this is illusion, since you can work around this. I asked this
question to the group once, and someone answered, "Why would you
want to change a field type in code?" Not a bad question. For such an
important modification, you might as well go to each table in design mode,
verify your changes and implement them with caution and purpose.
--
Danny J. Lesandrini dl*********@hot mail.com http://amazecreations.com/datafast
"Tom van Stiphout" <to*****@no.spa m.cox.net> wrote in message news:og******** *************** *********@4ax.c om... On Wed, 10 Dec 2003 05:39:05 -0700, "Danny J. Lesandrini"
<dl*********@ho tmail.com> wrote:
Actually, that's not quite true. I typically add a new field with the
correct datatype, run an update statement to copy over the data (if
possible), delete the old field, and rename the new field.
-Tom.
You can't change the data type of a field in a table. You have to create a new table
with your changes, load the data and drop the old table.
"Danny J. Lesandrini" <dl*********@ho tmail.com> wrote in news:br7dq8$69e e$1
@ID-82595.news.uni-berlin.de: I knew that statement would bring comments, but the way I understand it,
even in SQL Server, you CANNOT change the type of f field.
Wrong.
Lyle, I'm being dogmatic. Yes, of course, you can change the field type,
but as I said, it's my understanding that what you are REALLY doing is
building a new table with the correct field types and migrating data. This
all happens behind the scenes so you don't even realize it.
Why do I bother to raise this point? Because this little slight of hand is
the reason you can't do this
Dim fld As DAO.Field
Set fld = tdf.Fields("MyF ield")
If fld.Type = 8 Then fld.Type = 9
Can't do this! Why? Because you CANNOT change a field type.
That's all I'm saying.
--
Danny J. Lesandrini dl*********@hot mail.com http://amazecreations.com/datafast
"Lyle Fairfield" <Mi************ @Invalid.Com> wrote in message news:Xn******** ***********@130 .133.1.4... "Danny J. Lesandrini" <dl*********@ho tmail.com> wrote in news:br7dq8$69e e$1
@ID-82595.news.uni-berlin.de:
I knew that statement would bring comments, but the way I understand it,
even in SQL Server, you CANNOT change the type of f field.
Wrong.
"Danny J. Lesandrini" <dl*********@ho tmail.com> wrote in message
news:br******** ***@ID-82595.news.uni-berlin.de... I knew that statement would bring comments, but the way I understand it,
even in SQL Server, you CANNOT change the type of f field. You're
given the impression that you are changing the datatype, but behind the
scenes it's building a new table, copying data and dropping the old table.
SQL Server 2000/03 will cascade the data type change through all fields
("columns" in SQL Server) that are impacted by the change, i.e., according
to the PK>FK relationships. Your theory about new tables being created
"behind the scenes," etc. is doubtful, becuase the process is extremely
rapid even in large MDF's running on very old servers. (Consider the
machine resources that would be needed to perform a data type change in the
manner you propose.)
The only change that takes place "behind the scenes" that SQL Server will
admit to is that any indexes on the changed field will be discarded and
rebuilt. However, related fields will have their data type, length,
prcescion, scale, and collation changed automatically as the data type
change cascades. Presumeably, their indexes are discarded and rebuilt,
also.
Notes:
1) In versions prior to SQL Server 2000, the data type change might not
cascade. In fact, PK>FK relationships might be deleted, especially if you
change the FK data type. But in SQL Server 2000 and later, data type
changes cascade from either end of the relationship.
2) The ALTER TABLE procedure in TSQL does not allow many of the column
changes mentioned here.
Does Access do the same when, in design view, you change a datatype?
I don't really know. Michka probably does, but if I had to guess, I'd say
that's what's happening. I say that because I know you can't use DAO to
change the datatype the property of a field. It doesn't allow you. Why?
Because you can't simply toggle a field type.
The only thing dumber than Michael Kaplan is DAO. You picked a bad example.
Again, all of this is illusion, since you can work around this. I asked
this question to the group once, and someone answered, "Why would you
want to change a field type in code?" Not a bad question. For such an
important modification, you might as well go to each table in design mode,
verify your changes and implement them with caution and purpose.
Granted. But you have stipulated "in code," which changes the central
argument slightly. And it is not clear if we are talking about Access Jet
or SQL Server. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: iporter |
last post by:
Hi - I am changing a field from type nvarchar to type text, given that
I need to store strings longer than 255 characters. To do this I
change the data type in SQL Server, then I change the parameter code in
the calling procedure, as per below:
cmd.Parameters.Append(cmd.CreateParameter("@title", adVarWChar,
adParamInput, 255, title));
becomes:
|
by: SenseForAll |
last post by:
First please note I am a novice at VBA and not even that experienced
with DAO/ADO and MS-SQL. Any assistance is appreciated. That said...
I have an application written in Access w/ VBA. I need to get some
data out of a SQL server and into an Access table. I don't know
anything about the SQL server data model or structure. I don't have
access rights to link to the SQL table. What I do have is the rights
and information to run a stored...
|
by: Betrock |
last post by:
This is probably very simple, but I just can't see my way thru it.....
Short version: keyed values(numeric)in a lookup table are stored in a
main table. They are displayed as text values - the keyed values
description. I need a parameter query to return a range of these
values. The parameter input won't accept the 'text' version. You can
do it with 'Find' because you can select 'as formatted'. But I need a
range. Please, any...
|
by: sparks |
last post by:
I was copying fields from one table to another.
IF the var name starts with milk I change it to egg and create it in
the destination table.
It works fine but I want to copy the description as well.
Short version :)
For Each fld In tdf.Fields
pos = InStr(fld.Name, "milk")
If pos > 0 Then
|
by: Dan Sikorsky |
last post by:
How do you iterate thru a dataset to change money fields to a different
value?
Here's what I have. My dataset is filled directly from a stored procedure.
' Create Instance of Connection and Command Object
Dim myConnection As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As New SqlDataAdapter("OrdersList", myConnection)
| | |
by: Anil Gupte |
last post by:
After reading a tutorial and fiddling, I finally got this to work. I can
now put two tables created with a DataTable class into a DataRelation.
Phew! And it works!
Dim tblSliceInfo As New DataTable("SliceInfo")
Dim tblSliceRatings As New DataTable("SliceRatings")
'.... All the adding datacolumns, datarows, etc. goes here..
DatasetInit.Tables.Add(tblSliceInfo)
|
by: mlwerth |
last post by:
Dear Access Group:
This is the most basic and most embarrassing of questions, but I
cannot find where to change the data type of a text field that I have
in Access 2003 to a number field. I've searched high and low through
help databases and on the internet.
The directions say to :
Open the table in Design view
Click the Data Type column of the field you want to change, click the
|
by: gubbachchi |
last post by:
Hi all,
Please anybody help me solve this problem. I am stuck up with this from past 2 weeks. I am developing an application where, when the user selects date from javascript datepicker and enters the comments and clicks the save button then the date and the date will be stored in the mysql database. This is working fine. But my problem is when, after the user had made an entry the date in the calendar for which an entry has made should be...
|
by: ADezii |
last post by:
One question which pops up frequently here at TheScripts is: 'How do I retrieve data from a Recordset once I've created it?' One very efficient, and not that often used approach, is the GetRows() Method of the Recordset Object. This Method varies slightly from DAO to ADO, so for purposes of this discussion, we'll be talking about DAO Recordsets. The ADO approach will be addressed in the following Tip. We'll be using a Query, consisting of 5...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
