Doug,
I AM using user-level security. So which SYSTEM.MDW file should I be
using? I am using the one from C:\Documents and Settings\Dr. Anthony
Diecidue\Application Data\Microsoft\Access. Is that the correct one?
Could
it be corrupt? This code used to work on my old computer.
The code is pretty long, but here is a representative sample
strSyst = Text2.Text
DBEngine.SystemDB = strSyst
Dim db As Database
Dim ctrLoop As Container
Dim con As Container
Dim doc As Document
Dim tdfTableDef As TableDef
Dim fldTemp As Field
Dim Response
On Error GoTo errOut
Response = MsgBox("Be sure to exit EyeBase if it is running before
applying this upgrade.", vbOKCancel, "EyeBase Upgrade")
Select Case Response
Case Is = 1
Set db = OpenDatabase(strDB) ', False, False,
";UID=ncc;PWD=")
Set con = db.Containers("Tables")
Set doc = con.Documents("Contact Lens Rx")
doc.UserName = "Admin"
doc.Permissions = dbSecFullAccess
db.Execute "ALTER TABLE [Contact Lens Rx] ADD
COLUMN
radd Double;"
db.Execute "ALTER TABLE [Contact Lens Rx] ADD
COLUMN
ladd Double;"
db.Execute "ALTER TABLE [Contact Lens Rx] ADD
COLUMN
rnearva Text;"
db.Execute "ALTER TABLE [Contact Lens Rx] ADD
COLUMN
lnearva Text;"
db.Execute "ALTER TABLE [Contact Lens Rx] ADD
COLUMN
rlenscond Text;"
db.Execute "ALTER TABLE [Contact Lens Rx] ADD
COLUMN
llenscond Text;"
db.Execute "ALTER TABLE [Contact Lens Rx] ADD
COLUMN
rbitoricparam Text;"
db.Execute "ALTER TABLE [Contact Lens Rx] ADD
COLUMN
lbitoricparam Text;"
doc.UserName = "Admin"
doc.Permissions = dbSecInsertData Or dbSecRetrieveData
Or dbSecReplaceData Or dbSecDeleteData
db.Close
Douglas J. Steele said:
WIthout seeing your code, it's pretty hard for me to make any
suggestions.
However, it's unlikely you'd need to alter the SYSTEM.MDW, unless you've
applied User-Level Security to your database (and if you've done that,
then
you wouldn't be able to do anything to the database if you're referencing
the wrong MDW file)
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Tony said:
I just realized that NONE of my code is working. Its weird because if I
have
it report errors its telling me that the COLUMNS I am trying to ADD are
already in the table even though they are not. Its not giving me an
error
at
all when I try to ALTER the COLUMN, but it doesn't do any thing to the
table.
I don't know if this means anything, but this is a new computer and I
recently installed Access 2003 and VB6 on it. And this is the first
time
I'm
trying to run this code on this computer. Does the SYSTEM.MDW have a
big
part in this? Do I need to alter that file somehow?
:
Doug,
Actually, I just checked my VB6 references and I have DAO 3.6. I also
looked at my code again and I actually had a line in the code just
like
you
had given me with the ALTER COLUMN statement. I noticed that it is
not
working either.
Any other thoughts on this?
:
Hold on. It just occurred to me that you wouldn't be able to use
Access
2003
with DAO 3.5, so you must have DAO 3.6 picked.
What's the exact code you tried?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
message
Sorry, I don't have VB6 installed on this machine, but there
definitely is
a way to check what references you've set for the program.
Worst case, if you can't find the menu option, open the project
file
in
Notepad: the names of the references will be at the beginning of
the
file.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Doug,
I am using Access 2003 and VB6. I don't know how to check DAO
versions.
-Tony-
:
What version of DAO do you have referenced in your VB program?
I just tested using Access 2003, and it worked fine for me, so I
know
the
DDL is correct. However, it didn't work in Access 97, which
implies
that
DAO
3.5 may not be sufficient.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Doug,
Thanks for the reply. While I did not get an error message,
your
code
below
using ALTER COLUMN did not change the data type. Here is the
full
code
I'm
using; why is it not changing the data type:
Set doc = con.Documents("SpecialTesting")
doc.UserName = "Admin"
doc.Permissions = dbSecFullAccess
db.Execute "ALTER TABLE [SpecialTesting]
ALTER
COLUMN
Field1 MEMO;"
doc.UserName = "Admin"
doc.Permissions = dbSecInsertData Or
dbSecRetrieveData
Or dbSecReplaceData Or dbSecDeleteData
:
Using DAO, as you are in your sample code, the only option is
to
add
a
new
field of the correct type, run an update query to copy the
data
from
the
existing field to the new field, delete the old field, then
rename
the
new
field.
However, you can use DDL to make the change:
db.Execute "ALTER TABLE SpecialTesting ALTER COLUMN Field1
MEMO",
dbFailOnError
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
I want to change the data type of certain fields in certain
tables
using
VB6
code. I currently use code like this to modify some of the
field
attributes:
Set tdfTableDef =
db.TableDefs("SpecialTesting")
Set fldTemp = tdfTableDef!Field1
But I want to CHANGE the data type from TEXT to MEMO and
this
doesn't
seem
to be working for that. I would appreciate any help with
this.
-Tony-
