I am currently unable to delete any data from my table. When I highlight a row and right-click, the option to delete the record is grayed-out. I have deleted and recreated the table and relationships, saved the database as a new file, and compacted and repaired, but I am still unable to delete anything.
View RepliesI created a table to keep contact information such as address, phone number, and email address of instructors. I also modified the New Instructor Entry form to add an email address, since we usually have this when hired.
The problem arises when no such information exists for an instructor. I have attached a "sample" database with all the important components and "sample" data.
When I choose an instructor in which some contact information exists, the Instructor Contact Entry form performs as expected. On the other hand, if no information for an instructor exists, the information can be entered, even though the Employee Number, Family Name and Given Name fields are blank, contrary to the first case. Not only can the information can be "saved" but no data appears in the table.
How do I have to set this up to get it to work for an instructor not in the table?
I have a Union query that is linked into about 10 different excel spreadsheets on our drive and it pulls back only 3 columns of data (Document Number, Date Registered and Date Implemented). I then have another report which refers to the results of this query and does its own thing from thereon.
Here is a sample of the SQL:
SELECT CINT(RIGHT(RT_01000.[Rev-Trac Number],5)) as [Document Number], RT_01000.[CC_RT Issue Date] as [Date Registered], RT_01000.[Actual Delivery Date] as [Date Implemented] FROM RT_01000;
UNION SELECT CINT(RIGHT(CC_RT_01000.[Rev-Trac Number],5)) as [Document Number], CC_RT_01000.[CC_RT Issue Date] as [Date Registered], CC_RT_01000.[Actual Delivery Date] as [Date Implemented] FROM CC_RT_01000;
My Issue:
As it is above, the query will run, but we end up with duplicate numbers in the "Document Number". It only brings back the final 5 digits from each of these files and ignores the differenciating part of the number, the "CC_RT_" or the "RT_". This causes huge issues in the file which then goes onto use these results afterwards.
My Plan: (failed)
I thought I could simply update the "5" to an "11" to catch anything named "RT_00001" and also "CC_RT_00001" serperately so I can clearly see which is which without any duplicates. However, I recieve the below error when doing so:
"Data type mismatch in criteria expression".
I've been playing with this for hours and unfortunately many many reports reply on these results being correct. It is also not possible to change the names of the RT and CC_RT files that it links into.
Hi can anyone help. My database is running on a Citrix server. No real problems except that I am continualy being left with the database apparantly locked by another user. Am unable to delete the LDB file even when no other users are logged on. Is there a way of deleting LDB files in relation to stale Access sessions. I know that in normal circumstances these files close automatically. Problems seems to be occurring when the database is not exited properly or the connection to the server drops for some reason. I've tried loads but can't find a workaround.
Stuart Dennis
I have tried to rename or delete in MS Explorer, but this just creates confusion. The databases still show up in bits and pieses on various screens. I've purchased 2 additional how to do it books on MS Access 2007 and find nothing on deleting or renaming an entire database and all objects or references to it. Does anyone know the magic bullet?:confused:
View 3 Replies View RelatedI've been testing my scripts so my database has a whole bunch of bogus records in it. I'd like to simply delete everything out of my orders table (there aren't any legit ones in there yet) so that I can start over when I go live and not have all that crap in there.
Is there an easy way to do this without re-creating a blank database and copying/pasting table only over?
My goal is to get a huge amount of data paired down so that I can work with it in Excel.
I started with a 14 million record text file provided by a government agency. I used a text editor to break that into 7 2-million record files to fit into Access.
I figure I can filter each of those files down to 500,000 or so records, so that I can manipulate them in Excel. The problem is, when I open a text file in Access, Access insists on linking it, and then I can't delete data from a linked file.
Well I have two tables, one is the workorder register and the other is the closed work orders. This is for maintenance. I have a form for creating and veiwing workorders. I need to link a command button to the form that will delete the record being viewed in the WO REG table and paste it in the Closed WO table. I think I need to use two append queries but have no idea what I am doing. Here is what I have so far.
INSERT INTO [CLOSED WO] ( WO, MMCN, TECH, NOMIN, FUALTS, TYPE, [SECTION], CLOSEDATE, OPENDATE )
SELECT [WO REG].[WO], [WO REG].[MMCN], [WO REG].[TECH], [WO REG].[NOMIN], [WO REG].[FUALTS], [WO REG].[TYPE], [WO REG].[SECTION], [WO REG].[CLOSEDATE], [WO REG].[OPENDATE]
FROM [CLOSED WO], [EQUIP REG] INNER JOIN [WO REG] ON [EQUIP REG].[MMCN]=[WO REG].[MMCN]
WHERE ((([WO REG].[WO])=[WHAT WO TO CLOSE?]));
Like I said I have no idea what I am doing, this is out of the SQL view for my "move to closed WO" query. the fields I need to move are WO, MMCN, TECH, NOMIN, FUALTS, TYPE, SECTION, CLOSEDATE, and OPENDATE.
Is this even remotly right? I know nothing of VBA an all these symbols mean nothing to me.
How to Delete data from only one table from a Join? OR How to set the ADO recordset unique table property?
On Access 2010 module I have a class that manipulate data (save, read, edit and delete) from this statement:
Code:
Private Const strNomeTblFonte as string = _
"SELECT ER.*, ET.intTipoExame, ET.txtNomeExame, FROM tblExamesTipos
ET INNER JOIN tblClientesExamesRequisitados ER ON ET.idExamesTipos = ER.intQualExame;"
Private Sub Class_Initialize()
Set mCol = New Collection
[Code] ....
Problem: I need to delete data from only one table (tblClientesExamesRequisitados) of a inner join, but only delete from the "wrong" (tblExamesTipos) table.
After going to msdn on title: Unique Table, Unique Schema, Unique Catalog Properties-Dynamic (ADO) I attempted to address the problem with this line in the Class_Initialize():
Code:
Recordset.Properties("Unique Table").Value = "tblClientesExamesRequisitados"
But only generates this: Run Time Error 3265 - Item not found in this collection...
I know, if I open another Rst and use a Distinctrow, or open only one table, as in "DELETE * FROM tblClientesExamesRequisitados WHERE intQualExame = " & miQualExame & ";" it will resolve, but, why "Unique Table" isn't functioning?
Query4 is the result from left joining of Query3 and Query1 and I would like to delete some of the null data in query 4
while I execute the following code
DELETE DISTINCTROW Query4.*, [working hour] AS Expr1
FROM Query4
WHERE (((query4.[working hour])=0));
I get the warning of "Could not delete from specific table"
Access 2010. Can vba code be written to delete and insert data from a table in one routine.
Something like Delete * Insert into select from where order by....
I tried it with a query but have to write two.
I have a database that needs data to be reentered every school term, at the moment i am having to delete selected data fields manually. im looking to create a query so that the data is deleted by running it. The data would be returned to a blank field. I have tried using a delete query but it is asking for the selected table, even though a selected table exists. Using the update query i am faced with updating the query to a typed word however i just want it blank.
View 1 Replies View RelatedI have a table called "EquipmentRequired" which is populated by 4 append queries,
5000BaseReq, 6000BaseReq, 6000IFBBReq, EquipmentReq which get some of their information by counting fields in another table but all have the same field names.
The queries contain all data that is initially used to append new records to the table and this works fine.Unless some information changes or a record is added then I would like to add a button to a form and call it "update equipment" behind which would run a vba code firstly to delete all the records in "EquipmentRequired" table then run the 4 queries without the warnings and re-populate the table.
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
Hi, I'm trying to connect to SQL Server Express from MS Access 2003.
I've created an ODBC data source and tested it within the ODBC Wizard. It successfully connects to the SQL Server database.
From within MS Access, I choose File > Get External Data > Import and I'm presented with a dialogue for choosing the data source. As soon as I choose ODBC from the Files of Type drop-list, the dialogue disappears. No error-message, nothing!
Has anyone else had this problem?
Hi,
I just created another query, but with this one I cannot change any of the data. What could be the reason?
Thanks
I am working with two forms frmManager and frmMMeasures where based on selection from the combo box of frmManager of the department, position and staff member frmMMeasures is loaded where MUserLoginID and MPositionName is populated based on selection from frmManager. This is what I use to populate the MUserLoginID field for the control source " =[Forms]![frmManagers]![cboStaff] ".
My problem is when I go to the next record on the form those two fields do not change from what was picked up from the form frmManager.
I don't know how to ensure that the correct data is displayed.
it is zip file unable to get the size lower yet minimal amount in database I have 2 tables and one form when I create an query on the form the form then becomes that I cannot enter data in it or edit etc
View 3 Replies View RelatedI am trying to get a form to display certain information based on the data in another box. I have to input some exam results and want to assign a level to them. So, if they score < 13, the result would read "Below Entry 3" if between 13 and 21 "Entry 3" etc If this was a spreadsheet I would use an if function, but I am not sure that I can do this here
View 14 Replies View RelatedI import/export data from MS Excel. Access will not allow me to use the Browse function to select the directory and/or file to work with. I do not get an error message, just nothing happens.
View 3 Replies View RelatedI have a database that has been working properly for months...however, suddenly I am unable to add records to a number of my tables.
Each of 10 tables are related to a main table with a 1 to 1 relationship - the relationships have not changed.
Can anyone think of any table setting which I may have inadvertantly changed that would prevent additional records from being added?
I am still able to add to the main table and I have added several new tables also with a 1 to 1 relationship with the main table that are all working fine.
Thanks!
We are using access 97 and I have a linked table to sql 2000. A couple of weeks ago, our sql server crashed and we had to completely rebuild it / restore all of the data. Ever since then, only one of our linked sql tables in access is acting up. Whenever we go to edit a line (sometimes, not always), we get an error which states the following:
This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made. Copying the changes to the clipboard will let you look at the values the other user entered, and then paste your changes back in if you decide to make changes.
Options are Save Record (greyed out), Copy to Clipboard, and Drop Changes.
About all I have tried is re-linking the table...any other ideas?
I have been using my db for 3 years without this particular problem: I am now unable to add records to one of my tables - either through the form or in datasheet view. I have a patient table and a visit table. I put in the patient data using a form and then go to the visit form to add all of the visits for the month. I have never had a problem adding data to any of my tables but now I am unable to add data to the visit table. As much as I didn't want to I brought up last month's back up to enter the patients again. Before I did I checked to see if I could add visits - I could not. So I checked January's back up - same issue. I made no changes to the db before trying to add a visit .
View 10 Replies View RelatedI had an existing database with 2329 records entered into it. All of the fields (220ish) were all in one table. Myself and my co-workers wanted to rebuild the database without losing the data. We wrote queries to transfer the data from the original database to the new database and split the data from the original 1 giant table to 9 smaller tables.
The transfer of data worked so I went to start making forms. When I went to add fields from different tables I had to built a relationship, which I did. All of the data that transferred over from the new database is in the form (now multiple forms linked by button) but I can't add new information. I get an error stating, "You cannot add or change a record because a related record is required in table ..." and the table referenced keeps changing.
Hey
I have a procedure that imports excel spreadseet to access table ever today.
today it failed. Forrmat was the same as yesterday.
I did 'copy/paste special/format' from yesterday's file; still today's file wouldn't import.
Tried to copy manually to the table, got the error message about 'field not being in the same format'
Went to table's design view, and changed everything to 'memo', the was able to do copy/paste.
There wer no values with more then 20 characters
I was able to change 'memo' back to text and date/time without loosing any date.
Has anyone ecountered this ? Going changing table, and then chaging it back doesn't sound like the best practice
what can be done ?
can abything be done to excel file ?
I'm using Access 2007.
I have a button on a form that when clicked, does the following:
I have a table called [Workorder Parts] that has 128 records in it with Fields named WorkOrderPartID (Autonumber), WorkorderID(Number), PartID(Number), Quantity(Number), UnitPrice(Currency), Notes(Memo), KitID(Number).
I want to copy records into another table called [tKitsWorkorderParts] that has the identical structure based upon a value in the field WorkorderID. For testing purposes let's assume that the field contains the value "12". There are 28 records in the table that have the value set to "12"
If I hardcode the value "12" into the following SQL statement, it finds and copies the 28 records correctly.
DoCmd.RunSQL "INSERT INTO [tKitsWorkOrderParts] SELECT * FROM [WorkOrder Parts] WHERE [WorkOrder Parts.WorkOrderID] = 12"
so far so good.....
Now, on the form there is a Text Box called WorkorderID that contains the value "12".
If I change the code to the following, I get all 128 records instead of just the 28 I am expecting:
DoCmd.RunSQL "INSERT INTO [tKitsWorkOrderParts] SELECT * FROM [WorkOrder Parts] WHERE [WorkOrder Parts.WorkOrderID] = WorkorderID"
I have inserted a few MsgBox displays to display the value contained in WorkorderID and it shows "12"...
What am I doing wrong???