I have a database that has just been split. In the back end, I don't see the tables. I have gone to Tools>Options>View and found that Show Hidden Objects is checked. When I go to the front end, the links to the tables show up and I can open them and see the data. When I check the Linked Table Manager, I see that the path for the links is leading to the back end database.
I also noticed that on the bottom of the screen, the buttons for the front end show the name of the front end database and have blue page-like icons, but the button for the back end has only the pink key icon. Also, when I try to link another mdb to these tables, the window for selecting the table for linking is empty. I also notice that suddenly, any time I close an Access database, it goes through a compact and repair cycle. This just started happening today. I'm fairly sure that yesterday I could see the tables in the back end.
Oh, yes. Just remembered one other thing. Not everyone here has that location on the network mapped to the same drive. In order to accomodate everyone, the linked table manager was set up with the complete path to the folder:
\companyname.comfolderlevel1folderlevel2folder level3...ackend.mdb instead of
J:.....
Using Windows XP and Office 2003
There is no verticle scroll bar as was mentioned in another post I found when doing a search on "Hidden Objects".
I'm designing a database that is used both in the office and field and it has two tables - an "office" table and a "field" table. The office table is a list of addresses, cities, and counties, each with a custom-designed key field. The field table contains equipment information at each location specified in the office table.
What I want to do is set up the database so that field personnel can copy an updated office table (with new locations added or old locations removed) into their databse and click something and have the tables "resync" with each other - the field table automatically adding new records to match those in the office table or delete records it has that it doesn't find in the office table...
I was wondering if there was a special character you entered into the column area if you have missing data.
For example,
I am trying to run some statistical analyses using a pivot chart. I want to make sure that every box in a specific column is filled. For some of rows I am missing numbers for some of there columns. If there is missing data do I put a wild card?
EXAMPLE:
First Last Middle Average
John Smith Adam 100
Smith John Peter (Blank) <--- no data
(Now if I were to find the average of both it wont run)
I have 2 tables: one for repairs and the other for the billing for those repairs. There is a foreign key(record_num) in the billing table to match the primary key(prikey) in the repairs table. This works fine as long as the unit repair has been completed.
Now an employee wants to see records even if they are not completed and wants the rate to be $0.00 if the unit has not been completed. But by this method there is no record in the billing table.
My problem is if I have the 2 tables joined then I only see records that match both tables. Here is my SQL for the query:
SELECT DISTINCTROW tbl_module_repairs.end_user, tbl_module_repairs.pickup_date, tbl_module_repairs.complete_date, IIf([pickup_entity]="Storm","APS Storm","APS Field Tech") AS [Repair Pickup], tbl_module_repairs.mfg_part_num, tbl_module_repairs.manufacturer, tbl_module_repairs.module_type, tbl_module_repairs.incoming_module_sn,
I have a table that stores information for multiple behavioral surveys (numerical values). My goal is to add the proper fields that compose total scores value for each respective survey (do a summation of scores). Now, under design view for my table, I see that I can add a calculated field. When I create this calculated field, I can use the Expression Builder to do a sum of the proper fields (the fields that compose a total score for a survey). The only problem that I'm encountering is that if a field that is part of a survey is missing information, the summation disregards the rest of the values for that survey.
How can I account for these missing values so that, if 1 out of my 9 fields have information, I will still get a summation score for the 9 fields? I want to be able to do this without having to change the value of the missing field to 0.
In SPSS I can easily do this by computing a variable and using a code like this: SUM.2(field1, field2, field3, etc.)
I have an access database with several linked tables (linked to MySQL database) and several local tables. The theory is that if there's ever a connection issue, the device connected to the computer will continue logging data to the local tables. Once a connection is re-established, the linked tables should be updated with all the missing records which appear on the local tables.
I found several possibilities which I outlined below, but I've been asked to investigate whether there's a built in function in access that does this for you and use the other options as a last resort. Does access have any program feature that updates one table with missing data from another table, or will I have to write VBA code to do that? Options I've discovered:
1) Write unmatched query and insert missing data into table.
2) Create a linked table on MySQL that will link to the local tables on access, then compare the records there.The boss isn't happy with those options because he wants to keep the amount of code we add to a minimum. Ultimately, we hope that a program feature that does this is built in to access. If not, I have no problem adding code to do this instead.
I've got an Excel sheet with +700k rows and 20 columns that I wanted to import to Access. All fields are text except the field that I want to use as a primary key, but I planned to import that as a text as well.
When I used the import wizard, I set all fields to import as text except for three that I set to memo. The wizard didn't say there was any error after importing the data, but when I checked the table, I noticed there were *a lot* of records where many fields where blank. Some fields where completely unaffected by this problem throughout the entire table, but in the rest of them, there is data missing in many records, and when there is data missing, it is not always the same fields that are missing. I have been unable to find any pattern that explains why sometimes the records were imported correctly, and why sometimes they were not.
This is an ongoing problem I have had for 4 weeks now.
I have made a a system thats acts like a clock In/clock out Out system.
the structure is somthing like this ID Username tblDailyLog TimeIn MorningBreakOut MorningBreakOut LunchOut LunchIn AfternoonOut AfternoonIn TimeOut
All fields apart from ID (autonumber) and username (String*255) are Date field (there are a few others like DateOfTimesheet etc but they arnt important here)
When a user arrives in the morning they make a record which they use for the day
They then have a form with a whole bunch of buttons which simply updates the correct field. For example they click the "Sign in for the Day" button and it updates the correct field with the current time.
Everything was going fine until people noticed that every now and again a sign in time dissapeared.
I have hacked myself to death trying to solve this problem but still the updates go Astray.
Now each time a time is updated the process goes somthing like this
1. the user opens their timesheet for the day (the RS is SNAPSHOT and no locks)
2. User Hits a sign in/out button 3. The record source is changed to "" and all buttons hidden (to ensure the record isnt locked and to make sure you dont do two things at once) 3. The table is updated with the new time (using some dynamic SQL) 4. The table is repeatadly checked using a DO loop to make sure the the correct time went in. 5. when the returned time value of the field matches the varaible used to update it, the form is returned to normal and the user carries on his/her merry way (if it never matches the screen should crash but this never happens). 6. A New record is added to another table called "tblbugfixinglog" which records which field was updated and when. This is so that I have two records in two different ways (figured if one went astray I could pull it back off the other) 7. Another new record is added to yet another table called tblSQLRecord, which simply logs all .RUNSQL statements that are executed.
I thought that the two extra tables (and the check that the record had been updated) would help me track down where the records are going missing, but this isnt the case.
Now it appears that some records arnt being added to tblBugFixingLog and to tblSQLRecord either and some of these tables are getting quite a few #ERROR's in them..
None of the tables are related to any other and i've no idea how #ERROR lines are appearing in a table that has 1 function... to recieve new records ... no editing, no viewing, no deleting.
Does anyone have any idea how these updates/inserts can go missing or create #ERRORs. I've built plenty of Databases in my time and have never come across this. __________________________________________________ ______________
This is the function I use to add a record to tblBugfixingLog and tblSQLRecord
Private Sub AddBugLog(ByVal TimesheetNumber As Long, ByVal FieldUpdating As String, ByVal NewFieldValue) Dim TempSQL As String TempSQL = "INSERT INTO tblBugFixingLog (TimeAndDateOfEntrySERVER,TimeAndDateOfEntryPC,Fie ldUpdated,NewEntry,UserID,TimesheetNumber,Computer AssetNo) VALUES (" & _ "#" & Format(ServerGetTime(Environ$("LOGONSERVER"))) & "#," & _ "#" & Now & "#," & _ "'" & FieldUpdating & "'," & _ "'" & NewFieldValue & "'," & _ "'" & GetNTUser & "'," & _ "'" & TimesheetNumber & "'," & _ "'" & fOSMachineName & "')" ' MsgBox TempSQL DoCmd.RunSQL "INSERT INTO tblSQLRecord (Username,DateAndTime,Screen,TheSQL) VALUES('" & LoginInfo.sUsername & "','" & CStr(Now) & "','Add Bug Log function','" & CleanData(TempSQL) & "')", False 'CleanData is a function that removes ' and " from the SQL string so i can easily add the SQL string into the table DoCmd.RunSQL TempSQL, False End Sub
Public Function CleanData(ByVal DataToClean As String) Dim TempData As String Dim i As Integer TempData = "" For i = 1 To Len(DataToClean) Select Case Mid(DataToClean, i, 1) Case "'" TempData = TempData & "`" Case """" TempData = TempData & "`" Case Else TempData = TempData & Mid(DataToClean, i, 1) End Select Next i CleanData = TempData End Function
When I run this Query without the WHERE statement/Clause- it returns the accurate 985 records. However, when I include the criteria with the WHERE clause (it takes a range of +/- 10% of the Square Footage size into consideration), I lose 15 records, and only get 970 results. When I increase the range to +/- 15% in that field, it gives me 7 more records back.
Obviously it is dropping out records that do not meet the criteria for that field- how do I prevent that? I guess I would like it to display a ZERO or something, but still display the entire 985 records. What do I need to include to do that? Here's the SQL-
SELECT tbAct.MLS, tbAct.Status, tbAct.Address, tbAct.City, tbAct.Area, tbAct.SqFt, tbAct.Level, Avg(tbSold.SoldPrice) AS AvgOfSoldPrice, Count(tbSold.SoldPrice) AS CountOfSoldPrice FROM tbListing AS tbAct, tbListing AS tbSold WHERE (((tbSold.SqFt) Between ([tbAct].[SqFt]+([tbAct].[SqFt]/100*10)) And ([tbAct].[SqFt]-([tbAct].[SqFt]/100*10)))) GROUP BY tbAct.MLS, tbAct.Status, tbAct.Address, tbAct.City, tbAct.Area, tbAct.SqFt, tbAct.Level, tbSold.Status, tbSold.Area, tbSold.Level HAVING (((tbAct.Status)="ACT") AND ((tbAct.City)="TEMPE") AND ((tbSold.Status)="CLOSD") AND ((tbSold.Area)=[tbAct].[Area]) AND ((tbSold.Level)=[tbAct].[Level]));
I hope someone can help me with this. I figure I'm missing one line of code but can't figure it out. I have 3 tables - tblGeneral (which is my main table), tblMDLink, and tblMD. The primary key in tbl General is FileNo. The primary key in tblMDLink is an autonumber and the primary key in tblMD is also an autonumber called MDID. tblMDLink is just a linking table containing the fields Fileno and MDID and uses an autonumber as its primary key. It contains some other fields that have nothing to do with this. I have a query called qryMDLink which contains all the fields of tblMDLink together with a calculated field calle full name which just combines the last name and first name fields of tblMD. Now I come to my form (which is actually a subfrom) called subfrmIMEDoctor. The purpose of the fomr is to allow users to add the names of various doctors to various files. The record source of the form is qryMDLink. The form also contains a FileNo field which is added programatically. The field has a drop down combo box with its record souce set to MDID. The drop down box has two columns with the first (not visible to the user) set to MDID and the second set to Full Name. Here's the problem - it won't let me enter data. Whenver I click on the box I get the message - "You cannot add or change a record because a related record is required in table tblIMEDoctor". By the way, I did set up one to many relationships beteen tblGeneral & tblIMELink and between tblIMEDoctor and tblIMELink. Any help would be greatly appresciated. Thanks, Tom Gorton
I have a relatively large database 65M, 42K records.
For some reason I seemed to have lost most (not all) of the queries I have written. However, if I go to file>database properties>contents, I can see all my queries there.
I certainly did not delete them. Any I idea of what’s going on ? I'm, using Acess 2000 on win2K. Might this be a resources / memory related issue?
I have build a database which work perfectly on most computers. But one or two for the computer are error with the Left, Right and Date SQL function. Is it simplely miss for the install or it a problem with my code?
Hi, when designing reports or forms, when I pressing the toolbox icon, it would depress, but no toolbox was visible. I tried resetting the toolbox (through view/toolbars) and I set all of the settings back to default. I even uninstalled and reinstalled MS Access several times but no luck. Any help please.
Recently I lost the the functionality of the toolbox then shortly after I lost the toolbox icon completely. The only way to use the features was to create an new toolbar. System Restore finally fixed the problem. This has happened twice on me with the 200 and 2003 versions. Is it me? and is there an easier way to sort this. Reinstalling Access never cured the problem either.
Hi, i've developed a program in access 2003, but now i'm on access 2000 and seems that one / more library is/are missing..
i've this part of code
Private Sub List0_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single) Dim strOrd As String, strSql As String If Button = 1 Then If Y <= 225 Then If Right$(List0.RowSource, 5) = " Asc;" Then strOrd = " Desc;" Else strOrd = " Asc;" End If strSql = "SELECT cliente.CLI_ID, cliente.CLI_TITOLO, cliente.Cognome, cliente.Nome, cliente.Indirizzo, cliente.Cap, cliente.[Citta'], cliente.Regione, cliente.Nazione, cliente.CLI_SOCIO FROM cliente WHERE (((cliente.CLI_SOCIO)=Yes)) ORDER BY " Select Case X
and this is the screen with the libraries currently installed ..
Alot of my databases entries are missing. today i went on MS access 2007 and it said "security risk" so i went to options and enabled marcros. but when i get on to my database there is only one entry! but i know the information is there. the file is 1700 mb. what should i do?
I have a weird situation and I was wondering if anyone has had an issue with this. I have a record missing from a table. Normal Users are not allowed to delete records from the Form view. There is a blank record where the record should be. The auto number counts from 37, 38, 40. Record 39 is missing and there are blanks where it should be. The autonumber field is my Primary Key and it is blank just like the rest of the fields in the record. I know that the record existed at one time because one of the DB users has a report referencing that record. Any Ideas?
There are two tables in my database. A and B. Each table has the same criteria: Name, Number, and Date.
I have created a formula in my query which will add the Number fields together when they both have the same name. My problem is that when there is not a row for a particular name on table B, the calculation does not list that row in the results.
I would like for my calculation to insert a zero in the space and add Number.
I have tried the Nz function, but this results in a missing name and date and just the numbers are shown.
How do I get the results to show a row where the name is only on one field and still make sure that it places a zero in its spot and add the zero and the other number together based on the name.
I have a sequencial number in my data base. It counts 1,2,3 all the way to 302344. I want to find missing records. How would I come up with the missing records in a separte database. In other words if I had 1,2,4,5,8,9. I would want to have a database that would show me 3,6,7 since they are missing.
Query Design View is only showing Tables pane (top). Fields pane (bottom) is missing from previously saved queries. New queries show both panes as usual. Does any have any idea why? I cannot find any settings to show the bottom pane (or to hide it). Please help
