I have an MS Access database. It contains tables linked to tables on SQL Server. Therefore MS Access is really only acting as a front end to the SQL Server database.
I converted the MS Access database to a .mde file.
I noticed over the past few days that the .mde file is growing in size. I don't understand why this is the case. The data is being stored in SQL Server and the database only contains links to the tables so I wouldn't have expected the size of the .mde file to change.
Does anyone know why the .mde file is growing? Is there anyway I can stop this of growing?
I have a problems which seems simple, but it's made me doubt everything I have learned about this; I suddenly find myself unable to grasp what I belive is a very small and easy solution, but I cannot!! (Either that or I have stumbled upon a real conseptual problem without realising it :) )
This is what I had working nicely
Supplier supplyer_ID : ID and PK of a supplyer more boring attributes
Ingredient ingredient_ID: ID and PK of ingredient Name Supplyer_ID: FK to who the supplyer is
Recipe Recipe_ID: PK and ID Recipe_unit_weight: How big each unit should be other stuff
When making a recipe it is obvious that there can be many ingredients and that an ingredient can be used in many recipes, thus it's a many-many. I therefore created a junction table:
Recipe_Ingredients Recipe_Ingredient_ID: PK (I was not completely sure if this one would come in handy, but it has not so far caused any problems (that I can see) Recipe_ID: FK to which recipe it is for ingredient_ID: FK to which ingredient Receipe_ingredient_amount: How much of the ingredient Receipe_Ingredient_order: In which order to put into recipe
I had a nice form where the Recipe was the parent and the subform contained the ingredients. The subform was driven by a query that had the columns from the Recipe_Ingredient table plus the name of the supplier as well as the version of the recipe (as there might be many). Using a combo to get the name of the Ingredient and binding on the Ingredient_ID I didn't need to also bring in the name of the Ingedient in the query.
Now enter problem: For professional people this lacks one thing; Being able to use another Recipe as an ingredient. After a lot of thinking the solution I envisioned was;
Add a column in the Recipe to flag that is a base recipe (true/false). Then adding a new column to the Recipe_Ingredient table, namely Base_ID, which is the Recipe_ID for which recipe to use. As there is already a Recipe_ID referring to which the ingredient belongs, I needed a different name. Additionally I had to change the relationships in the subform query to be left-joins (always include all columns in the Recipe_Ingredient and join in whatever you can find in the other two.
This works for diplay purposes (when I hand edit the join table). The problems I get is when adding a new recipe as ingredient in the subform;
When adding a base (recipe) at the point of saving the record my Recipe table validation kicks in saying you need to have Recipe_unit_weight > 0!!! I am not planning on adding any recipes here. This does however not always happen, as another error also kicks in: Microsoft Jet database engine cannot find a record in the table 'Supplier' with the key matching field(s) ''. As it's a recipe there is no supplier and I'm assuming it is trying to find a supplier.
If I strip away the extra columns from the query powering the subquey (like supplier and base (recipe) version) it works like a charm. In effect it means that the query only consists of the junction table.
It seems that having a two-way junction table with left-join with both "parents" is causing problems. Am I doing something fundamentally wrong here or is it Access that don't get the picture (and how do I tell it).
Okay,This will probably sound confusing, but I'm going to try my best to hone down the issue.First:There is an imported DBF table that I have to perform calculations on, primarily of the string variety, but also with a few type conversions (such as: String "06" = CDate("1/1/" & "06") -> into a Date/Time field)Okay, given that, I was doing many things all at once, but found by doing it in several stages allowed me the ability to: Narrow down the actual amount of items in the DBF that I will import Speed up some of the calculation in the TransferSo I have two tables:tbl_OriginalTape_ID: Text(10) - {Primary}Title: Text(50) - {Primary}Title_Rem: Text(50)Category: Text(3)TitleLen: NumberActionQuery on Tbl_Original: INSERT INTO tbl_Original (tape_id, title, title_rem, category, TitleLen) SELECT qry_dbf.tape_id, qry_dbf.title, qry_dbf.title_rem, qry_dbf.category, Len(RTrim([qry_dbr.title])) as TitleLen) FROM qry_dbf;tbl_CalcTitlesTapeID: Text(10)MovieTitle: Text(100)AltTitle: Text(100)Remarks: Text(50)Paren: Text(20)Bracket: Text(30)Orig_Id: Text(10)OrigTitle: Text(50)Now to take the data populated into tbl_Original, there are many rules that I have to follow. Example: if Original.Title = {text}>>{text} the the left of the double ">" signs, is the MovieTitle, to the Right is the Alt Title. If in the MovieTitle there is a (*) {parenthetical} the parenthetical is removed and the text insde the () is instered into Paren. Similar rules apply for Bracket, just with [] square brackets instead of Parentheses (). Also, if the Trimmed Title Length from the Original insert =40 (the maximum for the DBF field, AND the Original Category field = "SEE" then the title_rem field is appended to the AltTitle field of tbl_CalcTitles, and the the tbl_CalcTitles.Remarks field is set to "" (emtpy string), otherwise the title_rem field is populated into tbl_CalcTitles.Remarks. Also for attempted relationships for later use, I directly transfer the tbl_Original.title and tape_id fields to the Orig_Id and OrigTitle fields of tbl_CalcTitles.Yes, This is very annoying, and i've spent some time writing all the little funcitons to do exactly this, and be accurate every time I run my action query. (It is pointless to post here, since it involves way too much vb back end code to post it all.)qry_dbf: 33099 recordstbl_Original: 33098 recordstbl_CalcTitles: 33098 recordsNow, given this setup, we know that in my insert into tbl_Original, 33098 records of qry_dbf were unique in the paired tape_id and title fields. So now I want to link qry_DBF to the tbl_CalcTitles, so that I get exactly 33098 records from qry_dbf that match up witht he Orig_id and OrigTitle in tbl_CalcTitles. Yet, no matter what i do, I do not get this result. My last attempt was a LEFT JOIN from tbl_Original to qry_DBF and I got 33138 records returns. How did grow extra records. if I ONLY have 33098 in tbl_Original, and 33099 in qry_dbf, where are the 40 extra coming from?So I tried to link tbl_CalcFields to qry_dbf on an INNER join on the same fields, and now, this is purely mind-boggling:Each Time I alter the number of fields DISPLAYED from qry_dbf, the total number of records return alters. Example:tbl_CalcTitles.TapeID, tbl_CalcTitles.MovieTitle, qry_dbf.Title: 33092 recordstbl_CalcTitles.TapeID, tbl_CalcTitles.MovieTitle, qry_dbf.Title, qry_dbf.category, qry_dbf.format: 33072 recordsNow this is completely beyond what one would expect. I already know that there are 33098 unique records in qry_dbf, on the pairing of fields, Tape_ID and Title, of with all 33098 records are contained within tbl_CalcTitles with the EXACT replica of those two fields. so If I am doing an Inner Joing of tbl_CalcTitles and qry_Dbf on those two fields which produced 33098 unique records, why would altering the number of DISPLAYED fields affect that outcome. Why am I not getting all 33098 records from qry_dbf that are contained in tbl_calcFields?Thanks a lot, this has been extermely nerve-wracking.Jaeden "Sifo Dyas" al'Raec RuinerPS - qry_DBF is SQL source defined as:SELECT tap001.*FROM tap001 IN 'tap001.dbf'[ODBC;DSN=Visual FoxPro Database;SourceType=DBF;SourceDB=D:Quarterly]ORDER BY tap001.tape_id, tap001.title;
Hello Everyone, Why does my database grow so large during the development stage? After compacting the database it is 64 megs, but only after a small number of changes it grows to 500 plus and more if I don’t re-compact it.
Hi, i am new to access. Anyone know why my .mdb file size Growing. Even when i delete the data from table and done with the compact and repair. i am doing my first small project with one table and 10 querys and 5 repots. each time i edit the query or report the file the size goes on increasing. i have not more then 100 records in my database. i only edit my records when ever i edit my records .mdb file size grows so any one could help me to make it constant. plz help
I have a DB that that is growing slowly. To keep it simple I have 2 tables and one form. One of the tables is a “ASSET_lookup” table. The other table (ASSETS) is the real meat and potatoes. I have a form for the assets table that will populate specific fields that are looked up based on a single combo box linked to a query from the assets_lookup table. Everything worked fine until I needed to add some additional items to the asset_lookup table.
The new “records” I added to the lookup table will not show up in the combo box after it updates (a selection is made). I did some experimenting and if I recreate after adding the new records to look up table, the combo box will show the value, but I am curious what I am missing.
I have a strange issue. First off I have a front end in Access 2000 as a MDE file linked to a mdb data file.
Issue number 1: The MDE file almost doubles in size daily. Yes, the mde file not mdb (it grows slowly and expectedly as data is added and deleted) . I expected the MDE file to stay a static size. Is this normal, what causes this?
Issue number 2: I had on 3 occasions a query in the mde file suddenly pop up with a ”cannot repeat table name ‘Nameoftable’ in FROM clause”. I go into the mde file, SQL query view and delete the extra table name and all is fine for awhile. After the second time I did away with that quey and rewrote the query for my form a different way. It’s come back once again now for anther table. Has anyone heard of Access doing this?
I also note that the modified date of the file keeps changing. I am wondering if someone is tampering. My client that has this software is very naďve.
The MS site has no information regarding either behaviour.
I have seven (sub) reports put on to a large report. How can I get the reports to shrink or grow according to how much info they have in them. I have tried Detail - Can Shrink, Can grow - but this doesn't seem to work. At the moment I have them all lined up but they are all overlapping each other.
I have a report with several forms, these are all graphs.
In order to fit them all on one report, I need them to be able to grow.
This works fine in Report View, they grow as expected, however when exporting to PDF, printing or print preview, they do not grow. I set them all to Yes for Can Grow and it displays correctly in Report View.
Hi Alli, I have a database that has a comments field. The database has a status field: Plan Test and Run, in the comments field the user adds comments, i'm trying to updated the comments with the status, for example; the user added some comments like. "The test will begin next week" I want the comments feild to automatically get updated when the user changes the status for example to Run, I want the "run status to be added to end of the comments line" can somebody help? Thanks.
So here's my problem. I'm trying to update a listbox in excel with values that are being pushed in a table in access. The problem is that there are repeated values in the column in access, but I only want to show each value once, regardless of how many times it appears in the table. I would give a code sample but I'm having trouble getting started...
I have a report, and the "reason" field is one that can grow as needed. The following string is in the field: "MISSING IMPLEMENTATION PLAN, MISSING SERVICE LOGS FOR DATE OF SERVICE". I bolded MISSING, where the space hits the border. My standard setting for the border style is Transparent, and the space is causing the growing to not work properly. TransparentBorder.pdf shows the incorrect growing. When I change the border style to Solid, the growing works correctly, as shown in the SolidBorder.pdf file.why the growing is not working properly with the Transparent border, but does work correctly with the Solid border?
On our site we have option where users can add coments to articles. Lately some people have been misbehaving. I wish to add a field in .mdb file and call it "approved". If the field contains "1" then it's ok to show the message, if the field has nothing in it comment will not be shown. How do I implement this into below code. I wish to leave posting as is, just not showing not yet approved comments.
Thank you in advance. (comments are shown in <td>" & rs("Comment") & "</td> part)
Here is the code:
' show past comments If Not rs.EOF Then
s = s & " <tr bgcolor=""" & TitleBGColor & """>" & vbCrLf s = s & " <td colspan=""3""> Comments:</td>" & vbCrLf s = s & " </tr>" & vbCrLf
While not rs.EOF s = s & " <tr class=""" & TitleClass & """>" & vbCrLf s = s & " <td> </td>" & vbCrLf s = s & " <td>From:</td>" & vbCrLf s = s & " <td>" & rs("Name") & " (" & rs("DateAdded") & ")</td>" & vbCrLf s = s & " </tr>" & vbCrLf s = s & " <tr>" & vbCrLf s = s & " <tr class=""" & TitleClass & """>" & vbCrLf s = s & " <td> </td>" & vbCrLf s = s & " <td>Comment:</td>" & vbCrLf s = s & " <td>" & rs("Comment") & "</td>" & vbCrLf s = s & " </tr>" & vbCrLf s = s & " <tr>" & vbCrLf s = s & " <td> </td>" & vbCrLf s = s & " <td> </td>" & vbCrLf s = s & " <td> </td>" & vbCrLf s = s & " </tr>" & vbCrLf rs.MoveNext Wend End If
We have several users that access the same database. Is there a way to make it so that the users can add comments to the "coments" field on the form, but not remove any comments once saved?
I have a comments on my form. I have contacts with them having from 1 to many customers accts. I am trying to come up with a way to update the comments for a contact that adds those comments to all their customers records...but not to those that have been closed. How would I add these comments to the comments on the form without overwriting the current comments.
Comments - Memo Contact - Text
It's quite a pain, copying and pasting from record to record.
Firstly, I would apologise as I am not overly familiar with the correct terminology!
I need to create a "space" on my form whereby I can add comments etc which will exceed 255 characters: these comments will be specific to each record. I have tried having an "unbound" text box in design view but this makes the comments the same on each record.
I know this is probably "as clear as mud" but if anyone can help I would be really grateful.
I am developing a new tool which needs to show the history of updates to the users, i.e.:
An observation is entered into a field on the MAIN FORM by user 1.
User 2 reviews the observation on the MAIN FORM and adds a separate comment in another field on the same form.
When the record is saved, the updates are NOT written to the Main Form Table but to the UPDATES TABLE for audit purposes.
I am also using current date/ time as a timestamp, which is protected and needs to be carried over to the UPDATES TABLE Every time a new comment is added in the MAIN FORM, I need it to be written to the UPDATES TABLE as a new record.
What I need to be able to show is:
Each comment added, as a list of readable records, in a table within the main form AND Each time the main form is refreshed/ opened the separate comment field is blank to enable each new update to be applied.
Hopefully there's someone who can help me with this!!! I've had several unsuccesfull attempts and don't know what else to try...
We're trying to force users to make entries in a field called txtComments in the frmQuestionnaire form if they provide negative responses and to stay on that record until the comments are changed. Inside the form is an option group (fraResponses) which contains options (OptA, OptB..etc.) valued from 1 through 7, based on a likert 7 scale. We're only using a likert 6 scale, so the respondents will only see 2-7 on the form. If they options B-D (valued 2-4), they are considered negative, so we want to make sure they enter a comment.
I tried to enter the italicized code under the form on BeforeUpdate, AfterUpdate, CmdUp (click to next question) and CmdDown (click to previous question), with frmQuestionnaire but nothing happens:
There is a module called clsUser which contains the following and where I tried to insert the code:
Set myForm = Forms!frmQuestionnaire ' ================================================== == ' First, set the value of the variables ' ================================================== == lngQuestion = arrQ(lngArray, 0) lngSession = GetCustomInfo("TestSession") lngUser = UserID lngBillet = BilletID
strComment = Nz(myForm.txtComment, "")
strComment = "None"
'If user selects negative responses If Form_frmQuestionnaire.fraResponses.OptionValue = 2 Or 3 Or 4 And strComment = "None" Then MsgBox "Please explain the problems you encountered with the system which " & _ "caused you to select an unfavorable response." Form_frmQuestionnaire.txtComment.SetFocus
End If
lngResponse = myForm.fraResponses
When I place the code there, the dialog box appears for all values and goes to the next record. This is what appears for the CmdUp (click to next question):
If cUser.blnDirty = False And Me.fraResponses = 153 (this is default value for the entire option group) And cUser.blnNew = True Then cUser.blnDirty = True End If
cUser.CaptureAnswer If cUser.lngArray < cUser.UBound_ArrQ() Then cUser.lngArray = cUser.lngArray + 1 Else cUser.lngArray = cUser.UBound_ArrQ() End If cUser.FillQuestions cUser.blnDirty = False
I've tried to enter the line of code with the form and the module, but no luck.
I also want it to remain on the same record until the field is changed. The form is unbound and has the following:
GetResponseSet lngRG FillAnswers If lngArray = 0 Then myForm.txtComment.SetFocus myForm.cmdDown.Enabled = False Else myForm.cmdDown.Enabled = True End If
If lngArray >= UBound(arrQ) Then myForm.txtComment.SetFocus myForm.cmdUp.Enabled = False Else myForm.cmdUp.Enabled = True End If End Sub
Public Sub FillAnswers() '************************************************* **** ' Purpose: Fill out the user's previous responses ' ' Assumptions: N/A ' ' Effects: N/A ' ' Inputs: ' None ' ' Returns: ' None '************************************************* ****
Dim strSQL As String Dim recAnswer As New ADODB.Recordset
If Not recAnswer.EOF Then myForm.fraResponses = recAnswer!reDatResponseSetID myForm.txtComment = recAnswer!reComment blnNew = False Else If myForm.fraResponses <> 152 Then myForm.fraResponses = 153 myForm.txtComment = "" blnNew = True End If End If
How to extract the comment system from the Contacts Web Database template found in Access 2010? I can upload it if you like.
I would like to use it in a standard desktop database to track notes and comments in my records.
I am able to convert it from web to standard, but it still appears to use data macros or something, and my knowledge on macros is limited.
I just want to keep a history of my notes for each record, with the most recent always listed, and the comment system in the Contacts Web Database template seems perfect for that. I just don't know how to implement it into my desktop database.
