Adding New Field to Table and to Form

[Luvflt]

I'm sorry I am not very well versed in MS Access but I do know enough to muddle my way though things. I have inherited a data base used by my employer. The original creator is long gone. I am using Access 2010 running off of a sql server. In the data base is a table called "Student" which contain all the necessary info on our students. This data base uses a form "frm_new_student_sql_new to enter student data upon the student registering and to look up info on existing students. I have been asked to add a field to this form to capture additional information. This field will be a check box (true/false) called "WIA".

Maybe a little backwards but I went into the form and created the check box with the label WIA. I noticed the check box was on the form but greyed out. I assume since there was no reference to it in the student table. I went to add the filed to the student table but was unable. There was nothing there to let me add a field. I also could not view the table in Design Mode.

I did some research on this and came up with the idea that the table may be linked and that I have to find the original source and change that. Here is the only information I could come up with.



At one time the student data base was so large that the original designer made a new one. In fact there are two student tables now. 1) called "Student" and one called "Student Old" When I called up the form "frm_new_student_sql_new" and looked at the properties I saw the Record Source was "sp_students_after_06302003" When I looked up this query I noticed in design view under the student registration date (Reg_Date) the criteria was >'6/30/2005'. I also happen to see another guery called sp_students_after_12312002. When I looked up this query in design view under Reg_Date was the criteria >'6/30/2003'.

Now the sp_students_after_12312002 in design view shows the "student" table selected with "all columns" checked and only the Reg_Date and the criteria listed as explained above. In the sp_students_after_06302003 query in design view it too shows the "Student" table but has all the fields checked and in the box below all the fields are listed as well with the Reg_Date having the above mentioned criteria. In both queries the output box is checked for all fields.

Additionally I did an advanced toggle filter search using the "Student' Table on a student that was listed in the "Student Old" data base and that student appeared. This leads me to believe that the query is used to located student from the older student data base or so I assume. What throws me here too is the criteria in the sp_students_after_06302003 query shows > 6/30/05 and the one for sp_students_after_12312002 show 6/30/2003.

So I am assuming the Student table is linked by the query. Having said that how do I add the field to the student data base? What am I missing? I am totally stumped at the moment so any info would be greatly appreciated.

[June7]

Does sound like a split database. If tables are linked, the frontend will show a little arrow icon next to each table. Hover cursor over the table listing and view the file path. Go there and open the backend file of tables.

[Luvflt]

Does sound like a split database. If tables are linked, the frontend will show a little arrow icon next to each table. Hover cursor over the table listing and view the file path. Go there and open the backend file of tables.

I read that during my research but when the cursor hovers over the table name (and I am assuming that you are speaking of the table name in the list under tables at the left) it does not show any path nor is there any arrow icon next to the table. The only thing I noticed was in the Student table there was a + sign next to each record whereas in the Student Old table there was no + at the beginning of each record. so although it does appear there is a link somehow I can't back track and have no way of knowing where the original table is located. I have full access to everything in the data base on my computer so it isn't like I was not given permission I am the administrator of the data base (for all that is worth)!! Still perplexed?? I would guess that the Student table is the name of the original student informational table. There may have been data broken away from that that table in my estimation should be the root table.

[June7]

The + indicates there is a linked dependent table. Clicking the + will expand to view the related records.

If there are no arrows then db is not split and is an all-in-one.

Are you not able to click on table and select Design View from the ribbon? Or right click Design View?

If you want to provide db for analysis, follow instructions at bottom of my post.

[Luvflt]

Ok, first I did click on the + sign to the left of each record in the student table. This brought up a dialog box that said Insert Subdatasheet and that had three tabs one for tables, queries and one for both. At the bottom was a space for "Link Child Fields" and a space for Link Master Fields with drop down arrows but nothing was in there.


Next if I am in the Student table or the Student Old table I can go to design view but it is all grey and I don't see anything there. There are no sliders so I know it isn't like off the page. There is nothing that allows me to add a field in either table. Can't insert and can't add at the end.

The file extension for the data base itself is .adp

As for providing copy of db to debug I will have to work on that. It isn't easily done since there is so much confidential info. but thank you.

[June7]

I have never worked with .adp.

Ooops! You said SQL Server? I am really confused. If the tables are actually in SQL Server then what are the tables you see in the Access file?

I am also not familiar with SQL databases.

You might try importing everything into a new .accdb file.

http://www.office-forums.com/convert...b-t473731.html

[Luvflt]

Ok well just to report back...After much research I have determined that there is only on "Student" table. The query in the student table is just so the people that input data about students don't have to deal with 8K worth of records when inputting. It is just to speed up the process. Yet, if it were necessary to get info from much older records they would have to go directly to the "Student" table. Having said that I found that I can not add or modify any fields in any table nor can I make a new table. For some reason those aspects are greyed out. I thought it might be a permission thing but our contract IT person was unable to figure that out. I did go into our sql server and was able to located the data base and the tables there and eventually was able to add a field using the actual server. So in essence I was able to accomplish my goal the long way.

I am still interested in knowing why I can't create or modify a table without doing it from the sql server. If anyone has any ideas please let me know.

[June7]

The database is split. Manually modifying tables must be done directly in the backend file.

If the frontend were an mdb or accdb file then tables could be created in the frontend. Since this is an adp file, I guess that is not possible.

[Luvflt]

Ok thanks for the info.