Sub forms "Microsoft Office Access can't find the object" error in an

[goddersb]

Hi,

I am coming across an issue in a sub form in an Access 2007 adp file. Before anyone says "why are you using an adp" - i inherited it. Anyway, when i try to add a new item in the sub form, which is being displayed in a datasheet style i get the error "Microsoft Office Access can't find the object 'Select * FROM * WHERE.... ( i have replaced the table names etc here with the *.
* You misspelled the object name. Check for missing underscores (_) or other punctuation, and make sure you didn't enter leading spaces.
*You tried to open a linked table, but the file containing the table isn't on the path you specified. Use the linked table manager to Update the link and point to the correct path.


Steps i have taken thus far to try to resolve this are;
1. Removed extended properties from the SQL tables and columns.
2. Re-Linked the tables as suggested in them message above.
3. Created a new sub form and used this instead in the adp.
4. Recreated a new copy of the adp in the event it was corrupted.
5. Tried the Compact and Repair function.
6. Checked the SQL which is being displayed in the above message for any erroneous chars etc.

None of these has so far worked. However if i insert a entry manually in SQL using SQL and then view this in the adp - I am able to update these values without any problems. I am really struggling to get to the bottom of this, does anyone have any further possible suggestions - other than rewriting this something other than an adp?

 

[jrbarnett]

Try putting the table (and any fieldnames in it) inside square brackets. This way, if any of them are SQL Server reserved words, they are likely to behave in a better way.

Eg:

SELECT [field1] FROM [table1] WHERE [field2] = 'X'



John

 

[goddersb]

John,

Fairly certain that i have done this but i shall double check.

G

 

[goddersb]

John,

No joy i'm afraid, even if i onlyhave the following SQL
SELECT [DeviceID] FROM [tbl_Device] - it still fails with the SQL being displayed in the error message. Not my choice to have the annoying underscores in the table name either.

 

[jrbarnett]

You don't say which version of SQL Server is being used - I'm assuming here its 2005 or 2008 (or a variant of that), if you are using an earlier version, post again.

Look in your access DB for the server connection information - SQL username, or windows username if using domain authentication.
Next, with your Access DB open, use SQL Server management studio and view open connections ( the easiest way is to use New Query then enter sp_who2 and press F5.)
Before the SQL zealots tell me to use DMV's, yes I know they are preferred under 2005/8 but I haven't yet had the time to master them and this is cross platform.

You will see a list of connections - find the one that your ADP is using, you can use the username, the application name will be something like Microsoft Access.

Check that the default database for that user account, SQL account or group is your application's database (rather than master or something else, which would explain the missing objects message).

John