Error running query after splitting database

[jackijackijacki]

I am trying to run a query on linked tables in my back end database. However
I am getting the following error message:

The search key was not found in any record. (Error 3709)
This error occues when an ISAM SEEK is being executed and there are no
matching values in the index.

How do I resolve this issue so that the query will run again?

 

[Klatuu]

What kind of database are you linking to?
Can you open the tables directly and see the data?
post the SQL of you query, please.

 

[jackijackijacki]

It's an Access 2007 .accdb database.
Yes I can open the tables directly and see the data.

The SQL is:
SELECT [LIMS Butter Retail].IDR, [LIMS Butter Retail].[Date packed], [LIMS
Butter Retail].[UB Date], [MF Butter Retail].[Entry Date], [MF Butter
Retail].Source, [MF Butter Retail].DT, [LIMS Butter Retail].[Plate TVC],
[LIMS Butter Retail].[Y & M cfu/ml], [LIMS Butter Retail].[Air Plate Y & M],
[MF Butter Retail].Note AS [Retail Comments], [LIMS Butter Retail].[Churn No]
FROM [LIMS Butter Retail] LEFT JOIN [MF Butter Retail] ON [LIMS Butter
Retail].[UB Date] = [MF Butter Retail].[Sample Code]
ORDER BY [LIMS Butter Retail].[Date packed];

 

[Klatuu]

Sorry, but I don't see anything obvious.
All I see that concerns me is extensive use of spaces and special characters
in your naming. Names in Access or any other DBS should contain only
letters, numbers, and the underscore charcter. There are two cases where I
think your naming may be causing a problem.
[Air Plate Y & M]
The & is a concatenation character and even though enclosed in brackets may
be an issue.
[Y & M cfu/ml]
Here we have the concatenation symbol and a mathmatical operator /

I am not very familiar with 2007, but it may be it ran in an unsplit
environment, because it did not need an ODBC driver to execute. Now that it
is split, the translation by the drive could be an issue.

I would recommend cleaning up your naming conventions and see if that helps.

--
Dave Hargis, Microsoft Access MVP

It's an Access 2007 .accdb database.
Yes I can open the tables directly and see the data.

The SQL is:
SELECT [LIMS Butter Retail].IDR, [LIMS Butter Retail].[Date packed], [LIMS
Butter Retail].[UB Date], [MF Butter Retail].[Entry Date], [MF Butter
Retail].Source, [MF Butter Retail].DT, [LIMS Butter Retail].[Plate TVC],
[LIMS Butter Retail].[Y & M cfu/ml], [LIMS Butter Retail].[Air Plate Y & M],
[MF Butter Retail].Note AS [Retail Comments], [LIMS Butter Retail].[Churn No]
FROM [LIMS Butter Retail] LEFT JOIN [MF Butter Retail] ON [LIMS Butter
Retail].[UB Date] = [MF Butter Retail].[Sample Code]
ORDER BY [LIMS Butter Retail].[Date packed];

What kind of database are you linking to?
Can you open the tables directly and see the data?
post the SQL of you query, please.