?
Solved

Access Error: "can't sql_variant_property"

Posted on 2011-05-09
19
Medium Priority
?
865 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:Voodoo_chile
  • 5
  • 4
  • 4
  • +2
16 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 35730705
so tbl_adtLog is in your SQL Server database and owned by dbo?

have you tried Null as the first parameter?
0
 

Author Comment

by:Voodoo_chile
ID: 35812712
tbl_adtLog is owned by dbo schema.
0
 

Author Comment

by:Voodoo_chile
ID: 35858016
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Expert Comment

by:harfang
ID: 35876205
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
 

Author Comment

by:Voodoo_chile
ID: 35876211
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
 
LVL 58

Expert Comment

by:harfang
ID: 35876233
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 35876280
Can we see the actual code that you are using?

You may have to wrap it in an executeSQL type command.
0
 
LVL 58

Expert Comment

by:harfang
ID: 35876342
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 35876361
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
 

Author Comment

by:Voodoo_chile
ID: 35876397
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 35876422
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 35879681
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
 
LVL 58

Expert Comment

by:harfang
ID: 35880292
<off-topic>
Hello Leigh! Glad to see you still monitor the tough ones... (^v°)
</off-topic>
0
 

Author Comment

by:Voodoo_chile
ID: 35883253
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 35883712
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
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 2000 total points
ID: 35889631
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question