Solved

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

Posted on 2015-02-19
12
912 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now