?
Solved

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

Posted on 2015-02-19
12
Medium Priority
?
1,588 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Suggested Courses

800 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