The record source...specified on this form or report does not exist

  • Thread starter Thread starter mr-tom
  • Start date Start date
M

mr-tom

Hello,

I've got what I'm really hoping isn't a big problem, but I have a nasty
feeling about this.

I have a database which functions as a front end for an SQL Server
warehouse, so it just holds linked tables and queries.

I have another database (let's call it the back end database) which holds
data which the SQL server warehouse doesn't and is also linked to by the
front end database.

A huge amount of work is invested in these and replacing them is a complete
non-starter so I have to solve this problem.

When I open one of the linked tables (called Data - Advisers) in the front
end database, I get the following message:

The record source 'SELECT [List - Current].[Current Ref], [List -
Current].Current FROM [List - Current];' specified on this form or report
does not exist.
You misspelled the name or it was renames or deleted in the current
database, or it exists in a different database...

Nothing has been deleted or renamed. Nothing has been changed at all.
Going into the back end database, everything is present and correct, no error
messages.

This has worked fine for ages - no errors before now.

I've compacted and repaired both databases. I've also turned off the Name
Autocorrect in the front end database and re-compacted (I saw somewhere that
this could help) and repaired but to no avail.

I don't know what could have cuased this or how to fix it. Unfortunately,
google hasn't offered much help. ;-)

Any help or pointers you can provide will be greatly appreciated.
 
Since you get the message referring to [List - Current] when you open
[Data - Advisers], then there is something in the properties of Data -
Advisers.

My first guess would be that there is a lookup defined in Data -
Advisers on some field.

Joan Wild
MS Access MVP
 
I'm not sure why you'd get that error message, but here's something I'd
try...

"delete" the link to that table.

Re-create a link to that table.

Try again.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks - have tried that a few times, have removed the linked table
completely and put it back in but no help I'm afraid.

Jeff Boyce said:
I'm not sure why you'd get that error message, but here's something I'd
try...

"delete" the link to that table.

Re-create a link to that table.

Try again.

Regards

Jeff Boyce
Microsoft Office/Access MVP

mr-tom said:
Hello,

I've got what I'm really hoping isn't a big problem, but I have a nasty
feeling about this.

I have a database which functions as a front end for an SQL Server
warehouse, so it just holds linked tables and queries.

I have another database (let's call it the back end database) which holds
data which the SQL server warehouse doesn't and is also linked to by the
front end database.

A huge amount of work is invested in these and replacing them is a
complete
non-starter so I have to solve this problem.

When I open one of the linked tables (called Data - Advisers) in the front
end database, I get the following message:

The record source 'SELECT [List - Current].[Current Ref], [List -
Current].Current FROM [List - Current];' specified on this form or report
does not exist.
You misspelled the name or it was renames or deleted in the current
database, or it exists in a different database...

Nothing has been deleted or renamed. Nothing has been changed at all.
Going into the back end database, everything is present and correct, no
error
messages.

This has worked fine for ages - no errors before now.

I've compacted and repaired both databases. I've also turned off the Name
Autocorrect in the front end database and re-compacted (I saw somewhere
that
this could help) and repaired but to no avail.

I don't know what could have cuased this or how to fix it. Unfortunately,
google hasn't offered much help. ;-)

Any help or pointers you can provide will be greatly appreciated.
Click to expand...
Click to expand...
 
Yep - there is a lookup, but it works in the back end, it always used to work
in the front end and nothing there has been changed.

Anything else I can try?

Joan Wild said:
Since you get the message referring to [List - Current] when you open
[Data - Advisers], then there is something in the properties of Data -
Advisers.

My first guess would be that there is a lookup defined in Data -
Advisers on some field.

Joan Wild
MS Access MVP


mr-tom said:
Hello,

I've got what I'm really hoping isn't a big problem, but I have a nasty
feeling about this.

I have a database which functions as a front end for an SQL Server
warehouse, so it just holds linked tables and queries.

I have another database (let's call it the back end database) which holds
data which the SQL server warehouse doesn't and is also linked to by the
front end database.

A huge amount of work is invested in these and replacing them is a complete
non-starter so I have to solve this problem.

When I open one of the linked tables (called Data - Advisers) in the front
end database, I get the following message:

The record source 'SELECT [List - Current].[Current Ref], [List -
Current].Current FROM [List - Current];' specified on this form or report
does not exist.
You misspelled the name or it was renames or deleted in the current
database, or it exists in a different database...

Nothing has been deleted or renamed. Nothing has been changed at all.
Going into the back end database, everything is present and correct, no error
messages.

This has worked fine for ages - no errors before now.

I've compacted and repaired both databases. I've also turned off the Name
Autocorrect in the front end database and re-compacted (I saw somewhere that
this could help) and repaired but to no avail.

I don't know what could have cuased this or how to fix it. Unfortunately,
google hasn't offered much help. ;-)

Any help or pointers you can provide will be greatly appreciated.
Click to expand...
Click to expand...
 
mr-tom said:
When I open one of the linked tables (called Data - Advisers) in the front
end database, I get the following message:

The record source 'SELECT [List - Current].[Current Ref], [List -
Current].Current FROM [List - Current];' specified on this form or report
does not exist.
You misspelled the name or it was renames or deleted in the current
database, or it exists in a different database...

Nothing has been deleted or renamed. Nothing has been changed at all.
Going into the back end database, everything is present and correct, no error
messages.

This has worked fine for ages - no errors before now.
Click to expand...

Perhaps Access can be confused by dashes in names, and occasionally
interprets one or more of them as a requirement to attempt a subtraction
operation. Unfortunately, the fact that Access used those very same
names successfully in the past is no guarantee that it will always
continue to do so.

A couple years ago I created a table to hold information about research
grants. I chose "Grant" as the name of the field where I stored my
grant ID values. Access didn't complain about my choice, and let me
load a few hundred thousand records into my table. However, a few weeks
later Access failed to perform several of the operations which were
previously successful. And I had no clue why --- I hadn't made any
changes to anything.

After a day of frustration, I finally changed the field name to GrantID,
even though I was convinced it wouldn't help. Bingo. Problem solved.

Does my experience seem familiar? In my case, I used an SQL keyword.
In your case, you have a math operator included in your names. But in
both cases, I think Access is confused by the names.

I realize my interpretation is speculative. And even testing it out
could be painful for you considering how much you've built using dashed
names. So I'll suggest you just keep this in mind in case you can't
find any other way to fix your current problem.

Good luck,
Hans
 
A few thoughts.....

Confirming a couple assumptions..... It sounds like you are talking about
the Access (not SQL server) portion of your back end. Here's some quesitons
/ things to check that may help sort it out. Of some of these overlap or
rule each other out.

Is there only one lookup which the error verbage could be referring to?
I.E. you don't have a mixture of in-the-table and in-the-form types?

It sounds like your lookup (or one of them) is of the in-the-table-type. If
so, on your "verifying" tests, are you looking directly at the BE table (when
it works) , and, directly at the linked table in the FE (when you get the
error message)? (if it were in a form then it may point to a problem with a
form lookup.

If your lookup is of the in-the-table type, try (after making a backup)
deleting it and instead creating the lookup in a form (or forms) in the front
end.
 
Thanks Hans and Fred,

No - none of the errors relate to the SQL warehouse - just the access back
end database ands the access front end database.

None of the databases contain any forms - just tables and queries. There's
only 2 people who would ever stick stuff in them and then generally through
mass import of data, so little use for forms.

As such, no chance of confusion between the same term used in different
contexts (i.e. form and table).

I'm going to have a look at all your suggestions and will report back.

Worst case, I can restore from backup, but that won't tell me what happened,
why and how to prevent it in the future.

Tom.
 
The developers in these forums all say never use in-table lookups, and to do
data entry only through forms. While I suspect that for us mere mortals,
breaking that rule is sometimes a good idea, I think that yours is an
example where the lookups are better off being created in the forms rather
than the tables.
 
Thanks Fred,

That's a good point, but I'd rather do away with the lookups altogether than
resort to forms, which bring their own special brand of "fun" to the mix.

Nothing we've tried has worked, so I'm restoring from backup and keeping my
fingers crossed.

I'm also keeping a hammer ready in case I find out who did the breaking...!
 
Ought to add - somebody else has suggested I should do away with the split
between the databases and simply migrate the back end tables into the front
end database. Apparantly this could be part of the problem.

I always thought that the split was "the right way to do things" and reduced
the chances of corruption etc, but to be honest I would welcome some pointers.

The database only has a few users and backend plus front end comes to about
15MB, the bulk of the data being in the warehouse.

Thoughts?
 
I think that you will get different answers from different groups. Most of
the top level experts in these forums are developers or do heavy-duty
development work. They have to deal in deploying and maintaining
applications for larger amounts of users. including those who know zero about
Access, or those who like to mess with stuff and do damage, and just blame
the developer when they mess something up. And so they also have to make
stuff dummyproof and bulletproof. That environment calls for splitting
all databases, having users interface only through forms, never using
in-table lookups etc.

My day job is running smaller technical companies, <25 employees, < 10 users
on any given database, <4 simultaneous users on any DB. I've got it
eaasier than the developers because I have fewer users, and they all know
Access a little, 1/2 of them are engineers, and all work for me and know the
rules. Our folks often create little databases in minutes instead of Word
and Excel documents. They do 1/4 of their data entry and 3/4 of their
searching in queries rather than forms, including specifying never-planned
search, sort and view conditions in the query grid in seconds. And since
(I think) that in-table dropdowns are the only way to have dropdowns in
queries, we use some of those. . Conversely, the development time for a DB
has to be 10 minutes or 10 hours and not 100 / 500 hours Only our most
widely used databases are split.


So there's two sides on the Access universe.
 
Well something has changed in the frontend. Does the frontend still
include a link to the the [List - Current] table?

Joan Wild
MS Access MVP


mr-tom said:
Yep - there is a lookup, but it works in the back end, it always used to work
in the front end and nothing there has been changed.

Anything else I can try?

Joan Wild said:
Since you get the message referring to [List - Current] when you open
[Data - Advisers], then there is something in the properties of Data -
Advisers.

My first guess would be that there is a lookup defined in Data -
Advisers on some field.

Joan Wild
MS Access MVP


mr-tom said:
Hello,

I've got what I'm really hoping isn't a big problem, but I have a nasty
feeling about this.

I have a database which functions as a front end for an SQL Server
warehouse, so it just holds linked tables and queries.

I have another database (let's call it the back end database) which holds
data which the SQL server warehouse doesn't and is also linked to by the
front end database.

A huge amount of work is invested in these and replacing them is a complete
non-starter so I have to solve this problem.

When I open one of the linked tables (called Data - Advisers) in the front
end database, I get the following message:

The record source 'SELECT [List - Current].[Current Ref], [List -
Current].Current FROM [List - Current];' specified on this form or report
does not exist.
You misspelled the name or it was renames or deleted in the current
database, or it exists in a different database...

Nothing has been deleted or renamed. Nothing has been changed at all.
Going into the back end database, everything is present and correct, no error
messages.

This has worked fine for ages - no errors before now.

I've compacted and repaired both databases. I've also turned off the Name
Autocorrect in the front end database and re-compacted (I saw somewhere that
this could help) and repaired but to no avail.

I don't know what could have cuased this or how to fix it. Unfortunately,
google hasn't offered much help. ;-)

Any help or pointers you can provide will be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Good thought.

Yes - it's still linked and displays correctly without any error messages etc.

I'm restoring from backup and keeping my fingers crossed.

Also looking at whether I can track changes to field names, designs etc.

Tom.

Joan Wild said:
Well something has changed in the frontend. Does the frontend still
include a link to the the [List - Current] table?

Joan Wild
MS Access MVP


mr-tom said:
Yep - there is a lookup, but it works in the back end, it always used to work
in the front end and nothing there has been changed.

Anything else I can try?

Joan Wild said:
Since you get the message referring to [List - Current] when you open
[Data - Advisers], then there is something in the properties of Data -
Advisers.

My first guess would be that there is a lookup defined in Data -
Advisers on some field.

Joan Wild
MS Access MVP


mr-tom wrote:
Hello,

I've got what I'm really hoping isn't a big problem, but I have a nasty
feeling about this.

I have a database which functions as a front end for an SQL Server
warehouse, so it just holds linked tables and queries.

I have another database (let's call it the back end database) which holds
data which the SQL server warehouse doesn't and is also linked to by the
front end database.

A huge amount of work is invested in these and replacing them is a complete
non-starter so I have to solve this problem.

When I open one of the linked tables (called Data - Advisers) in the front
end database, I get the following message:

The record source 'SELECT [List - Current].[Current Ref], [List -
Current].Current FROM [List - Current];' specified on this form or report
does not exist.
You misspelled the name or it was renames or deleted in the current
database, or it exists in a different database...

Nothing has been deleted or renamed. Nothing has been changed at all.
Going into the back end database, everything is present and correct, no error
messages.

This has worked fine for ages - no errors before now.

I've compacted and repaired both databases. I've also turned off the Name
Autocorrect in the front end database and re-compacted (I saw somewhere that
this could help) and repaired but to no avail.

I don't know what could have cuased this or how to fix it. Unfortunately,
google hasn't offered much help. ;-)

Any help or pointers you can provide will be greatly appreciated.
Click to expand...
Click to expand...
Click to expand...
 
I encountered this problem. I was looking for solutions, and the ones listed did not work! I found the control causing the problem (I deleted it off the form in Access and it went away), but even if I created it from scratch, the annoying message still came back. Sometimes it would open without a problem, but one or two more tries and it would fail. Then I realized something in the rowsource - the cursor was acting funny. It wasn't in the right position when I deleted letters. So I deleted the rowsource and typed it manually - IT WORKED! Before I had developed a query, turned it into a SQL, then copied and pasted it in. But when I typed it manually the problem went away.
 
Back
Top