Access Error: "can't sql_variant_property"

I am using an ADP connection to SQL Server. When I update a drop down box in a subform, I get the following message

[Application Name] cann't find the object 'SELECT *, sql_variant_property(value, 'basetype') AS type FROM
 ::fn_listextendedproperty(N'MS_Description',N'user',N'dbo',N'table',N'tbl_adtLog',NULL,NULL)

This is quite strange to me because the table tbl_adtLog is in my database, it does not have any extended properties and this table is not used in the Access GUI at all. But why does Access encounter this error?
Voodoo_chileAsked:
Who is Participating?
 
Leigh PurvisDatabase DeveloperCommented:
Yes I think it's important to clarify what I was saying.
Create a new, blank, ADP - import all objects into it.  That's your forms, reports, modules (possibly pages and macros :-s)
That's all there is to them.  The application is requesting to read a table property for some reason. In a form, that makes no sense.  Why should it care?  Time to reset and see where you're at.
(You'll need to set up the connection properties in the new ADP to point to the same server database of course.)

Just to confirm, ADPs remain as they were functionality-wise, even though it takes the Access team real resources to continue to support them in the advancing versions of Access.  (So the cries that they're dead do somewhat over-state the matter.)

<off-topic>
Hi Markus.  Well we've both always loved the more unusual subject matters (keeps the interest up doesn't it) - though I suspect that I might have been less inclined to dash back over had I not seen your name! ;-)
Great to see you around mate. Catch up soon no doubt!
</off-topic>
0
 
aikimarkCommented:
so tbl_adtLog is in your SQL Server database and owned by dbo?

have you tried Null as the first parameter?
0
 
Voodoo_chileAuthor Commented:
tbl_adtLog is owned by dbo schema.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Voodoo_chileAuthor Commented:
Yes, the table tbl_adtLog is in your SQL Server database and owned by dbo.
What do you mean "Null as the first parameter" ?

Thanks.
0
 
harfangCommented:
It seems that your query is written in SQL Server syntax. In any case, Access — or rather Jet — will not be able to understand it. When that happens, it will assume it's the name of an object and not an SQL query...

If this is meant as a pass-through query, you will have to save it as such in your queries. Then you can use it as row source by name. As far as I know, there is no way to write a pass-through directly into the row source property of a combo...

So, first things first: does this query work as a stand-alone query? can you make it work by changing it to a pass-through query?

I hope this helps
(°v°)
0
 
Voodoo_chileAuthor Commented:
Is it mean if I would like to write a SQL query, I have to use the pass-through query rather a plain SQL script?
0
 
harfangCommented:
Honestly, I have almost no practical experience with ADP projects.

However, in general, queries are interpreted by the Jet Engine, using Jet SQL syntax. It has a compatibility mode for SQL Server, but it will not understand a call like “ ::fn_listextendedproperty()” or string literals like “N'user'”...

When you create a pass-through query, Jet is disconnected from the loop, and the query is passed directly to the server. If your query is otherwise valid, it should work as pass-through. In the `row source´ property of a combo, you have no options to specify how the query should be treated, so Jet will take over in all cases.

Does that make sense?
(°v°)
0
 
Jim P.Commented:
Can we see the actual code that you are using?

You may have to wrap it in an executeSQL type command.
0
 
harfangCommented:
Jim, I believe there is no code. It looks as though the message is just the standard message when the `row source type´ is "Table/Query" and the `row source´ isn't a table or query...
(°v°)
0
 
Jim P.Commented:
In that case, he will probably have to hand code a recordset type object to resolve the issue. Agreed it is a pain, but you can't really go from the functions as the table object in an ADP.
0
 
Voodoo_chileAuthor Commented:
Yes, there is no code and it is really pain that I do not know where is this problem coming from. Therefore, I want to ask which situation will enconter such problems.
0
 
Jim P.Commented:
What version of Access?

If it is 2007+ the issue might be from the version and number of tables. I hate being a google monkey, but...

http://www.pcreview.co.uk/forums/access-2007-adp-and-sql-t2921641.html
0
 
Leigh PurvisDatabase DeveloperCommented:
I don't think it's the number of tables per se.
That harks back to another issue which is where the SubDatasheet of a table hasn't been specified.  In an MDB/ACCDB this is a potential problem when developing (it's a pain in the ass - as [Auto] causes full read of every table to look for a likely suspect.  The same check is performed on the server (I think it'll enumerate the contraints perhaps).
You don't want that - but it shouldn't cause this.
The extended property in question is 'MS_Description' (i.e. the description you give the table when in the Access UI).

You say there is no such extended property on your server table.  That should be fine.  But clearly Access is expecting it (there's nowhere it should be able to - the binary definition of the file would be the only place - which is crazy. That's what the server is for.)

Sadly importing all into a new ADP file might be the best starting point.  (It's such a boring solution don't you think?) There should be nothing wrong with the server having no extended properties on its tables.  (I don't know if the initial release of 2007 might have had a bug in that regard - I've not got it installed anywhere anymore - only SP2.) But I have messed about in general with extended properties and the effect on ADP and there should be none (all things being equal - a new ADP is a good shot at getting them equal again. :-s)
0
 
harfangCommented:
<off-topic>
Hello Leigh! Glad to see you still monitor the tough ones... (^v°)
</off-topic>
0
 
Voodoo_chileAuthor Commented:
I do agree the solution "importing all into a new ADP file might be the best starting point" and it can get rid of such queries from Access to MS SQL Server.

Thanks for all comments
0
 
Jim P.Commented:
This being an ADP means that the only native Access structures are the VB code modules and reports. The queries are turned into views and the update/insert queries are stored procedures. At least in AccXP.

This may have changed with Acc07/10 but I haven't played enough to know.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.