Solved

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

Posted on 2015-02-19
12
1,311 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
  • 7
  • 3
  • 2
12 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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 47

Expert Comment

by:Dale Fye (Access MVP)
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 500 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 47

Expert Comment

by:Dale Fye (Access MVP)
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

Industry Leaders: 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!

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

730 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