SQL to Access ODBC link issue

[Menes]

Hi all.

Having a bit of a problem with a form in Access. I have the table setup in SQL and have linked in Access via an ODBC.

I create a form using the fields to enter the data into however I'm getting errors when I use it that says:

ODBC call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]The EXCUTE permission was denied on the objects 'sp_execute', database 'mysqlsystemresource', schema 'sys' (#299)

The field types I have in SQL are:
[ID] [int] IDENTITY(1,1)NOTNULL,

 [nvarchar][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]255[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][Person_Name] [nvarchar][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]255[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][Job_Role] [nvarchar][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]255[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][Organisation_name] [nvarchar][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]255[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][Em_address] [nvarchar][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]255[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][telephone_number] [nvarchar][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]255[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][DS_MH] [bit] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][DS_IA] [bit] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][DS_CN] [bit] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][DS_COM] [bit] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][CAT_IND] [bit] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][CAT_TRU] [bit] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][CAT_COMM] [bit] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][CAT_DH] [bit] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][CAT_CH] [bit] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][CAT_SYS] [bit] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][CAT_OTHER] [bit] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][ACTIVE_CONTACT] [bit] [/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL,[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][INACTIVE_CONTACT_DATE] [nvarchar][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]255[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]NULL[/COLOR][/SIZE][/COLOR][/SIZE]
 
[SIZE=2][SIZE=2][COLOR=black]Which access converts to:[/COLOR][/SIZE][/SIZE]
 
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080][COLOR=black][ID] Autonumber[/COLOR][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][Code] text[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][Person_Name] text[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][Job_Role] text[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][Organisation_name] text[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][Em_address] text[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][telephone_number] text[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][DS_MH] yes/no[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][DS_IA] yes/no[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][DS_CN] yes/no[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][DS_COM] yes/no[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][CAT_IND] yes/no[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][CAT_TRU] yes/no[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][CAT_COMM] yes/no[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][CAT_DH] yes/no[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][CAT_CH] yes/no[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][CAT_SYS] yes/no[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][CAT_OTHER] yes/no[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][ACTIVE_CONTACT] yes/no[/COLOR][/SIZE][SIZE=2][SIZE=2][COLOR=black],[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=black][INACTIVE_CONTACT_DATE] text[/COLOR][/SIZE]
 
[SIZE=2][COLOR=black]Can anyone help me with this as i'm getting something wrong. It could I'm using field types that Access is having trouble with and can quite easily change them if needed. I need it in SQL as some of the fields are pulled from other tables in SQL and really I'm just trying to create an input screen for contacts. I do have a primary key defined when linking the tables on ID.[/COLOR][/SIZE]
[COLOR=#000000][/COLOR] 
[COLOR=#000000]Using Access 2003 and SQL 2005.[/COLOR]
 
[SIZE=2][COLOR=black]Any help is much appreciated.[/COLOR][/SIZE]
 
[SIZE=2][COLOR=black]Thanks[/COLOR][/SIZE]
[/COLOR][/SIZE][/COLOR][/SIZE]

 

[boblarson]

Do NOT use NULL bit fields. Access has trouble with SQL Server 2005 and null bit fields. Change them to Small Int or Tiny Int if you must have nulls. Otherwise set the Bits to default to 0.

 

[Menes]

Thanks a lot for that.

I've set them to not allow NULL anymore and put a default value of 0 in "Default value or binding" in SQL however when the I make the link it doesn't pull the default value through and access won't let me make the change as "This property can't be modified in linked tabels".

I'll do a search and see if anyone has found a solution for it but at least I know the problem now.

Thanks again.

 

[boblarson]

If you have ANY records in the table currently you MUST run an update query in SQL Server Management Studio to update all of the null bit fields to 0's before it will work in Access.

 

[boblarson]

Oh, and you don't need to set the default in Access. It being in the table on SQL Server is fine regardless of what it says in Access. Creating a new record (row of data) your fields will be set to 0 by SQL Server itself.

 

[Menes]

Thanks Bob.
As this is the start of the project and still setting it up I just deleted the table and recreated it as suggested. I've put the create script that was used below however I'm still getting the problem with access having no default value pulled through when I link the table.

Thanks again

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Contacts_Database](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Org_Code] [nvarchar](255) NULL,
[Person_Name] [nvarchar](255) NULL,
[Job_Role] [nvarchar](255) NULL,
[Organisation_name] [nvarchar](255) NULL,
[Em_address] [nvarchar](255) NULL,
[telephone_number] [nvarchar](255) NULL,
[DS_MH] [bit] NOT NULL CONSTRAINT [DF_Contacts_Database_DS_MH] DEFAULT 0,
[DS_IA] [bit] NOT NULL CONSTRAINT [DF_Contacts_Database_DS_IA] DEFAULT 0,
[DS_CN] [bit] NOT NULL CONSTRAINT [DF_Contacts_Database_DS_CN] DEFAULT 0,
[DS_COM] [bit] NOT NULL CONSTRAINT [DF_Contacts_Database_DS_COM] DEFAULT 0,
[CAT_IND] [bit] NOT NULL CONSTRAINT [DF_Contacts_Database_CAT_IND] DEFAULT 0,
[CAT_TRU] [bit] NOT NULL CONSTRAINT [DF_Contacts_Database_CAT_TRU] DEFAULT 0,
[CAT_COMM] [bit] NOT NULL CONSTRAINT [DF_Contacts_Database_CAT_COMM] DEFAULT 0,
[CAT_DH] [bit] NOT NULL CONSTRAINT [DF_Contacts_Database_CAT_DH] DEFAULT 0,
[CAT_CH] [bit] NOT NULL CONSTRAINT [DF_Contacts_Database_CAT_CH] DEFAULT 0,
[CAT_SY] [bit] NOT NULL CONSTRAINT [DF_Contacts_Database_CAT_SYS] DEFAULT 0,
[CAT_OTHER] [bit] NOT NULL CONSTRAINT [DF_Contacts_Database_CAT_OTHER] DEFAULT 0,
[ACTIVE_CONTACT] [bit] NOT NULL CONSTRAINT [DF_Contacts_Database_ACTIVE_CONTACT] DEFAULT 0,
[INACTIVE_CONTACT_DATE] [nvarchar](255) NULL
) ON [PRIMARY]

 

[boblarson]

Access won't show you the default value when linked. But it will occur when you add a new record (in SQL Server it doesn't add the values immediately when you start a new record like Access does it. It does it when the record is about to be committed).

 

[Menes]

Sorry Bob I missed your previous reply while I was posting.

Despite having the default value set it still throws up the same ODBC error.

 

[boblarson]

Is your SQL Server instance set up to use Windows Authentication or Mixed Mode?

Also, you might want to try using the SQL Native Client ODBC Driver.

And last - I just noticed that the error you mentioned says something about not being able to execute a stored proc of sp_execute so the last question is - is this an Access MDB file or an ADP (Access Data Project)?

 

[Menes]

Bob,
Thanks for you help. You are indeed right in that it is an issue with the autonumber. Where I've put [ID] [int] IDENTITY(1,1)NOTNULL,
it throws up the error on this. If I recreate the table with out the autonumber it works perfectly. I was planning on using this as the primary key by having it increment by 1 each time. However when I put the record in all the bit values change from NULL to 0, however it just reads autonumber in the ID field. I assume it's because SQL is providing the number and so because access is not putting the auto number rather SQL, it doesn't like it as it is a not null field.

 

[boblarson]

We use SQL auto numbers in our tables. They are set for us with:

DataType = Int
AllowNulls = No
Is Identity = Yes
Identity Increment = 1
Identity Seed = 1

Now, you may find, as I had said, that using TinyInt instead of bit fields is going to be more workable.