Solved

Referential Integrity Problem

Posted on 2004-08-18
8
316 Views
Last Modified: 2012-08-13
I have two tables, tblOrders and tblOrderDetails on which referential integrity is enforced.  I have an orders form which an order detail subform.  When the user chooses an order type, the code saves the record and then pulls up an additional form asking for more information based on the order type.  This code has been used for 6 months without incident.  Yesterday, I started getting a run-time error message saying that a record couldn't be located in tblOrders.  When I hit debug, the problem appeared to be on the code which saved the record.  On the theory that the file had been corrupted, I did a compact/repair on both the tables file and the front-end file.  No luck.  I created a new file for the tables, no luck.  I have checked tblOrders and the required record is definitely in the table.  I have no idea what to try next.  Any ideas on your end?  
0
Comment
Question by:SallyBullard
  • 4
  • 4
8 Comments
 
LVL 84
ID: 11830756
Create a new file for your frontend objects, and import them (making sure to reset all references in the new file). Before doing this, however, make A COPY of your current file ... you never know what will happen.

What version of Access are you using?
0
 

Author Comment

by:SallyBullard
ID: 11830988
I am using Access 2000.  Since I posted the question, I have discovered that the problem is not with the reference.  I have a form and subform with the OrderID as the linking criteria.  Previously, when a new item was added to the order detail subform, the order ID field was automatically added to the detail subform because of the link, I guess.  For some reason that has stopped working.  I have added code to the OnEnter event of the subform to put the order ID in the proper field on the subform and it works.  What I find frustrating is figuring out what could cause this to suddenly occur?  I don't think the file is corrupted, because the front-end portion of this program is on 10 computers in my company and they all started doing the same thing!  One of the VERY frustrating things about Access, as much as I love it.

0
 
LVL 84
ID: 11831274
>> I have added code to the OnEnter event of the subform to put the order ID in the proper field on the subform and it works

Do yourself a favor and make a backup of your files now. This is not normal behaviour, and abnormal behaviour in Access is normally a big, flashing neon sign that says "I'm About To Corrupt And Lose All Your Data!!!" <g>.  Also, the Enter event only occurs ONCE - when the user first leaves the main form and enters the subform, so if users add more than one OrderDetail, you could end up with other errors or, worse yet, orphaned records.

Do all users have their OWN copy of the frontend, all of which are connected to the SAME backend?

Do you have relationships setup on the backend tables IN the backend? If all users have their own copy of the frontend, and all users started having the same problem at the same time, you may have some issues with relationships (aka "constraints"). Access handles these internally, and you could have a pending corruption issue. Same drill as before - make a copy of your tables NOW ...

When you created a new file, did you import the tables to this new file, or use some other method? If you imported the tables, then Access will also import the constraints, and you may simply have moved the problem from one db to another. You may actually have to rebuild by hand (i.e. rebuild your table structure, recreate your relationships/indexes etc, then run Insert queries to move your data across).
0
 

Author Comment

by:SallyBullard
ID: 11832044
Oh, great!  Yes, all users have their own copy of the frontend (the main file is on the server and then copied to each individual workstation).  The main relationships are set up in the file with the backend tables.  This is not the first weird problem I have had with Access.  Two months ago when I was working on a major revision of the database, I started getting very strange errors in the Visual Basic Code.  It would just stop recognizing fields that were definitely on the forms, it quit recognizing the Me. designation in the code, etc.  In every case, if I created a new file and copied everything over the problem would go away.  Then, all of a sudden, it just stopped giving me errors.  I have felt for a while that there are major problems somewhere in my file or in Access, but am at a loss as to how to fix it.  Starting over would be a major project, but I am beginning to feel that maybe that is my only option.  I have told the staff to make a copy of the data tables every day (usually we do it once a week) so the most we will lose is one day's work.  Are there any options other than starting again?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 84
ID: 11832214
If the users copy the file to their workstation regularly (like every day, everytime they log in, etc), then I'd bet a day's pay your project is corrupt.

There is always the undocumented SaveAsText and LoadFromText features that will handle your non-table objects (forms, reports, etc). I use them somewhat regularly when developing, espeically when I think my VBA project may be corrupt. I export all objects, build a new, blank database, then use the LoadFromText routine to import all objects to my new, blank database. I have personalized routines to do this, but they wouldn't do you much good since they copy things to and from specific machines on my network (and do a bunch of other things as well). Notice, however, the "undocumented" wordage - MS doesn't support these, and they're really for use internally. Basically, they save the Text definition of your objects (matter of fact, after running SaveAsText, you can open the resulting file in Notepad and view this definition in plain text ... pretty neat, if you ask me).

Here's a link to a recent exchange ... includes a link in my comments to a website with the SaveAtText stuff on it:
http://www.experts-exchange.com/Databases/MS_Access/Q_20891441.html
0
 

Author Comment

by:SallyBullard
ID: 11841899
We continue to have weird problems, mostly when saving a record.  We are creating a new file for the front-end and importing all the forms, etc. today.  I followed your link to the website with a sub called DocDatabase.  I am a little confused as to how to use it.  Could you give me precise instructions?  Are the Application.SaveAsText and Application.LoadFromText built-in, or do I have to go somewhere else for the code?  Do I run the DocDatabase from a copy of my front-end?  Thanks for your time.

0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 11842318
The LoadFromText and SaveAsText are builtin functions, but you won't find any real documentation on them (since they're "undocumented"). I'd certainly do this on a copy of my frontend. I've included my code module at the end of this posting.

1) Copy and paste the code at the end of this message into a new, blank module. Name it "basExportObjects". The code begins with "'************** CODE START **************". AFter pasting, review it and make sure you don't have an errors (red highlighted text) ... sometimes a copy and paste can throw off formatting. Save these changes.
2) Make a copy of your database.
3) Open the copy, and open the Immediate window (type Ctrl + G)
4) Type this in the immediate window: ?ExportObjects
5) When this process finishes, build a new, blank database in the SAME directory as the current datbase. This is important; if you are currently in the C:\YourDatabase directory, make SURE to build the new database in the C:\YourDatabase directory. Open that new database
6) Add a new module, then copy and paste the code below into that module (just like in Step #1 above). Name that module "basExportObjects"
7) Open the Immediate window again (Ctrl + G), click Tools - References and find and set a reference to the Microsoft Scripting Runtime library and the Microsoft DAO Object Librayr (Tools - References to do this).
8) After ensuring that you have the references, type this in the immediate window: ?ImportObjects

You may be asked to save a few items ... just click Yes and the code will continue. This can take a few minutes, so be patient ... a messagebox will pop up when finished.

AFter the import is finished, you'll need to import your tables (or re-link them), set the startup properties, setup all references, etc etc ... same thing you'd do when buildilng a new db. Also, it's a good idea to Compile the new database to make sure all references are remade, and to compact and repair.



'************** CODE START **************
  Option Compare Database
  Option Explicit

Public Function ImportTextObject(TypeOfObject As AcObjectType, NameOfObject As String, PathToObject As String) As Boolean

  '/Purpose:
  '/Created: 1/26/2004 08:50 AM
  '/Created By: Scott

  On Error GoTo Err_ImportTextObject
  LoadFromText TypeOfObject, NameOfObject, PathToObject

Exit_ImportTextObject:
  On Error Resume Next
  Exit Function
Err_ImportTextObject:
  MsgBox Err & ":" & Error$, vbCritical, "basExportObjects" & ": " & "ImportTextObject"
  Resume Exit_ImportTextObject

End Function
'
'
Public Sub SaveTextObject(TypeOfObject As AcObjectType, NameOfObject As String, PathToObject As String)

  '/Purpose:
  '/Created: 11/21/2003 05:07 AM
  '/Created By: Scott

  On Error GoTo Err_SaveTextObject
  SaveAsText TypeOfObject, NameOfObject, PathToObject


Exit_SaveTextObject:
  On Error Resume Next
  Exit Sub

Err_SaveTextObject:
  MsgBox Err & ":" & Error$, vbCritical, "basExportObjects" & ": " & "SaveTextObject"
  Resume Exit_SaveTextObject

End Sub

Public Function ImportObjects(Optional PathToFiles As String)

  On Error GoTo ProcErr

  Dim strPath      As String
  'Dim strDir       As String
  Dim fld          As Scripting.Folder
  Dim fil          As Scripting.File
  Dim fso          As Scripting.FileSystemObject
  Dim qry          As QueryDef
  Dim con          As DAO.Container
  Dim doc          As DAO.Document
  Dim dbs          As DAO.Database

  Set fso = New Scripting.FileSystemObject

  '/first we must delete all objects in this database
  '/we're using magic numbers because this routine deletes the basGlobals module which house
  '/many of the constants and such ...
  Screen.MousePointer = 11

  DoCmd.SetWarnings False

  Set dbs = CurrentDb

  Set con = dbs.Containers("Forms")

    For Each doc In con.Documents
      DoCmd.DeleteObject acForm, doc.Name
    Next doc

  Set con = dbs.Containers("Modules")

    For Each doc In con.Documents

        If doc.Name <> "basExportObjects" Then
          DoCmd.DeleteObject acModule, doc.Name
        End If 'If doc.Name <> "basExportObjects"

    Next doc

  Set con = dbs.Containers("Reports")

    For Each doc In con.Documents
      DoCmd.DeleteObject acReport, doc.Name
    Next doc

    For Each qry In dbs.QueryDefs

        If Left(qry.Name, 1) <> "~" Then
          DoCmd.DeleteObject acQuery, qry.Name
        End If 'If left(qry.Name, 1) <> "~"

    Next

  DoCmd.SetWarnings True

  DoEvents: DoEvents:

  Application.RefreshDatabaseWindow

  DoEvents: DoEvents: DoEvents:

    If Len(PathToFiles) = 0 Or IsNull(PathToFiles) Then
      strPath = CurrentProject.path
    Else
      strPath = PathToFiles
    End If 'If Len(PathToFiles) = 0 Or IsNull(PathToFiles)

  Set fld = fso.GetFolder(strPath & "\Forms")

    For Each fil In fld.Files
      ImportTextObject acForm, Left(fil.Name, Len(fil.Name) - 4), fld.path & "\" & fil.Name
      Debug.Print fil.Name
    Next fil

  Set fld = fso.GetFolder(strPath & "\Views")

    For Each fil In fld.Files
      ImportTextObject acQuery, Left(fil.Name, Len(fil.Name) - 4), fld.path & "\" & fil.Name
      Debug.Print fil.Name
    Next fil

  Set fld = fso.GetFolder(strPath & "\Modules")

    For Each fil In fld.Files

        If fil.Name <> "basExportObjects.mod" Then
          ImportTextObject acModule, Left(fil.Name, Len(fil.Name) - 4), fld.path & "\" & fil.Name
          Debug.Print fil.Name
        End If 'If fil.Name <> "basExportObjects.mod"

    Next fil

  Set fld = fso.GetFolder(strPath & "\Reports")

    For Each fil In fld.Files
      ImportTextObject acReport, Left(fil.Name, Len(fil.Name) - 4), fld.path & "\" & fil.Name
      Debug.Print fil.Name
    Next

  MsgBox "Finished importing text files found in " & strPath & " folder."

ProcExit:
  On Error Resume Next
  Screen.MousePointer = 0
  Set fld = Nothing
  Set fil = Nothing
  Set fso = Nothing
  Set qry = Nothing
  Set con = Nothing
  Set doc = Nothing
  Set dbs = Nothing
  Exit Function

ProcErr:

    Select Case Err.Number
      Case Else
        MsgBox "Error: " & Err.Description & vbCrLf & vbCrLf & "basExportObjects_ExportObjects"
    End Select

  Resume ProcExit

End Function


Public Function ExportObjects()

'Purpose:
'Created: 1/26/2004 08:45 AM
'Created By: Scott

  Dim strPath      As String
  Dim varItem      As AccessObject

  On Error GoTo Err_ExportObjects

  On Error Resume Next
  MkDir CurrentProject.path & "\Views"
  DoEvents
  strPath = CurrentProject.path & "\Views"

    For Each varItem In Application.CodeData.AllQueries
      Application.SaveAsText acQuery, varItem.Name, strPath & "\" & varItem.Name & ".vew"
    Next

  On Error Resume Next
  MkDir CurrentProject.path & "\Forms"
  DoEvents
  strPath = CurrentProject.path & "\Forms"

    For Each varItem In CurrentProject.AllForms
      Application.SaveAsText acForm, varItem.Name, strPath & "\" & varItem.Name & ".frm"
    Next

  On Error Resume Next
  MkDir CurrentProject.path & "\Reports"
  DoEvents
  strPath = CurrentProject.path & "\Reports"

    For Each varItem In CurrentProject.AllReports
      Application.SaveAsText acReport, varItem.Name, strPath & "\" & varItem.Name & ".rpt"
    Next

  On Error Resume Next
  MkDir CurrentProject.path & "\Modules"
  DoEvents
  strPath = CurrentProject.path & "\Modules"

    For Each varItem In CurrentProject.AllModules
      Application.SaveAsText acModule, varItem.Name, strPath & "\" & varItem.Name & ".mod"
    Next


Exit_ExportObjects:
  On Error Resume Next
  Exit Function
Err_ExportObjects:
  MsgBox Err & ":" & Error$, vbCritical, "basExportObjects" & ": " & "ExportObjects"
  Resume Exit_ExportObjects

End Function
0
 

Author Comment

by:SallyBullard
ID: 11914119
Thank you.   I used the code and created a new file for the front-end.  I also made created new tables and used queries to import the data into the new structure.  So far, everything is fine.  (I have also found one computer with outdated virus software and updated. Just in case that was the case of the file corruption.)
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now