Cannot insert datasheet into form (Access Project)

Question

Following the instruction found here: Insert a datasheet into a form I get an error when trying to insert a query as a datasheet in an Access Project (SQL Server 2005 backend)

The form name you entered doesn't follow Microsoft Office Access object-naming rules

I selected the view View.dbo.viewname from the dropdown in 'Source Object' and the above error occurred as soon as I did that. If I take off View.dbo.viewname, it saves, but then I can't see the datasheet data. I want to add the view as it contains subdatasheets.

Wrapping it in brackets, e.g. [View.dbo.viewname] or View[.]dbo[.]viewname (as dots are apparently not allowed, although the link suggests otherwise) doesn't help

Answer 1

It's a bit deceptive; "Query.foobar" is OK, but "Query.foo.bar" is not.

MS Access seems to be using that first portion as a way of deciding if your record source is a Query or Table, and the period (.) is a delimiter, and you seem to be limited to one. This is probably one of the reasons why it is disallowed in object names.

It looks like you are accessing the view directly instead of creating a link to the view in the tables tab/list (you shouldn't even be able to create a table/query named like that). Perhaps this is available in ms-access-2007, I'm not familiar enough with it.

In any case, my solution would be to create a table link to View.dbo.viewname and rename it (if MS Access doesn't do that for you by it self) to dbo_viewname. This will let you set the data source to Table.dbo_viewname.

Edit:
To create the table link,

1. go to the Tables tab/list on the database window
2. right click in the list
3. Select link tables...
4. Change Files of Type to ODBC databases (I assume you already have an ODBC connection set up. If not, how are you getting to your data?).
5. Switch to the Machine Data Source tab
6. Select your data source.
7. Click OK
8. Select the tables/views you want.
9. Click OK

The links will show up in your tables tab/list as with arrows pointing at little green globe.

Don't type the data source in. Use the pull down, that will guarantee you are using the right syntax.

Answer 2

Never managed to get it to work. Perhaps due to being an Access Project (ADP) rather than database (MDB). Had to resort to creating forms (with nested subforms) instead of datasheets to get what I wanted.

Also, nested forms (two levels down) don't seem to work in Access 2007, but do in 2003, so had to go back to that.