add field to back-end existing table

How do I add field to a back-end table that already has existing data in it?
Basically I am trying to add another field to make the record inacvtive so
that you will not have to see it anymore.

James

The same way you'd add a field to any other Access table...

Open the .mdb file. Open the table in design mode. Add a new field.

P.S. You cannot do this "from the front-end".

--
Good luck

Jeff Boyce
Access MVP

"James" wrote in message
...
How do I add field to a back-end table that already has existing data in
it?
Basically I am trying to add another field to make the record inacvtive so
that you will not have to see it anymore.

James wrote:

How do I add field to a back-end table that already has existing data in
it?
Basically I am trying to add another field to make the record inacvtive so
that you will not have to see it anymore.

James, Access allows you to modify the table structure even if there is data
in it. Just open the back-end database, open the table in design mode and
create the new field. If you are trying to create a checkbox field, the
default value for that column will be unchecked (false). Since you have
pre-existent records, you may want to initialize the new field value for
those, something you can accomplish by using an Update Query on that table.

Note: Creating new field with the Required attribute turned-on, requires
that you create the field with the attribute turned-off (in the case of
tables already with records). Then initialize the column using an Update
Query, and then turn-on the Required attribute.

Remember, that to see the new field also in the front-end, you will need to
re-link linked tables (if used), add the new field to forms, reports, etc.
Since you are talking about "not having to see inactive records anymore" I
imply that you will have to make changes in your query or record sources
properties of your forms, reports and/or modules. Something like "SELECT *
FROM [My Table] WHERE Inactive = FALSE."

-Randy

Thank you Randy,

Is there a way to do this withour me actually going to each site. There are
5 different locations that use a standalone network, and I would like to send
them a file or a batch file to add the new fields automatically. Is that
possible?

"Randy" wrote:

James wrote:

How do I add field to a back-end table that already has existing data in
it?
Basically I am trying to add another field to make the record inacvtive so
that you will not have to see it anymore.

James, Access allows you to modify the table structure even if there is data
in it. Just open the back-end database, open the table in design mode and
create the new field. If you are trying to create a checkbox field, the
default value for that column will be unchecked (false). Since you have
pre-existent records, you may want to initialize the new field value for
those, something you can accomplish by using an Update Query on that table.

Note: Creating new field with the Required attribute turned-on, requires
that you create the field with the attribute turned-off (in the case of
tables already with records). Then initialize the column using an Update
Query, and then turn-on the Required attribute.

Remember, that to see the new field also in the front-end, you will need to
re-link linked tables (if used), add the new field to forms, reports, etc.
Since you are talking about "not having to see inactive records anymore" I
imply that you will have to make changes in your query or record sources
properties of your forms, reports and/or modules. Something like "SELECT *
FROM [My Table] WHERE Inactive = FALSE."

-Randy

James wrote:

Thank you Randy,

Is there a way to do this withour me actually going to each site. There
are
5 different locations that use a standalone network, and I would like to
send
them a file or a batch file to add the new fields automatically. Is that
possible?

"Randy" wrote:

James wrote:

How do I add field to a back-end table that already has existing data
in
it?
Basically I am trying to add another field to make the record inacvtive
so
that you will not have to see it anymore.

James, Access allows you to modify the table structure even if there is
data
in it. Just open the back-end database, open the table in design mode and
create the new field. If you are trying to create a checkbox field, the
default value for that column will be unchecked (false). Since you have
pre-existent records, you may want to initialize the new field value for
those, something you can accomplish by using an Update Query on that
table.

Note: Creating new field with the Required attribute turned-on, requires
that you create the field with the attribute turned-off (in the case of
tables already with records). Then initialize the column using an Update
Query, and then turn-on the Required attribute.

Remember, that to see the new field also in the front-end, you will need
to
re-link linked tables (if used), add the new field to forms, reports,
etc.
Since you are talking about "not having to see inactive records anymore"
I
imply that you will have to make changes in your query or record sources
properties of your forms, reports and/or modules. Something like "SELECT
*
FROM [My Table] WHERE Inactive = FALSE."

-Randy

The ideal scenario is that all users have shortcuts to a Front-End Database
Program that resides in a network. And a Back-End database located in the
same folder than the Front-End. That way you only have to update at one
place and everybody is all set.

If the users are in separate LANs then they are using their own copies of
the Front-End and/or Back-End on their computers. In that case you will need
to update such copies (every one of them). If you only need to update the
Front-End just replace the old with the new one. But if they also have a
Back-End, you will need to create a script (as you are asking). However
scripts are not too easy to develop and they will work without error only if
your development scenario is very consistent, something not achieved by 99%
of Access solutions writers. Those kind of scripts or upgrades are left for
companies who release updates to a global community. Not saying that you
cannot do it, but that it may involved a lot of work and time, perhaps not
valuable as per the few times you might be using it, or the roll that the
actual database plays. But since you asked and the lack of details about
your project here it is some guidelines.

The problem here is that the users have a table (with existent records) and
you need to modify its structure. Also that you will want to deal with the
modifications of this field on objects like Queries and Forms.

There are two ways to deal with this: 1) is to create an update script (as
you said). Or 2) to create a brand new database (with no records), with an
option that when executed the first time, asks the user to import the
old-version's data. I'll follow number 1, which is a script. To do this
create another database. This database is to be sent via email or diskette
by interoffice mail. Name the database with a meaningful name as
"Upgrade5.mdb." In that database include the new version of all the forms
and queries that have been affected/redesigned because of the new field
change. Also add new forms and queries if any. Then create a new form. In
that form place a label and type the upgrade statement, identifying the name
of the program and its purpose: "Ticket Control Database version 5 Upgrade"
(I picked that name as an example, but you should also mention a version
number). Create another label with red-color saying "Only for upgrade from
Version 4." Put another label specifying the author name, release date,
company, etc.

Place a button on the form and label it "Update Now". On the click event of
this button you will need code to verify the existence of the current
"Ticket Control Database" and its location. Then you will need to make sure
the database is not currently open. If not notify the user and abort. If the
"Ticket Control Database" is not being used, then get its version (which
should come up with "4"). You should abort the upgrade if the version is not
suitable for this upgrade (In this case the update is only design to upgrade
to version 5 from 4).

The rest of the code will be as: 1) Display a message telling the user to
wait until getting a confirmation that the update has been completed. 2)
Once the database has been located, lock it, by opening it Exclusively,
until the duration of the upgrade. 3) Going through upgrade process you
should modify the table adding the new field. 4) Copy and overwrite with the
new version of your other objects (forms, queries and reports) to the new
database. 5) Close and release the database. 6) Notify the user the update
process was completed successfully. 7) Close the upgrade program
automatically.

You would want the form to come up automatically when the user opens the
upgrade file. To do this save and close the form and go to Tools-Startup of
the menu. Select the form's name from the "Display Form" drop-down menu and
click OK.

Below most of the code you will need. Place it on the code-window of the
upgrade form:

'-----------------------BEGIN CODE------------------------------

Dim db As DAO.Database 'Ticket Control Database handle
Dim dbname as string 'Ticket Control Pathfilename

'This opens and locks the database---------------------------------
Sub OpenAndLockDatabase()
Const EXCLUSIVEMODE As Boolean = True

'was file specified?
IF (nz(dbname, "") = "") Then
MsgBox "No database file name has been specified.", vbCritical
Exit Sub
END IF

'does filename is valid or does file exist?
On Error Resume next
IF (Dir(dbname, vbArchive) = "") Then
MsgBox "The database file cannot be found.", vbCritical
Exit Sub
END IF
IF err.number 0 then
MsgBox "The database file name is invalid.", vbCritical
Exit Sub
Exit Function

'open file exclusively
On Error Resume Next
Set db = DAO.OpenDatabase(dbname, EXCLUSIVEMODE)

IF err.number 0 then
Select Case err.number
Case 3045, 3196, 3006, 3356
MsgBox "The target database is in used " & _
"and cannot be locked.", vbCritical
Case 3049, 3343, 3182
MsgBox "The target file is damaged or is not " & _
"a valid database file.", vbCritical
Case 3051
MsgBox "The database cannot be locked. " & _
"It is either read-only or you need " & _
"access permission.", vbCritical
Case Else
Err.Raise Err.Number, Err.Source, Err.Description
End Select
Exit Sub
END IF
End Sub

'This releases the database----------------------------------
Sub ReleaseDatabase()
db.close
set db = nothing
End Sub

'This adds the "Inactive" field to the database------------------
Sub AddField()
Dim tdf As DAO.TableDef
Dim fld1 As DAO.Field
Dim prp1 As DAO.Property

Set tdf = dbs.TableDefs("Tablename to Modify")

'Creates new field data type
Set fld1 = tdf.CreateField("Inactive", dbBoolean)
tdf.Fields.Append fld1
dbs.TableDefs.Refresh

'Creates the checkbox attribute
Set prp1 = fld1.CreateProperty("DisplayControl", dbInteger, acCheckBox)
fld1.Properties.Append prp1
dbs.TableDefs.Refresh

Set dbs = Nothing
End Sub

'This copies the new versions of objects to the target database-------------
Sub CopyNewVersionOfObjects( )
DoCmd.SetWarnings (False) 'Disable file overwriting question

'Replace Forms
DoCmd.CopyObject dbname, "Form1", acForm, "Form1"
DoCmd.CopyObject dbname, "Form2", acForm, "Form2"
DoCmd.CopyObject dbname, "Form3", acForm, "Form3"

'Replace Queries
DoCmd.CopyObject dbname, "Query1", acQuery, "Query1"
DoCmd.CopyObject dbname, "Query2", acQuery, "Query2"
DoCmd.CopyObject dbname, "Query3", acQuery, "Query3"

'Add New Forms (if any)
DoCmd.CopyObject dbname, "Form4", acForm, "Form4"

'Add New Queries (if any)
DoCmd.CopyObject dbname, "Query4", acQuery, "Query4"

DoCmd.SetWarnings (True)
End sub

'This closes this update-program automatecaly----------------------------
Sub CloseOut( )
DoCmd.Quit acQuitSaveNone
End Sub

'-----------------------END CODE------------------------------

-Randy

"Randy" wrote in message
...
James wrote:

The ideal scenario is that all users have shortcuts to a Front-End
Database
Program that resides in a network. And a Back-End database located in the
same folder than the Front-End. That way you only have to update at one
place and everybody is all set.

No it's not.

Each user should have his/her own physical copy of the front-end, preferably
on his/her hard drive. You're increasing the risk of corruption
exponentially if you have everyone sharing the same physical front-end
database.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Thank you, That is what I was looking for! I will repost if there are any
problems, but I should be able to go from here.

"Randy" wrote:

James wrote:

Thank you Randy,

Is there a way to do this withour me actually going to each site. There
are
5 different locations that use a standalone network, and I would like to
send
them a file or a batch file to add the new fields automatically. Is that
possible?

"Randy" wrote:

James wrote:

How do I add field to a back-end table that already has existing data
in
it?
Basically I am trying to add another field to make the record inacvtive
so
that you will not have to see it anymore.

James, Access allows you to modify the table structure even if there is
data
in it. Just open the back-end database, open the table in design mode and
create the new field. If you are trying to create a checkbox field, the
default value for that column will be unchecked (false). Since you have
pre-existent records, you may want to initialize the new field value for
those, something you can accomplish by using an Update Query on that
table.

Note: Creating new field with the Required attribute turned-on, requires
that you create the field with the attribute turned-off (in the case of
tables already with records). Then initialize the column using an Update
Query, and then turn-on the Required attribute.

Remember, that to see the new field also in the front-end, you will need
to
re-link linked tables (if used), add the new field to forms, reports,
etc.
Since you are talking about "not having to see inactive records anymore"
I
imply that you will have to make changes in your query or record sources
properties of your forms, reports and/or modules. Something like "SELECT
*
FROM [My Table] WHERE Inactive = FALSE."

-Randy

The ideal scenario is that all users have shortcuts to a Front-End Database
Program that resides in a network. And a Back-End database located in the
same folder than the Front-End. That way you only have to update at one
place and everybody is all set.

If the users are in separate LANs then they are using their own copies of
the Front-End and/or Back-End on their computers. In that case you will need
to update such copies (every one of them). If you only need to update the
Front-End just replace the old with the new one. But if they also have a
Back-End, you will need to create a script (as you are asking). However
scripts are not too easy to develop and they will work without error only if
your development scenario is very consistent, something not achieved by 99%
of Access solutions writers. Those kind of scripts or upgrades are left for
companies who release updates to a global community. Not saying that you
cannot do it, but that it may involved a lot of work and time, perhaps not
valuable as per the few times you might be using it, or the roll that the
actual database plays. But since you asked and the lack of details about
your project here it is some guidelines.

The problem here is that the users have a table (with existent records) and
you need to modify its structure. Also that you will want to deal with the
modifications of this field on objects like Queries and Forms.

There are two ways to deal with this: 1) is to create an update script (as
you said). Or 2) to create a brand new database (with no records), with an
option that when executed the first time, asks the user to import the
old-version's data. I'll follow number 1, which is a script. To do this
create another database. This database is to be sent via email or diskette
by interoffice mail. Name the database with a meaningful name as
"Upgrade5.mdb." In that database include the new version of all the forms
and queries that have been affected/redesigned because of the new field
change. Also add new forms and queries if any. Then create a new form. In
that form place a label and type the upgrade statement, identifying the name
of the program and its purpose: "Ticket Control Database version 5 Upgrade"
(I picked that name as an example, but you should also mention a version
number). Create another label with red-color saying "Only for upgrade from
Version 4." Put another label specifying the author name, release date,
company, etc.

Place a button on the form and label it "Update Now". On the click event of
this button you will need code to verify the existence of the current
"Ticket Control Database" and its location. Then you will need to make sure
the database is not currently open. If not notify the user and abort. If the
"Ticket Control Database" is not being used, then get its version (which
should come up with "4"). You should abort the upgrade if the version is not
suitable for this upgrade (In this case the update is only design to upgrade
to version 5 from 4).

The rest of the code will be as: 1) Display a message telling the user to
wait until getting a confirmation that the update has been completed. 2)
Once the database has been located, lock it, by opening it Exclusively,
until the duration of the upgrade. 3) Going through upgrade process you
should modify the table adding the new field. 4) Copy and overwrite with the
new version of your other objects (forms, queries and reports) to the new
database. 5) Close and release the database. 6) Notify the user the update
process was completed successfully. 7) Close the upgrade program
automatically.

You would want the form to come up automatically when the user opens the
upgrade file. To do this save and close the form and go to Tools-Startup of
the menu. Select the form's name from the "Display Form" drop-down menu and
click OK.

Below most of the code you will need. Place it on the code-window of the
upgrade form:

'-----------------------BEGIN CODE------------------------------

Dim db As DAO.Database 'Ticket Control Database handle
Dim dbname as string 'Ticket Control Pathfilename

'This opens and locks the database---------------------------------
Sub OpenAndLockDatabase()
Const EXCLUSIVEMODE As Boolean = True

'was file specified?
IF (nz(dbname, "") = "") Then
MsgBox "No database file name has been specified.", vbCritical
Exit Sub
END IF

'does filename is valid or does file exist?
On Error Resume next
IF (Dir(dbname, vbArchive) = "") Then
MsgBox "The database file cannot be found.", vbCritical
Exit Sub
END IF
IF err.number 0 then
MsgBox "The database file name is invalid.", vbCritical
Exit Sub
Exit Function

'open file exclusively
On Error Resume Next
Set db = DAO.OpenDatabase(dbname, EXCLUSIVEMODE)

IF err.number 0 then
Select Case err.number
Case 3045, 3196, 3006, 3356
MsgBox "The target database is in used " & _
"and cannot be locked.", vbCritical
Case 3049, 3343, 3182
MsgBox "The target file is damaged or is not " & _
"a valid database file.", vbCritical
Case 3051
MsgBox "The database cannot be locked. " & _
"It is either read-only or you need " & _
"access permission.", vbCritical
Case Else
Err.Raise Err.Number, Err.Source, Err.Description
End Select
Exit Sub
END IF
End Sub

'This releases the database----------------------------------
Sub ReleaseDatabase()
db.close
set db = nothing
End Sub

'This adds the "Inactive" field to the database------------------
Sub AddField()
Dim tdf As DAO.TableDef
Dim fld1 As DAO.Field
Dim prp1 As DAO.Property

Set tdf = dbs.TableDefs("Tablename to Modify")

'Creates new field data type
Set fld1 = tdf.CreateField("Inactive", dbBoolean)
tdf.Fields.Append fld1
dbs.TableDefs.Refresh

'Creates the checkbox attribute
Set prp1 = fld1.CreateProperty("DisplayControl", dbInteger, acCheckBox)
fld1.Properties.Append prp1
dbs.TableDefs.Refresh

Set dbs = Nothing
End Sub

'This copies the new versions of objects to the target database-------------
Sub CopyNewVersionOfObjects( )
DoCmd.SetWarnings (False) 'Disable file overwriting question

'Replace Forms
DoCmd.CopyObject dbname, "Form1", acForm, "Form1"
DoCmd.CopyObject dbname, "Form2", acForm, "Form2"
DoCmd.CopyObject dbname, "Form3", acForm, "Form3"

'Replace Queries
DoCmd.CopyObject dbname, "Query1", acQuery, "Query1"
DoCmd.CopyObject dbname, "Query2", acQuery, "Query2"
DoCmd.CopyObject dbname, "Query3", acQuery, "Query3"

'Add New Forms (if any)
DoCmd.CopyObject dbname, "Form4", acForm, "Form4"

'Add New Queries (if any)
DoCmd.CopyObject dbname, "Query4", acQuery, "Query4"

DoCmd.SetWarnings (True)
End sub

'This closes this update-program automatecaly----------------------------
Sub CloseOut( )
DoCmd.Quit acQuitSaveNone
End Sub

'-----------------------END CODE------------------------------

-Randy

Douglas J. Steele wrote:

"Randy" wrote in message
...
James wrote:

The ideal scenario is that all users have shortcuts to a Front-End
Database
Program that resides in a network. And a Back-End database located in the
same folder than the Front-End. That way you only have to update at one
place and everybody is all set.

No it's not.

Each user should have his/her own physical copy of the front-end,
preferably
on his/her hard drive. You're increasing the risk of corruption
exponentially if you have everyone sharing the same physical front-end
database.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Hi Doug, I know... I was referring to the scenario where to avoid new
application roll-outs (which is what James wanted to avoid). But yes, to
have each user with its on copy of the front-end is has always been the way
to go. Unfortunately he wants a deployment without releasing new front-end
copies. Thanks for clarifying though.

Regards,

-Randy