I have a Access database where I need to locate a project depending on the ProjectID, ProjectTitle, First name and LastName.When Iam searching the table with the FirstName or LastName,I create a pointer for that CPID (Contact_Person_ID) I get this run time error saying CPID is not the index of this table.The index works for the other fields in the table Lastname,First Name ..it doestn't work only for CPID.Why? Here's my code Function FindingContact(PID As String) MsgBox "In finding Contact"
Dim db As DAO.Database Set db = CurrentDb()
Dim rsc As DAO.Recordset Set rsc = db.OpenRecordset("Contact_Person", dbOpenTable)
Dim rsct As DAO.Recordset Set rsct = db.OpenRecordset("Prj_Contact_Person_Type", dbOpenTable)
Do While rsct.EOF = False
If (rsct("Project_ID") = PID) Then
'If the located Project_ID has primary ContactPerson then book mark the current pointer P1 If (rsct("Contact_Person_Type") = "Primary") Then CPID = rsct("Contact_Person_ID") rsc.Index = "Contact_Person_ID" rsc.Seek "=", CPID If rsr.NoMatch = True Then MsgBox "Contact person not found!" Else CurPointer_P1 = rsc.Bookmark End If End If
'If the located Project_ID has Secondary ContactPerson then book mark the current pointer P2 If (rsct("Contact_Person_Type") = "Secondary") Then CPID = rsct("Contact_Person_ID") rsc.Index = "Contact_Person_ID" rsc.Seek "=", CPID If rsr.NoMatch = True Then MsgBox "Contact person not found!" Else CurPointer_P2 = rsc.Bookmark End If End If
'If the located Project_ID has Other ContactPerson then book mark the current pointer P3 If (rsct("Contact_Person_Type") = "Other_Contact") Then CPID = rsct("Contact_Person_ID") rsc.Index = "Contact_Person_ID" rsc.Seek "=", CPID If rsr.NoMatch = True Then MsgBox "Contact person not found!" Else CurPointer_P3 = rsc.Bookmark End If End If
I got this "AOIndex is not an index in this table" error with Access 2000 after replicating a database and storing it in the same network folder. I am not able to open my database now, i am afraid 2days work on my forms is lost. I usually have backup, but the last backup i have is 2 days ago i have made alot of changes to 2 FORMS after that :(
Please anybody have suggestions on how to retrive my forms??? I am really concerned about the forms. I have backup for data and for the reports. Please help me?
Last week my hard disk on my old machine corrupted…
It happened half way through entering information on a particular MSAccess 2000 database although I am assured that the failure of the database did not result in the failure of the disk.
The computer would no longer boot up and unfortunately we hadn't taken a ghost image of the disk or made a boot up disk. In the end we managed to take the hard disk out of the machine - then using a small white pin on one of the disks rear ports change it to a slave by placing a small white plug in two of the vertical pins at the rear of the scsi port we then put it into a vacant port on another computer's scsi bus and got that computer to recognise the disk.
Once I managed to get the disk up and running I copied the file to the master disk and went about investigating whether I could get the database back.
With regards to the database the problem I got immediately prior to disk failure was -'AO Index' is not an index in this table- and this was still the case when I could see the file again.
I tried compact and repair I tried copy and pasting / renaming the database I also tried importing tables from the corrupted database by using a fresh database.
None of these things worked.
Note initially I was getting a Cyclical Redundancy Check Error trying to paste the database from the old slave hard disk to the master disk however this seemed to disappear.
Eventually I set up an ODBC link and was able to export the data into an excel spreadsheet. Obviously all the forms / reports have been lost however this was a small personal database on which had been conducted little design. I also have a similar database into which I can place the information and it wil probably take me about a week to get the data back into a database.
All in all a good learning experience - our network databases are fully backed up and we would just go back to a historical copy if something happened to one of our Access applications. I did a search on the forum and only found one other thread on this - 'AO Index' is not an index in this table - so thought I'd post my experiences.
Would welcome any comments if people have a better understanding of the causes / solutions to this problem.
How do I create a table that can cross index items in another table. Maybe I am not using the right terms here so let me show a small example.
Say I have a tables of words. tblWords numWordID txtWord
Then I have some entries, all more or less synonyms of each others fresh new clean
Now I want to create a cross-index table, related to the table "tblWords" where I can select synonyms from words already in the table "tblWords", so if I for the word "fresh" add "new" and "clean" as synonyms or entries, if I then go and look at the word "new" it will already have the synonyms "fresh" and "clean", likewise the entry "clean" will then have the synonyms "fresh" and "new".
Kind of a many to many relationship junction table but only with one table!
I hope my explanation have not been to confusing, but let me know if you need a clarification.
I am attempt to connect to a DB2 table via ODBC. However, each time I do, I get an error stating that the index already exists (two indexes with the same name). Our IT folks are telling me that this is not the case, and additionally, I am able to connect using ODBC in excel, and I am able to conncet to other tables in this DB. Does anybody know a method by which I could connect to this table? Thanks!
Is there a way to change the message that is diplayed when a duplicate record is created? I have a field that does not allow duplicate entries and I would like to be more consise in my message to end users.
I'm trying to copy a table, minus a couple fields, so I need to copy the primary keys as well. When trying to add an index from the source table to the new table, it gives me an error saying that the object already exists in the collection. However, the indexes collection has a count of 0. What could this mean?
I am currently working on creating a database and have a few questions on indexing fields. Primarily, does it speed up or slow down the overall database speed? The reason I am asking is because I read an article somewhere that advised against Indexing because haveing a great deal of indexed values actually slows down a database instead of speeding it up.
The DB I am working on has a loooot of IDs and I just wanted to know if it would be a good idea to index them or not.
Also, if two tables are joined at a foreign key like... lets say... PetID, would it be extraneous to index the PetID field in both tables? If so then in which table should the ID be indexed?
I am currently working on a Access db that tracks properties and owners. It has a many to many relationship between the property and owner tables. It has a subform for "current owners" which I am able to see the current owner(s) of that property, re-assign owner(s) and add additional owner(s). My problem is that when I add a new property through the main form then go to the subform to add an owner I get an error message "Index or primary key cannot contain a Null value (Error 3058)" Does anybody know of a good approach to troubleshooting this error message?
Does anyone know how to fix or at least what caused the following error? Operation invalid without a current index. (Error 3019) I have tried reinstalling Access, repairing, compacting and nothing is working. I have also searched the web with no luck on any solutions.
I am recieving the following message on a computer
'~sq_fShiftReports' isn't an index in this table. Look in the Indexes collection of the TableDef object to determine the valid index names.
Now here is why this is very confusing. It is a progrma that is on our shared drive and can be opened by everyone except this one computer. I even tried to save the one I have on my desktop and open that version, but was unable. If someone can help me with this, it would be great. If not the only other solution is to uninstall and reinstall access. (We are using access 97)
I am pullling data from a query using an unbound form and a query that that uses linked tables. I can not edit the index of the tables, so is there a way i can create a new index for sorting data in a form?
I am creating a table with a make-table query. The only trouble is that I can't figure out how to designate which field will be the primary key. Is there a way to do this...... other than creating the table, then manually going into desing mode and specifying the primary key there? I want this to be automated.
Hi I have table what look like this: [ip address] [device] [fault reason] [start time] [end time] [channel] 111.111.22 divisi service 12234 12.8.12:00 12.8.13:00 BBC
I have make index for this table whit query1. Query1: CREATE UNIQUE INDEX indeksi ON Table ([IP address], device, [fault reason], [start time], [end time];
This index prevent my INSERT INTO-query not add duplicates into table.
Now I'am trying to do index to the table which prevent INSERT-INTO query to add records which [start time] is older than the tables latest [start time] in table records. How this kind of index should be done?
I have read a post in regard to this same error (on an earlier version of Acess), so I hope it is not incorrect of me to ask further questions in regard to the error.
I have had this error on a database recently. Our IT section was able to recover the database from the server's backup, but the data was missing for the full days work on Tuesday.
What I was wondering is if this could cause data loss and how to prevent it happening again?
I don't have the file any longer as the IT department restored over top of it ... so I can't show it to anyone. My Executive Manager is not happy with what has happened and wants to make sure that we can rectify the problem.
I have a db full of linked tables. I do not know the type of database the original tables are. I am linked using a program called RealVNC. I can use a make table query to export the data into an access table but I can not figure out how to create indexes in the new tables.
Should I write a function in the new db and call it from a macro in the linked db? Is there a way to build indexes in external tables? Is there a way to include indexes in a make table query?
Any help would be greatly appreciated.
By the way I need to transfer 20 tables each night. My plan is to open a form when I leave the office and have the queries begin around 1am.
I have a table which contains many records with a unique field and then up to 25 data fields with varying data in each record. What I want to end up with is a new table with a record for each unique field & one of the 25 fields in the source table. For example: I have a record where the unique field is 001 and data fields 1-5 are 12s,14b,17c,18r,100v, respectively. What I need to end up with is a table that shows:
....then we'd look at the 2nd record of the source table and do the samething. Unfortunately, not all of the non-unique fields will be populated - maybe there will only be 5 instead of 25 and not necessarily in the 1st five consecutive data fields.
I have a table that holds banking transactions that I download from an online bank account. I have a multi field index setup to make it impossible to import duplicates. The problem is that sometimes I have duplicates. ie. A deposit for $5 on 1/1/2006 and then a second deposit for $5 on the same day. My table would stop this from happening. There isn't a unique field from the new records I'm dumping in so what would be the possible solution here? Is there any querie I can run that would list duplicate values from the new records table to the main records table before they're dumped. How do I check for duplicates on multiple fields?