[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

The Setting you entered isn't valid for this property Error

Posted on 2015-02-19
12
Medium Priority
?
1,781 Views
Last Modified: 2015-02-20
I am using Access 2013 and I have a query that uses a linked SQL server 2008R2 table with roughly 11 columns, when I open the query I get "The Setting you entered isn't valid for this property"  error I have narrowed it down to the email field causing  error. the SQL data type for the email is nvarchar(50). the query has no property values set, it is driving me nuts.
0
Comment
Question by:skull52
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
12 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40619324
Can you run the same query from SQL Server?  If so, what do you get when you try that?

Are you using any functions of conversion in your query?  Can you post your SQL?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40619439
Emails are someone@somewhere.com
SQL variables are @SomeParameterOrVariable
That may squirrel things, BUT

I have a query that uses a linked SQL server 2008R2 table

Ok, can you create a query with all the columns except the email string?
Can you create a query that gets only the email string?
0
 

Author Comment

by:skull52
ID: 40619447
Query works just fine in SQL, no functions  of conversion in the query
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:skull52
ID: 40619452
if i exclude the email column in the query it works just fine, I will do one with just the email
0
 

Author Comment

by:skull52
ID: 40619457
This is the Access SQL

SELECT dbo_dealer_trade_expo.accountnumber, dbo_dealer_trade_expo.businessname, dbo_dealer_trade_expo.address, dbo_dealer_trade_expo.address2, dbo_dealer_trade_expo.city, dbo_dealer_trade_expo.state, dbo_dealer_trade_expo.zip_code, dbo_dealer_trade_expo.phone, dbo_dealer_trade_expo.fax, dbo_dealer_trade_expo.email, dbo_dealer_trade_expo.salesrep
FROM dbo_dealer_trade_expo;

Open in new window

0
 

Author Comment

by:skull52
ID: 40619922
It is definitely the email column, I created a query with just the email and it produced the error, but after you click OK on the error it opens the query
0
 

Author Comment

by:skull52
ID: 40619962
I cleared the data from the table and it still throws the error so it is not data related. How do I find what property is causing the issue
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40620001
Can you simply open the linked table, or do you get the error when you try that as well?
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 40620002
Aieee!

Not fun.

Can you create a new mdb file and link to the SQL Server?
Then set up the query in that file and run it.
That'll see whether something odd is going on in your Access file
Try that file on a different machine to eliminate the problem being some weird setting on the Access machine

After that, It's poking the server with a stick
Screenshots are good!
0
 

Author Comment

by:skull52
ID: 40620014
Dale, Yes I can open the linked file with no issues.

Nick, I will try that
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40620040
So, opening the linked table works fine, but a simple query:

SELECT email from dbo_dealer_trade_expo
WHERE email IS NOT NULL

generates this error?

What data type has Access assigned to the [eMail] field in the linked table?

How are you creating the linked table?  Are you using a DSN connection or a DSN-less connection?

Try it with a DSN-less connection. This subroutine should allow you to create a linked table via a DSN-less connection, assuming you are using Windows authentication on the SQL Server.  

Note: You may have to change the driver

Public Sub LinkSQLServerTable(TableName As String, _
             Optional dbName As String = "YourDatabaseName", _
             Optional ServerName as string = "YourServerName")

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    
    Set db = CurrentDb
    Set tdf = db.CreateTableDef(TableName)
    tdf.SourceTableName = TableName
    tdf.Connect = "ODBC;DRIVER=SQL Server Native Client 10.0;" _
               & "SERVER=" & ServerName & ";" _
               & "Trusted_Connection=Yes;" _
               & "DATABASE=" & dbName & ";"
    db.TableDefs.Append tdf
    db.TableDefs.Refresh
    Set tdf = Nothing
    Set db = Nothing
        
End Sub

Open in new window

0
 

Author Comment

by:skull52
ID: 40621154
Nick, creating a completely new database and linking the table, then creating the query worked, I should have thought of that myself, thanks.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

656 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