Re: ADO/ALTER TABLE problem

From: Brian Morris (softcom_at_tstt.net.tt)
Date: 01/18/05 

Date: Tue, 18 Jan 2005 10:57:11 -0400

What I want to do is to be able to add new fields to tables using either ADO
code or sometimes SQL, depending on the back-end. For general knowledge I
had mapped the schema ADO data-types to the SQL data-types. Now I'm not
sure if what I came up with is entirely correct. For example I've been
creating new Access fields as adChar when I use ADO code when clearly I
should have used adVarChar. I'd just like to get a little routine to get
the correct thing.

Experience is a good teacher.

Thanks
Brian

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
news:Oai8fJV$EHA.1452@TK2MSFTNGP11.phx.gbl...
> Mostly, Brian, if I'm not sure what the data type is, I'll open the table
in
> design view and look. Needing to determine the data type at run time is
not
> something that I've had to do very often. Possibly if you describe the
> circumstances in which you need to do this, I or someone else might be
able
> to suggest something.
>
> I'm not sure what you mean about 'where did I find the info on what the UI
> does'. You mean the fact that text fields defined via the UI are variable
> length? I'm afraid I can't remember. I'm an Access developer, I've spent
> most of my working time in the Access UI for several years now, so this is
> one of those things that I've known for so long that I can't remember when
> or where I first learned it.
>
> --
> Brendan Reynolds (MVP)
> http://brenreyn.blogspot.com
>
> The spammers and script-kiddies have succeeded in making it impossible for
> me to use a real e-mail address in public newsgroups. E-mail replies to
> this post will be deleted without being read. Any e-mail claiming to be
> from brenreyn at indigo dot ie that is not digitally signed by me with a
> GlobalSign digital certificate is a forgery and should be deleted without
> being read. Follow-up questions should in general be posted to the
> newsgroup, but if you have a good reason to send me e-mail, you'll find
> a useable e-mail address at the URL above.
>
>
> "Brian Morris" <softcom@tstt.net.tt> wrote in message
> news:e5aGdcQ$EHA.1392@tk2msftngp13.phx.gbl...
> > Based on your LEN() example I definitely have to use VARCHAR.
> > I've also found that TEXT does the same as VARCHAR so I think I'll use
> > this
> > so that I don't confuse myself thinking about the equivalent adVarChar
and
> > adChar.
> > How then do you get the correct datatype for a field in a table?
> > Where did you find the info on what the UI does?
> > Thanks a lot
> > Brian
> >
> >
> >
> > "Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
> > news:%237y64zP$EHA.2568@TK2MSFTNGP10.phx.gbl...
> >> It does seem to be a bit of a mine-field, and not terribly well
> >> documented
> >> as far as I can tell. There are all sorts of surprising (to me at
least)
> >> differences dependant on how a table or column was created - UI, ADO,
or
> >> DAO.
> >>
> >> The Access UI doesn't provide any means of creating a fixed-length text
> >> field, and Access help even refers to CHAR and VARCHAR as synonyms, but
> >> if
> > I
> >> do this ...
> >>
> >> Public Sub AddCols()
> >>
> >> CurrentProject.Connection.Execute "ALTER TABLE tblTest ADD COLUMN
> > Text2
> >> CHAR(50)"
> >> CurrentProject.Connection.Execute "ALTER TABLE tblTest ADD COLUMN
> > Text3
> >> VARCHAR(50)"
> >>
> >> End Sub
> >>
> >> Public Sub AddData()
> >>
> >> 'Text1 was created via the UI. There are no other records
> >> 'in the table, so the record we read when we open the
> >> 'recordset is the same one we insert via the SQL statement.
> >>
> >> Dim rst As ADODB.Recordset
> >>
> >> CurrentProject.Connection.Execute "INSERT INTO tblTest (Text1,
Text2,
> >> Text3) VALUES ('abc', 'abc','abc')"
> >> Set rst = New ADODB.Recordset
> >> With rst
> >> .ActiveConnection = CurrentProject.Connection
> >> .Source = "SELECT * FROM tblTest"
> >> .Open
> >> Debug.Print Len(.Fields("Text1")), Len(.Fields("Text2")),
> >> Len(.Fields("Text3"))
> >> .Close
> >> End With
> >>
> >> End Sub
> >>
> >> ... the result in the Immediate window is ...
> >>
> >> addcols
> >> adddata
> >> 3 50 3
> >>
> >> ... demonstrating that the second column, 'Text2', has been created as
a
> >> fixed-length column.
> >>
> >> To the best of my knowledge, in Jet 4, text fields are always Unicode.
> >>
> >> But why, using your OpenSchema code, we get 130, adWChar, for all three
> >> fields, is a mystery to me. I would have expected adVarWChar for
'Text1'
> > and
> >> 'Text3', and adWChar for 'Text2'.
> >>
> >> --
> >> Brendan Reynolds (MVP)
> >> http://brenreyn.blogspot.com
> >>
> >> The spammers and script-kiddies have succeeded in making it impossible
> >> for
> >> me to use a real e-mail address in public newsgroups. E-mail replies to
> >> this post will be deleted without being read. Any e-mail claiming to be
> >> from brenreyn at indigo dot ie that is not digitally signed by me with
a
> >> GlobalSign digital certificate is a forgery and should be deleted
without
> >> being read. Follow-up questions should in general be posted to the
> >> newsgroup, but if you have a good reason to send me e-mail, you'll find
> >> a useable e-mail address at the URL above.
> >>
> >>
> >> "Brian Morris" <softcom@tstt.net.tt> wrote in message
> >> news:Oylr0TO$EHA.2156@TK2MSFTNGP10.phx.gbl...
> >> > Thanks. You are right.
> >> > But I checked the datatype using...
> >> > Dim rs As ADODB.Recordset
> >> > Set rs = CurrentProject.Connection.OpenSchema(adSchemaColumns,
> >> > Array(Empty, Empty, "table1", Empty))
> >> > With rs
> >> > Do While Not .EOF
> >> > Debug.Print .Fields("COLUMN_NAME"), .Fields("DATA_TYPE"),
> >> > .Fields("CHARACTER_MAXIMUM_LENGTH")
> >> > .MoveNext
> >> > Loop
> >> > End With
> >> > rs.Close
> >> > and the field DATA_TYPE is adWChar=130 instead of adVarChar=200
> >> > What ADO datatype should I be using when I'm creating fields and
> >> > parameters
> >> > then? And worse, when I have to test field datatypes?
> >> > Thanks a lot
> >> > Brian
> >> >
> >> > "Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
> >> > news:esqcuAO$EHA.1400@TK2MSFTNGP11.phx.gbl...
> >> >> You're not really comparing like with like. Your DDL statement is
> >> >> defining
> >> > a
> >> >> fixed length, while text fields defined via the UI are variable
> >> >> length.
> >> >> Altering the field size via the UI would be the equivalent of ...
> >> >>
> >> >> cnn.Execute "ALTER TABLE table1 ALTER COLUMN txt VARCHAR(50)"
> >> >>
> >> >> ... note VARCHAR rather than CHAR.
> >> >>
> >> >> --
> >> >> Brendan Reynolds (MVP)
> >> >> http://brenreyn.blogspot.com
> >> >>
> >> >> The spammers and script-kiddies have succeeded in making it
impossible
> >> >> for
> >> >> me to use a real e-mail address in public newsgroups. E-mail replies
> >> >> to
> >> >> this post will be deleted without being read. Any e-mail claiming to
> >> >> be
> >> >> from brenreyn at indigo dot ie that is not digitally signed by me
with
> > a
> >> >> GlobalSign digital certificate is a forgery and should be deleted
> > without
> >> >> being read. Follow-up questions should in general be posted to the
> >> >> newsgroup, but if you have a good reason to send me e-mail, you'll
> >> >> find
> >> >> a useable e-mail address at the URL above.
> >> >>
> >> >>
> >> >> "Brian Morris" <softcom@tstt.net.tt> wrote in message
> >> >> news:OnB45ZN$EHA.3120@TK2MSFTNGP12.phx.gbl...
> >> >> > I've noticed that if I use an ADO connection to increase the size
of
> > a
> >> >> > field
> >> >> > from 40 to 50 characters with DDL SQL, the MDB jumps from 6MB to
> > 12MB,
> >> >> > EVEN
> >> >> > AFTER COMPACTING twice.
> >> >> >
> >> >> > If I do the same thing using the Access Table Design the filesize
> > does
> >> > not
> >> >> > change AT ALL!
> >> >> >
> >> >> > My code is...
> >> >> > cnn.Execute "ALTER TABLE table1 ALTER COLUMN txt CHAR(50)"
> >> >> >
> >> >> > Is something wrong? I can't find reference to this at all.
> >> >> >
> >> >> > Thanks for the help.
> >> >> > Brian
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Relevant Pages