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]

Keith Wilby <ke*********@AwayWithYerCrap.com> wrote in
news:Xn************************@10.15.188.42:

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.

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

Changes field type from binary to text.

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

[Keith Wilby]

Lyle Fairfield <Mi************@Invalid.Com> wrote:

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.

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

Changes field type from binary to text.

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

[Allen Browne]

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" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...

Keith Wilby <ke*********@AwayWithYerCrap.com> wrote in
news:Xn************************@10.15.188.42:

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.

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

Changes field type from binary to text.

[Keith Wilby]

Lyle Fairfield <Mi************@Invalid.Com> wrote:

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.

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

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]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in news:3fb8cc0b$0$1747
$5*******@freenews.iinet.net.au:

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

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]

Lyle Fairfield <Mi************@Invalid.Com> wrote:

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!

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]

> 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'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
bt******@mvps.org (See the Access FAQ at http://www.mvps.org/access)

NO Email Please. Keep all communications

within the newsgroups so that all might benefit.<<

[Larry Linson]

"Lyle Fairfield" wrote

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!

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]

> 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.

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

Arno R

[Lyle Fairfield]

"Bruce M. Thompson" <bthmpson@big_NOSPAM_foot.com> wrote in message news:<vr************@corp.supernews.com>...

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'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.

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]

"Lyle Fairfield" <ly******@yahoo.com> wrote in message
news:42**************************@posting.google.c om...

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.

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