Open another database and change a data type

[Keith Wilby]

A97.

I want to be able to use code to open change the data type of a field in a
table in another database - can this be done?

Many thanks.
Keith.

[Lyle Fairfield]

Re: Open another database and change a data type

Keith Wilby <keith.wilby@Aw ayWithYerCrap.c om> wrote in
news:Xns94367C4 C9A8FBkeithwilb y@10.15.188.42:
[color=blue]
> A97.
>
> I want to be able to use code to open change the data type of a field in a
> table in another database - can this be done?
>
> Many thanks.
> Keith.[/color]

DAO.DBEngine(0) (0).Execute "ALTER TABLE " _
& "[D:\My Documents\Acces s\db1.mdb].tbl2002Transac tions " _
& "ALTER COLUMN fldDescription TEXT"

Changes field type from binary to text.

--


Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Keith Wilby]

Re: Open another database and change a data type

Lyle Fairfield <MissingAddress @Invalid.Com> wrote:
[color=blue][color=green]
>> I want to be able to use code to open change the data type of a field
>> in a table in another database - can this be done?
>>
>> Many thanks.
>> Keith.[/color]
>
> DAO.DBEngine(0) (0).Execute "ALTER TABLE " _
> & "[D:\My Documents\Acces s\db1.mdb].tbl2002Transac tions " _
> & "ALTER COLUMN fldDescription TEXT"
>
> Changes field type from binary to text.[/color]

Many thanks Lyle, I'll try that now.
:-)

[Allen Browne]

Re: Open another database and change a data type

Lyle, I had the impression that you could only use ALTER COLUMN in Jet 4,
and then not with DAO. Is that correct?

The workaround, Keith, would be to:
1. ALTER TABLE ... CREATE COLUMN
2. UPDATE ... (to copy the data into the new column)
3. ALTER TABLE ... DROP COLUMN

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lyle Fairfield" <MissingAddress @Invalid.Com> wrote in message
news:Xns943650C F463A9FFDBA@130 .133.1.4...[color=blue]
> Keith Wilby <keith.wilby@Aw ayWithYerCrap.c om> wrote in
> news:Xns94367C4 C9A8FBkeithwilb y@10.15.188.42:
>[color=green]
> > A97.
> >
> > I want to be able to use code to open change the data type of a field in[/color][/color]
a[color=blue][color=green]
> > table in another database - can this be done?
> >
> > Many thanks.
> > Keith.[/color]
>
> DAO.DBEngine(0) (0).Execute "ALTER TABLE " _
> & "[D:\My Documents\Acces s\db1.mdb].tbl2002Transac tions " _
> & "ALTER COLUMN fldDescription TEXT"
>
> Changes field type from binary to text.[/color]

[Keith Wilby]

Re: Open another database and change a data type

Lyle Fairfield <MissingAddress @Invalid.Com> wrote:
[color=blue][color=green]
>> I want to be able to use code to open change the data type of a field
>> in a table in another database - can this be done?
>>
>> Many thanks.
>> Keith.[/color]
>
> DAO.DBEngine(0) (0).Execute "ALTER TABLE " _
> & "[D:\My Documents\Acces s\db1.mdb].tbl2002Transac tions " _
> & "ALTER COLUMN fldDescription TEXT"[/color]

Hi Lyle, thanks for your response. It doesn't seem to like the second
'alter' clause and I can't find anything related in the help. Am I missing
something?

Regards,
Keith.

[Lyle Fairfield]

Re: Open another database and change a data type

"Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in news:3fb8cc0b$0 $1747
$5a62ac22@freen ews.iinet.net.a u:
[color=blue]
> Lyle, I had the impression that you could only use ALTER COLUMN in Jet 4,
> and then not with DAO. Is that correct?
>
> The workaround, Keith, would be to:
> 1. ALTER TABLE ... CREATE COLUMN
> 2. UPDATE ... (to copy the data into the new column)
> 3. ALTER TABLE ... DROP COLUMN[/color]

The posted code works on my work machine. I don't know about a requirement
for JET 4. At one time I tried to keep a machine as a pure Access 97
vetting system, and another for AC2K only. But I've stopped doing that and
I run only ACXP now. (How many years has XP been out?). I also download my
WindowsXP updates regularly and I believe these keep JET up to date.

How long will the regulars of this group continue to support Ac97? It's
many years old and it's many versions old. Questions and answers about it
take up space and time. Our supporting an archaic technology compromises
our credibility. Cost? C'MON!

I'd support splitting this group into two parts, one named as now,
Comp.Databases. MS-Access, and the other Comp.Databases. MS-Access.Archaic.
Persons using anything <2K could be directed to the latter.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Keith Wilby]

Re: Open another database and change a data type

Lyle Fairfield <MissingAddress @Invalid.Com> wrote:
[color=blue]
> How long will the regulars of this group continue to support Ac97? It's
> many years old and it's many versions old. Questions and answers about
> it take up space and time. Our supporting an archaic technology
> compromises our credibility. Cost? C'MON![/color]

Well, FWIW I have no control over the version I use, that's up to our IT
masters, sadly. I did specifically state at the top of my post "A97". You
could have ignored it if you'd wanted to ;-)

[Bruce M. Thompson]

Re: Open another database and change a data type

> How long will the regulars of this group continue to support Ac97? It's[color=blue]
> many years old and it's many versions old. Questions and answers about it
> take up space and time. Our supporting an archaic technology compromises
> our credibility. Cost? C'MON![/color]

I'll help support whatever version the users/developers are using, all the way
back to version 2.0 - both in the newsgroups and at client sites.

--
Bruce M. Thompson, Microsoft Access MVP
bthmpson@mvps.o rg (See the Access FAQ at http://www.mvps.org/access)[color=blue][color=green]
>> NO Email Please. Keep all communications[/color][/color]
within the newsgroups so that all might benefit.<<

[Larry Linson]

Re: Open another database and change a data type

"Lyle Fairfield" wrote
[color=blue]
> How long will the regulars of this group
> continue to support Ac97? It's many years
> old and it's many versions old. Questions
> and answers about it take up space and
> time. Our supporting an archaic technology
> compromises our credibility. Cost? C'MON![/color]

Ask the same about Access 1.0, 1.1, 1.0, 95, 2000, or (soon) 2002 and the
answer is the same: as long as people are still using them, still ask about
them, and someone still knows or remembers the answer.

Haven't you already wasted enough time and energy fighting the version
battle, Lyle? It's as fruitless as demanding that people put the version in
the Subject line (and it's difficult enough, even though it's posted in the
FAQ every day, to get people to summarize the _topic_ in the Subject).

Give Access 2003 a try -- I think you'll like it, except possibly the new
Help interface. Have a good day.

Larry

[Arno R]

Re: Open another database and change a data type

> I'll help support whatever version the users/developers are using, all the way[color=blue]
> back to version 2.0 - both in the newsgroups and at client sites.[/color]

That's the spirit Bruce!
I absolutely agree with you. I hope that a lot of us think this way.

Arno R

[Lyle Fairfield]

Re: Open another database and change a data type

"Bruce M. Thompson" <bthmpson@big_N OSPAM_foot.com> wrote in message news:<vrhs18qro 98q27@corp.supe rnews.com>...[color=blue][color=green]
> > How long will the regulars of this group continue to support Ac97? It's
> > many years old and it's many versions old. Questions and answers about it
> > take up space and time. Our supporting an archaic technology compromises
> > our credibility. Cost? C'MON![/color]
>
> I'll help support whatever version the users/developers are using, all the way
> back to version 2.0 - both in the newsgroups and at client sites.[/color]

I'm proud of you, Bruce. While I can't match your altruism I'll
continue to work towards the eradication of War, Poverty, STDs and
Donald Rumsfeld, in my own humble way.

[rkc]

Re: Open another database and change a data type


"Lyle Fairfield" <lylefair@yahoo .com> wrote in message
news:42d36dfd.0 311171039.2548b ac0@posting.goo gle.com...
[color=blue]
> I'm proud of you, Bruce. While I can't match your altruism I'll
> continue to work towards the eradication of War, Poverty, STDs and
> Donald Rumsfeld, in my own humble way.[/color]

Keep a sharp eye out for black helicopters in the night sky.