2

I have two Access databases: Main stores most of my data and Memos stores data of datatype Memo. I am storing the memos in a separate db because everything I read about Memo fields said they were prone to corruption and that the only safe way to protect your database is to have the memos in a separate linked db.

Memos has a table Info with fields: ID (type Autonumber primary key) Info (type Memo)

Main has a table Content with fields: ID (type Autonumber primary key) infoID (type Number) entryDate (type Date/Time)

I want to enforce referential integrity on Content so that it can only accept values that are valid IDs from table Info. But I can't, because Memos is a linked database. I can establish controls at another point in the data entry process to ensure that only values from Info can be inserted into Content, but I'd rather not code the validation if there's a way to enforce it through database constraints.

Is there another way to enforce integrity between linked tables that I'm not aware of, or a different way to handle the Memo storage problem so that I can keep the Memos in the same DB?

sigil
  • 9,370
  • 40
  • 119
  • 199
  • 1
    I think this is an old problem, not relevant to more recent versions (http://bytes.com/topic/access/answers/851398-memo-fields-multiuser-database) Which version are you using? – Fionnuala Dec 16 '11 at 22:14
  • @Remou, I'm using Access 2010. – sigil Dec 16 '11 at 22:31
  • 1
    Unless you are doing a lot of memo field updates across a shaky network, it should not be a problem. Or use method three in the link in my first comment (unbound controls). If you really must keep a separate table, consider data macros, available since 2010, to validate: http://blogs.office.com/b/microsoft-access/archive/2009/08/13/access-2010-data-macros-similar-to-triggers.aspx – Fionnuala Dec 16 '11 at 22:49
  • I am doing a lot of memo field updates across a shaky network. Possibly using wi-fi, which the link in your first comment mentions as a corruption risk. Data macros look promising; I'll research those more. – sigil Dec 17 '11 at 00:31
  • This will have been said many times before, but if you have any other option available, then avoid a wireless network at all costs, not only is it a security risk, but performance will be slow at the best of times and could have interference from many different sources. – Matt Donnan Dec 17 '11 at 14:24

1 Answers1

2

No, there is no way to enforce referential integrity between tables in different databases.

IMHO, the best solution is to setup up an MS SQL database back-end, and set up referential integrity within that DB. In general, it is more robust than MS Access in your situation (flaky network). You might be able to get away with an SQL Server Express version.

BIBD
  • 15,107
  • 25
  • 85
  • 137
  • I was really trying to do this in Access without SQL Server, because I didn't have server-side administrative privileges and all I could do was put an Access file in a shared network folder. But the project's over, and if I had to do it again I'd go the SQL Server route. – sigil May 16 '12 at 20:09