If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
"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!) |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 06:02 PM |
How do I reset the autonumber field back to 1 in a table when I a. | John Cassidy | General Discussion | 9 | November 23rd, 2004 08:05 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Adding auto-numbered field screws up Table order | Carl | Database Design | 5 | May 30th, 2004 03:25 AM |