Re: ADO/ALTER TABLE problem
From: Brendan Reynolds (brenreyn)
Date: 01/18/05
- Next message: SomeoneKnows: "Re: datagrid updating from join to single table"
- Previous message: ghosthunter_at_gmx.at: "Re: Recordset.open with existing Records in the DB"
- In reply to: Brian Morris: "Re: ADO/ALTER TABLE problem"
- Next in thread: Brian Morris: "Re: ADO/ALTER TABLE problem"
- Reply: Brian Morris: "Re: ADO/ALTER TABLE problem"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 18 Jan 2005 11:39:30 -0000
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 >> >> > >> >> > >> >> >> >> >> > >> > >> >> > >
- Next message: SomeoneKnows: "Re: datagrid updating from join to single table"
- Previous message: ghosthunter_at_gmx.at: "Re: Recordset.open with existing Records in the DB"
- In reply to: Brian Morris: "Re: ADO/ALTER TABLE problem"
- Next in thread: Brian Morris: "Re: ADO/ALTER TABLE problem"
- Reply: Brian Morris: "Re: ADO/ALTER TABLE problem"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
