<div>
If a linked server is invovled, see this <a href="https://support.microsoft.com/en-us/topic/kb329964-fix-microsoft-sql-server-odbc-driver-cannot-retrieve-output-parameters-of-a-remote-stored-procedure-on-a-linked-server-fc876be4-ae3a-d4a6-4d57-18e77f609b2d" rel="ugc">KB329964</a>.<br />
<br />
Otherwise, what driver are you using? Consider using <a href="https://docs.microsoft.com/en-us/sql/relational-databases/native-client/applications/installing-sql-server-native-client?view=sql-server-ver15" rel="ugc">SQL Native Client 11</a> or the actual <a href="https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15" rel="ugc">ODBC 17</a>.
</div>


Retired IT Manager

Melvyn Shepherd

<div>
Hello,<br>You usually should install the SQL server native client, which belongs to the Server version you use. <br>This makes sure, that all statement passing the nativ client are correctly send to the server.<br>But an older client may not be aware about newer functionality. <br><br>The Nothwind database is an example / demonstation database and offered for each SQL version. <br>So it is possible, that there are strored procedures inside, which doesn't work in newer versions or vice versa.<br>So use the databse with belongs to the server version.<br><br>You can also play around with compatibility mode. Older stored procediures may work, if you set the database to the compatibility mode they are constructed for.&nbsp;
</div>


<div>
&nbsp;To pin down where the problem lies, see if you can open the table(s) that the subform is based on directly in the database window.<br><br>&nbsp;If so without error, then try opening the query it is based on (if it is).<br><br>Jim.
</div>


<div>
Jim,<br>I can open all the tables directly from within the Microsoft SQL Server Management Studio and all looks Ok.<br>I then moved to the query's in the Northwind database linked to the SQL Server starting with the Inventory query which is used by the Home page Inventory subform and it came up with the same error. Being new to this I cannot see the reason why. A number of the query's failed with the same error. <br>But what does the error mean?
</div>


<div>
&lt;&lt;I can open all the tables directly from within the Microsoft SQL Server Management Studio and all looks Ok. &gt;&gt;<br><br>OK, so then more than likely, the table connection string is invalid, but there still might be some things wrong outside of Access.<br><br>Access tables all have a connection string associated with them (their .Connect property), and this tells Access how to go after the data. &nbsp; You can best see this by opening the immediate window(Ctrl/G), and typing in:<br><br>? &nbsp;CurrentDB().TableDefs("NameOfTableHere").Connect <br><br>&nbsp;and then hitting a return.<br><br>&nbsp;The value of the connect property will be shown in the window.<br><br>&nbsp;Your next step in troubleshooting would be to try and create a DSN outside of Access and see if you can connect to the SQL Server instance.<br><br>&nbsp;If you cannot, then it's a problem with the:<br>&nbsp;1. Protocol being used to communicate with SQL<br>&nbsp;2. A firewall blocking the port.<br>&nbsp;3. SQL is not listening on the port you think it is, or it is closed off.<br><br>&nbsp;Create a DSN through the ODBC applet in control panel and use the "test" button there to see if you can get a connection. &nbsp; If you can, then the problem is inside Access, and probably just that the connect property is wrong.<br><br>&nbsp;and by the way, copy and paste what you get for the connection string to a comment here as that's probably where we are heading.<br><br>Jim.<br><br><br>
</div>


<div>
The error itself means that your used driver encounters an error when talking to your SQL Server Express. <br />
<br />
Sometimes it is caused by an erroneous driver instllation on the client side. Sometimes it is an faulty server installation. And sometimes it can be a network issue. Like defect NIC, cable or switch.
</div>


<div>
Again,<br>this database is a demonstration / example database, and it has to fit to the version you have installed. <br>Each SQL Server version has its own northwind database, and guess why.<br>If it doesn't work on your current SQL Server version, so just take the version, which is created for that version.&nbsp;<br>&nbsp; &nbsp;
</div>


<div>
Hi, thanks for all your help<br><br>In answer to the above suggestions:<br><br>I am currently on my third attempt of installing SQL Express, each time I have recovered my PC to a state before I initially installed SQL 2019.<br><br>I have converted the Access version of Northwind, using the SQL Import Export Utility. I have not directly loaded the SQL version of Northwind, because I am looking at using Access with SQL.<br><br>I am using a System DNS connecting via SQL Authentication, external to Access (Office 365).<br>It seems to work because, I can access the tables directly from Access. I have also tested using Windows Authentication, getting the same results for both. Test Connection works OK in both cases.<br><br>I have tried using the SQL Server Native Client 11, connected Ok!<br>Currently using the ODBC Driver 17 for SQL Server, Connected Ok!<br><br>Again, neither make any difference to the error I am getting.<br><br>Below is a couple of table connections from the intermediate window of Access VBA<br><div style="margin-left: 20px;">?CurrentDB().TableDefs("Products").Connect</div><div style="margin-left: 20px;">ODBC;DSN=Northwind;APP=Microsoft Office;DATABASE=Northwind;Regional=Yes;</div><div style="margin-left: 20px;">?CurrentDB().TableDefs("Inventory Transactions").Connect</div><div style="margin-left: 20px;">ODBC;DSN=Northwind;APP=Microsoft Office;DATABASE=Northwind;Regional=Yes;</div><br>When I load and run the Access Northwind database I tested the Queries and the following Access queries failed with the same error, the rest were Ok! :<br><br>&nbsp; &nbsp;Inventory &nbsp;(Used by the Home form.)<br>&nbsp; &nbsp;Inventory on Order (If I remove the sum column it works Ok)<br>&nbsp; &nbsp;Order Details Extended<br>&nbsp; &nbsp;Product Category Sales By Date<br>&nbsp; &nbsp;Invoice Data<br>&nbsp; &nbsp;Product Orders<br>&nbsp; &nbsp;Product Purchases<br>&nbsp; &nbsp;Product Sales By Category<br>&nbsp; &nbsp;Product Sales Qty by Employee and Date<br>&nbsp; &nbsp;Product Details Extended<br>&nbsp; &nbsp;Sales Analysis<br>&nbsp; &nbsp;Product Transactions&nbsp; (Union Query)<br><br>I have attached a copy of my SQL Northwind.bak (Backup) and a copy of my converted Access Northwind database.<br><a href="https://filedb.experts-exchange.com/incoming/2021/05_w20/1560534/Northwind.bak" rel="ugc">Northwind.bak</a> <a href="https://filedb.experts-exchange.com/incoming/2021/05_w20/1560535/Northwind.accdb" rel="ugc">Northwind.accdb</a><br><br><br>
</div>


Northwind.accdb

Northwind.bak

<div>
hmm, check the drivers. btw, in never have seen the regional parameter..
</div>


<div>
Hi ste5an<br><br>Sorry, not sure what your comment means.<br><br>Regards<br>Melvyn<br><br><br>
</div>


<div>
At first you should take a look at SSMA :&nbsp;<a href="https://www.microsoft.com/en-us/download/details.aspx?id=54255" rel="ugc">https://www.microsoft.com/en-us/download/details.aspx?id=54255</a><br>Its a tool specifically for transferring Access to SQL<br><br>
</div>


<div>
Hi John<br>Did as you mentioned in your comment, but it does not migrate the data across to SQL ?<br>Regards<br>Melvyn
</div>


<div>
Maybe this give a clue...<br><a href="https://social.msdn.microsoft.com/Forums/en-US/7996e47e-382c-4aaf-90dc-3892a1a6f1b0/quotrestricted-data-type-attribute-violationquot-error-and-sqls-2017-odbc-drivers-11-amp-13?forum=transactsql" rel="ugc nofollow">https://social.msdn.microsoft.com/Forums/en-US/7996e47e-382c-4aaf-90dc-3892a1a6f1b0/quotrestricted-data-type-attribute-violationquot-error-and-sqls-2017-odbc-drivers-11-amp-13?forum=transactsql</a><br><br>This points to some field types used in this database and problems according the drivers. <br>At least make sure, to use the most recent version of SQL Server als well as the client drivers, which may have to be downloaded via the SQL Server feature pack. <br><br>I'm not sure if this database is capable to be used as reference for your own database as far as you havn't used it as base to build your own.<br><br>Rather to use a sample database, you should use a copy of your working databasefor testing. <br><br>Also be aware about the fact, that<br>CurrentDB / CurrentDB().TableDefs <br>is DAO, a method with is marked as discontinued since more than 10 years.<br>Client VBA code should use ADO.
</div>


<div>
DAO works and is a little bit faster in some cases...
</div>


<div>
<div class="content wysiwyg-content fr-view">
I have moved the Northwind Database to an SQL 2019 Express Server, I followed the instructions as per Steve Bishops Access 2013 Advanced Course. After I have selected the default employee and before it displays the 'Home' form, I get the above ODBC --Call Failed error. The form then displays but the inventory subform is empty. I also get the same error if I click on 'View-Inventory' on the right-hand side of the form.<br>Has anybody got any ideas why this is happening?<br><br>ps. I have gone through the SQL database and carefully set all primary and foreign keys, I assigned a default value of zero for all conversion generated Bit fields, I also set the SQL Identity Specification properties for all Access Autonumber fields.<br><br>The reason for doing all this is because, I plan to move my Customer Order Entry Access Application to SQL
</div>
</div>



I have moved the Northwind Database to an SQL 2019 Express Server, I followed the instructions as per Steve Bishops Access 2013 Advanced Course. After I have selected the default employee and before it displays the 'Home' form, I get the above ODBC --Call Failed error. The form then displays but the inventory subform is empty. I also get the same error if I click on 'View-Inventory' on the right-hand side of the form.
Has anybody got any ideas why this is happening?
ps. I have gone through the SQL database and carefully set all primary and foreign keys, I assigned a default value of zero for all conversion generated Bit fields, I also set the SQL Identity Specification properties for all Access Autonumber fields.
The reason for doing all this is because, I plan to move my Customer Order Entry Access Application to SQL

Access 365 SQL 2019 EXPRESS Server, I keep getting the error ODBC-- Call Failed [Microsoft][SQL Server Native Client 11.0] Restricted datatype attribute violation (#0)

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

Databases

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Microsoft Access

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.