Hi
I'm just doing an access form to link to a mysql daabase to update a websites products, using a odbc connection.
When i input a new record and go back to it i see only #Deleted in all fields i have to close the database and open again to see the data correctly.
Is there a way to cure this problem?
Cheers
Neil
I have developed a Microsoft Access 2010 database for my client and the database is split with Front-end/Back-end, the Back-end and the database is shared on Network, The client operating system and applications for all users are hosted and consistent and the service is delivered over Citrix.
The database some times corrupt the tables record and give a permanent #Delete Error, I have attached one of the database table and the screenshot of the error,
Hello,
I am an Oracle DBA with little to no Access experience so please be kind. :D
As of yesterday a frequent user of Access 2003 (11.8166.8172) SP3 reported that all columns in all rows have the value #Deleted in a number of tables linked back to an Oracle database.
I am not experiencing this problem but I have Access 2007. It is not an option for this user to upgrade.
I've discovered several issues over the web with data type issues/ characterset issues but nothing seems to fix the problem. I've tried the Oracle supplied 11g and 10g ODBC drivers. I've tried the Microsoft supplied ODBC for Oracle drivers.
None of it seems to work and I am out of ideas. Can anyone suggest anything?
The problem wasn't noticed until yesterday so it can't have been happening for long. The problem is occuring in Oracle 10.2.0.3 and 10.2.0.1 databases. There has been no change in the Oracle environment.
There was a windows update recently but we tried uninstalling that and it didn't help.
any ideas?
Hi All,
This is my first post here and I imagine there will be many more as I have only just started out using Access to create a database at work and I am sure that I'm breaking many rules along the way.
Anyway, over the weekend, my boss tried to add some information (to the "Memo" field) in a form that I have created from a table, went to close the form and got a message saying something along the lines of "this record cannot be saved, if you close it, information will be deleted.
Now, in the Memo field, is the text "#Deleted" - when I try to overwrite this and come out of the record, it comes up with the message "record is deleted". If I put information in any other field in the record, there is no problem.
Also, in the table, if I try to sort by the Memo field, it comes up with same message, "record is deleted".
Does anyone know what I've done and how I can solve this?
With thanks in advance.
Ronnie
By: Tony Hine (mail@TonyHine.co.UK)
Tel: +44 1635 522233
My profile on ecademy (http://www.ecademy.com/user/tonyhine)
I had a problem importing just one table I kept getting the message “Record is deleted” and no records were imported.
First I thought it may be because there were quite a few columns in the table so I tried importing just one column, however I got the same message “Record is deleted“ .
I considered it could be because the database was originally in a Company office workgroup. I wondered if the “Work group security” was causing the problem. I read up on this and found that importing the objects directly into a new database was one of the recommended ways of circumventing this problem. This was what I was already doing! So what was wrong?
Next idea, I exported all of the records into an excel spreadsheet. This was successful! So I then re-imported the records from the spreadsheet back into a table in the database.
There were a couple of problems, in that all of the text fields were converted to 255 character length. Although annoying I thought I could handle this problem. I proceeded making a few changes to this database, then I noticed that one of the fields that had been imported was originally a “Memo field” it had been converted to a text field with 255 characters. As soon as I noticed, I checked to see if any data had been lost by comparing this field with the original data. I found a significant loss of data so back to the drawing board!
All of the other tables had imported correctly, only this one seemed to be giving problems, I had tackled the size problem it was not a big table by MS Access standards, but I felt it could be the size, In particular the number of fields could be causing this error. But just trying to import one field at a time hadn’t worked?
Then it dawned on me what if I tried to import one record at a time --- that worked! So I wrote the query with a between statement I tried 10, then a 100, then a thousand records it worked fine! However when I tried 5000 records the error re-occurred?
I realized that I probably had some corrupt data in the table, so I set about systematically extracting a limited number of records until I found the area where the corruption was.
This worked but returned “0” records --- Between 11001 And 12000
“Between 12001 And 13000” Got error message “Record is deleted”
I got to 12800 all ok
12900 caused error
Error is in a record between 12800 and 12900
I will continue to whittle it down!
OK to 12819
The corrupt record is: 12820
I had found the corrupt record! Now all I had to do was create two queries one each side of this corrupt record to extract the information.
On inspection of this corrupt record I discovered that the memo field contained the following: “#Deleted “ I am going to attempt to modify the memo field and see if it fixes the corruption of the database.
My attempt at modifying the corrupt record, in particular the memo field containing “#Deleted” caused the error message to re-occur. “Record is deleted”
I have made a copy of the actual record 12820 and pasted it into a spreadsheet to send to the customer so that it can be checked in the existing data on their system.
Fortunately there were no related records in other tables affected.
We developed a simple database that operates across a network at work (max 15 intermittent users). We split it into a front and backend and made it a .accde file format of which through a desktop short cut we all access. We are recently getting the error message Record is Deleted.
View 12 Replies View RelatedI have just split my multi-user DB into Front end and back end. All appears fine apart from there is code in the autoexec module that checks to see if a particular table has been updated today. Basically the first person to login to the DB each day will run this procedure without realising. Now I have FE & BE however, because the query used by the autoexec module is a "Make table" query rather than delete all data and use an append query, every person logging in is running the update each day if it is the first time they have logged in on their PC and they are getting a local table on each of their computers. Is there any code that will create the table in the networked location (when the first person of the day logs in), rather than me having to change all my "delete table" queries to cleardown data and then re-append updated data?
There are quite a lot of delete queries in my DB and I am now worried that I shouldn't have split the DB as I may have caused myself loads of work which I don't have time to do.
I have looked on other threads but cannot find the answer and would greatly appreciate some help
Is it possible to change the message that will be displayed when someone enters data that doesn't meet the criteria of an input mask?
Thx,
Lion85heart
ok.. i need to add an input textbox to my message alert..
i have this right now..
Option Compare Database
Private Sub Form_Open(Cancel As Integer)
MsgBox ("Enter the date:")
End Sub
i also need the value they enter to be saved as 'formdate'
then i will assign my date field to that value using something like
datefield = formdate
My problem started when upon running a macro that runs a report, the message "Record is Deleted" was returned and thus the report did not run. This is very strange because it worked ten minutes prior. Upon further research, I found that the table that is queried for this report could not be opened and upon attempting to open it the message "Record is Deleted" was returned again. I am, however, able to view all of the records in my table through a form. I cannot seem to find a reason for the table to work one place and not another. Any insight that could be provided would be greatly appreciated.
Thank you.
I want to have an input mask on an 'Expiry Data' Field so that the input method is 'MON-YY" and I need access to realise it as a data. And then I also need when a user opens a record an anything that is 2 weeks from expiring I need an error message to pop up.
View 1 Replies View RelatedI am trying to change the standard input mask violation error message to a personalised one. I have found this code:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const INPUTMASK_VIOLATION = 2279
If DataErr = INPUTMASK_VIOLATION Then
MsgBox "There was an input mask violation in the field no!!"
Response = acDataErrContinue
End If
End Sub
However, i would like to change the message for a number of different text boxes. and i don't know how to isolate each one, and give each one a different message?
I have a database with a number of linked tables that are linked to tables in different databases (not a back-end).for example, I have table1 that is linked to table1 in K:databasedb1.mdb.table2 linked to table2 in S:datadata.mdb.and so on...
However, recently we have moved all our databases to a new location.
K:databasedb1.mdb is now residing in O:masterdatabase
and S:datadata.mdb is now residing in O:masterdata
and so on...
I'm now in charge of relinking all those tables to point to the new location.I would do this in linked table manager one by one but we have 100s of tables linked to multiple different databases in different location.is there a way to create a VBA code that will automatically do this re-linking process?
so,
1. find unlinkable tables
2. search its new location under O:master
3. re-link it to the new location
Database names and tables names have not been changed. Just the location of databases.
My db includes two tables, Employees and Users, both with a pk of EmpNum (Autonumber in Employees; Number in Users). Users is a subset of Employees, and in the Users table, the row source for EmpNum is a query of the Employees table. I had a relationship defined with a one-to-one between the two tables, but did not have Enforce Referential Integrity or Cascade selected.
The problem is, I deleted a record out of Users, but it also automatically deleted the related Employees record. Re-creating the employee was quite the task because all related records in several other tables also disappeared. I experimented by deleting the relationship between the two tables and then deleting another user, but it still deleted the related employee. How can this be? The two tables are no longer linked, other than the Users EmpNum lookup.
Hi
I have a muli-user database that's been creating the odd strange record for me
two records that have been created have all the fields data as
#ERROR or
#DELETED
no matter what I do with these records I can't get rid of them or find out where they came from.
Can any help me just delete these out of the table?
Would be extreemly helpful as they are now getting on my nerves :-)
Cheers Homer
I have created a Asset database using MS Access 2010 & the same was published in SharePoint portal. Post publishing, all the "Query & Forms" are working as excepted but the "Reports" are not working.
The "Reports" is designed with "Group & Sort" option. Post grouping am able to see the "=Sum([Expression])" (i.e. Sum of the Value) value in MS Access Locally but not in SharePoint. Once i published in SharePoint, am getting "#Error" message for all the below expression wherever i have used in my reports design.
Expression Used:
Below expressions are works fine locally & getting #Error in SharePoint.
1. =Sum()
2. ="Page " & [Page] & " of " & [Pages]
3. =Now()
4. =Date()
One of the tables in my access file is a linked excel file. I however now want to create a form with which I want people can enter information in the table. However when I try to do this, it doesn't work. Whether it's possible to create an input form for an excel linked access table?
View 1 Replies View RelatedI have an Access 10 DB that includes 299 names and other associated data relevant to these names. I have a need to drop 249 of these names that are no longer needed in the DB, and just keep the 50 names that would remain in the table.
If I am in the table is there any way to somehow "designate" or select the 50 names I want to keep and then just mass delete the other 249 in one fell swoop? If I can somehow sort the 50 names so they would appear as the first 50 names in the table, then I could simply delete all the names below.
But not sure how to make this happen. It would seem to be the simplest solution. Unless I can physically drag and drop each of the 50 names I want to keep to the top of the table, but I don't think this is possible.
Three tables:
Employee, Sessions, EmployeeSessions.
Many sessions can have many employees - thus the joining table has been included.
When trying to delete an employee from the database using a form, I encounter the error:
The record cannot be deleted or changed because table 'tblEmployeeSessions' includes related records
Is there a problem with my table relationship structure? Or is it 'correct' that as the employee is supervising a session he/she cannot be deleted as this would interfere and maybe mess up the session record?
I have split database (B/E is in the SharePoint library, F/E has users on a local PCs). Sometimes, when I update/add data (does not matter if it is via form or directly in the table) it looks OK, but when I re-open the database, the data are gone.
Problem is that I cannot catch the moment when data were not saved (sometimes data are saved, sometimes not). I can point out this: if I re-enter the missing data, primary key continues subsequently, it looks like the data have never been entered. I tried to use script
Code:
If Me.Dirty Then Me.Dirty = False
on "On Close" form event, does not work.
B/E is linked by VBA code and it looks OK (no error, Link Manager shows correct path). I suspect interrupted connetion to the SharePoint but I don't know how to check it. I implemented VBA script co keep open connection to the SP but the issue persists.
I have linked tables from SQL Server using ODBC connection that their location never changes. I have used certain fields of those tables to create queries and make table queries to derive to the information I needed.
On these tables on SQL Server, there is new data added daily. Every day, midnight, there's new data records added of whatever transactions took place in that working day. how often do I need to refresh linked tables in this case to get the latest data added. I mean, once I am linked, the make table query using those defined fields, would it get the latest data added by default when the query is executed, or I must refresh linked tables using Linked Table Manager and then run make table query.
Also, if I want the access to automatically refresh linked tables, can I use the following code? I have added this code, and executing it through a button, but I don't see anything happen, the database becomes inactive for couple seconds (I guess while it is updating) but I don't know is it updating the tables for sure or not, though I am not receiving any error when executing the code through the button.
Function RefreshLinkedTables()
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
tdf.RefreshLink
End If
Next td
End Function
How to Update ODBC linked tables on ms access?
View 5 Replies View RelatedI have linked tables in my db at the moment that rely on user dsn connections to an SQL server. I've been reading about DSNLess connections and want to try convert what i have to have permanent DSNless connections, but the code I've found doesn't appear to be working.
I've removed server specific details where i felt necessary, but when running the code i have it in place.
Code:
Public Sub RefreshODBCLinks()
Dim connString As String
Dim db As DAO.Database
Dim tb As DAO.TableDef
connString = "DRIVER=SQL Server;SERVER=<database ip address>;DATABASE=<
[Code] .....
I have a split database.(Access 2010).Three of the linked tables are Appointments, Appointments_OLD, Appointments_NEW.
I want to use vba to rename Appointments as Appointments_OLD (replacing the current one) and to rename Appointments_NEW as Appointments (replacing the current one)
I have used:
'replace Appointments_OLD by Appointments, replace Appointments by Appointments_NEW
DoCmd.Rename "Appointments_OLD", acTable, "Appointments"
DoCmd.CopyObject , "Appointments", acTable, , "Appointments_NEW"
Unfortunately this just made Appointments and Appointments_NEW clones of one another - changes in one automatically occur in the other.
What I actually want to do is to swap the names round in the backend database while maintaining the right links to the frontend.
Is this possible using vba in the frontend?
Trying to get a record entered into a field on table (a) to automatically enter into same field on table (b).Example: Plant database table, input record in plant name field. Have same record appear in propagation table in the plant name field.Played around with relationships a bit, don't know if there is where u do this.Plant name is primary in both tables.
View 1 Replies View RelatedHello All,
I currently have a bunch of tables that I have linked to a SQL database. For development purposes (easy of use), I'd like to turn all those tables into regular tables rather than linked so if I take the db offsite, all the data is with me. I realize there will be no updates, etc. but that really doesn't matter for my needs in this case.
Is there a script or an easy way to do this? (besides doing a manual import for a LOT of tables)
I've tried searching but I might not be searching for the correct terminology.
Thanks in advance!
Randy