• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1947
  • Last Modified:

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

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
skull52
Asked:
skull52
  • 7
  • 3
  • 2
1 Solution
 
Dale FyeCommented:
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
 
Nick67Commented:
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
 
skull52Author Commented:
Query works just fine in SQL, no functions  of conversion in the query
0
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.

 
skull52Author Commented:
if i exclude the email column in the query it works just fine, I will do one with just the email
0
 
skull52Author Commented:
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
 
skull52Author Commented:
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
 
skull52Author Commented:
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
 
Dale FyeCommented:
Can you simply open the linked table, or do you get the error when you try that as well?
0
 
Nick67Commented:
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
 
skull52Author Commented:
Dale, Yes I can open the linked file with no issues.

Nick, I will try that
0
 
Dale FyeCommented:
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
 
skull52Author Commented:
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.

  • 7
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now