If you get this error for no apparent reason in MS Access then you probably just need to decompile the file like this
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "d:\My Documents\access\mayapp.mdb" /decompile
From here: https://support.microsoft.com/en-ca/help/214459/info-optional-feature-not-implemented-error-message
I got this error when running the code via MS Access VBA.
When you use ActiveX Data Objects (ADO) to pass parameters to a stored procedure, you may receive the following error:
Run-time error '2147217887 (80040e21)':
[Microsoft][ODBC SQL Server Driver] Optional Feature Not Implemented.
This error can occur if you attempt to set the TYPE of a parameter in an ADODB command object's parameters collection to a type that is not supported by the data provider.
For example, using SQL Server 7.0, create a stored procedure on the PUBS database:
CREATE PROCEDURE GetEmployeeInfo (@thedate datetime, @NumEmployees int output)AS
SELECT @NumEmployees = count(*) FROM EMPLOYEE WHERE hire_date < @thedate
GO
This stored procedure returns an output parameter of type int indicating the number of employees hired before a given date. The date is passed to the stored procedure as a parameter, and the number of employees is passed to the calling program as an output parameter.
Now create an ADO application to use the stored procedure. The example given is written in Visual Basic.
Private Sub MySubroutine()
Dim dbConnection As ADODB.Connection
Dim dbCommand As ADODB.Command
Set dbConnection = New ADODB.Connection
Set dbCommand = New ADODB.Command
Dim DSNNAME As String
Dim USERNAME As String
Dim PASSWORD As String
DSNNAME = "Pubs"
USERNAME = "sa"
PASSWORD = ""
dbConnection.Open DSNNAME, USERNAME, PASSWORD
dbCommand.ActiveConnection = dbConnection
Dim TheDate As Date
TheDate = Now
dbCommand.CommandText = "GetEmployeeInfo"
dbCommand.CommandType = adCmdStoredProc
dbCommand.Parameters.Append dbCommand.CreateParameter("@thedate", adDBDate, adParamInput, 0, TheDate)
dbCommand.Parameters.Append dbCommand.CreateParameter("@NumEmployees", adInteger, adParamOutput, 0)
dbCommand.Execute
Dim strTheString As String
strTheString = "There are " & dbCommand.Parameters("@numemployees") & " employees who were hired before " & TheDate
MsgBox strTheString, vbOKOnly, "Demonstration"
End Sub
When the sample code is run, it gives this error:
Run-time error '2147217887 (80040e21)':
[Microsoft][ODBC SQL Server Driver] Optional feature not Implemented.
This is because SQL Server does not support the adDBDate datatype. To correct this problem, change the datatype of the @theDate parameter to adDBTimeStamp.
In order to determine the number, names, types, and sizes of parameters needed in a stored procedure, use the Parameters.Refresh method of the command object. You can call this method during development of your application to determine the correct requirements for the stored procedure, then remove the expensive call to Parameters.Refresh after you have gathered the necessary data.
Recently I had to link to 4 SQL Azure tables that are in a Access Web App and found a way to always connect even though MS Access does not save the UserName/Password in the connection string.
The SQL Azure connection string is as follows, however note that when you view the connection string in desgin mode you will not see the UID/PWD part.
ODBC;Driver={SQL Server Native Client 11.0}; Server=[Server Name].database.windows.net; Database=[Database Name];
Uid=[Database Name]_ExternalWriter; Pwd=[Password]; Encrypt=Yes
So in order to get around this issue, create a Passthrough Query to one of the Linked SQL Azure tables and save the Uid/Pwd with the connection string. Make it some thing like SELECT TOP 1 * From Access.[Table Name]. Save the query as something like qptSQLAzureStartUp. Then Add this to the StartUp form's OnOpen Event: Dim lngCount As Long and the next line as lngCount=DCount("*", "qptSQLAzureStartUp"). Then you'll find that your Linked SQL Azure tables will open.
Option Compare Database
Option Explicit
Private Declare PtrSafe Sub keybd_event Lib "user32" ( _
ByVal bVk As Byte, _
ByVal bScan As Byte, _
ByVal dwFlags As Long, _
ByVal dwExtraInfo As Long)
Private Const VK_NUMLOCK = &H90
Private Const KEYEVENTF_KEYUP = &H2
Declare Function GetKeyState Lib "user32.dll" ( _
ByVal nVirtKey As Long) As Integer
Public Sub NUM_TOGGLE()
keybd_event VK_NUMLOCK, 1, 0, 0
keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
End Sub
Public Sub NUM_On()
If Not (GetKeyState(vbKeyNumlock) = 1) Then
keybd_event VK_NUMLOCK, 1, 0, 0
keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
End If
End Sub
Public Sub NUM_Off()
If (GetKeyState(vbKeyNumlock) = 1) Then
keybd_event VK_NUMLOCK, 1, 0, 0
keybd_event VK_NUMLOCK, 1, KEYEVENTF_KEYUP, 0
End If
End Sub
In Access 2010 I recently got an error when sorting a column or opening a Form that had a sorted column. It was a popup like you would see when you had a missing parameter in a query. It read qryQuery,FieldName.FileUrl and a place to enter the value. It had me stumped so I opened the form in design and removed any sorting. I was then able to open the form OK, but when I clicked on the first column to sort it gave me this error. BTW it was a Split Form with the Datagrid on the bottom and only the Form section on top was editable.
I did a manual Compact and Repair and the then the issue disappeared. Funnily enough this also happened in the compiled ACCDE. So to fix the ACCDE I just did a manual Compact and Repair ACCDB and THEN made the ACCDE. Then it worked fine.
I hope this helps someone else out there one day!
This trick works for a domainless environment AND when you want to connect to SQL Azure tables from you Access Web App.
The best solution is obviously to use Windows security.
If that is not suitable, here is a possible alternative trick, exploiting the fact that Access remembers all opened connections until Access is closed:
- copy the connect string of one of your tables
- create a passthru queries "ptqConnect" and enter any fast SQL statement in it, like
SELECT 1
- paste the connect string of into the PTQ Connect property, and make sure you add the
PWD=something;
in it.
- in the startup procedure of your app make sure you call that PTQ. Something like
DCount("*", "ptqConnect")
will do.
That's it. Since Access remembers opened connections until you close it, even if you close the db, your other tables will now open without any fuss, even if no password is stored in the linked tables Connect string.
If you don't want to expose the connection string that includes the PWD, you could as well initiate a connection from VBA Code and hide the code by delivering a ACCDE or just password protecting the code. The choice is all yours.
From here: http://stackoverflow.com/questions/9093266/save-password-for-odbc-connection-to-ms-sql-server-from-ms-access-2007
Private Sub cmdRefreshAllRecords_Click()
Dim strGuidID As String
Dim rst As DAO.Recordset
' This is NOT how to get the GUID
' The result will be "????????"
strGuidID = Me.ID
' This will get the GUID. StringFromGUID is a built in MS Access function
strGuidID = StringFromGUID(Me.ID)
' You can also get the GUID from the actual Records Field
strGuidID = Me.Recordset.Fields("ID")
Me.Requery
Set rst = Me.RecordsetClone
If rst.RecordCount > 0 Then
' Here we must convert the ID field using the StringFromGUID field.
' ID is the name of the field in the Recordset/Underlaying Form
rst.FindFirst "StringFromGUID(ID) = " & strGuidID
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub
PROBLEM:
Some users of an ACCDE file get this error message:
"The database cannot be opened because the VBA project contained in it cannot be read."
Here are the details of what I'm doing:
1. I Make ACCDE from MS Access 2010
2. I put ACCDE on the network
3. Users try to load the ACCDE.
4. Some users are successful.
5. Some users get this message:
"The database cannot be opened because the VBA project contained in it cannot be read."
All users have the same install procedure:
1. Project Referenced DLLs
2. MS Access 2010 Runtime
Notes:
1. The ACCDB file opens and runs properly in Access Development Environment.
2. The ACCDE worked at some point on the user's machine that it now not working.
3. Some of the previous versions now do not work either.
4. We have tried decompiling , compact and repair, and starting a new DB and copying the objects in.
SOLUTION:
You have compiled the MS Access application with a version that is higher than the one used by the person trying to open the ACCDE file.
This could be as simple as compiling with MS Office 14.0.7151.5001 and the users getting the error is running 14.0.4760.1000. Onece the user is updated to 14.0.7151.5001 then the applicatioin will work again.
https://support.microsoft.com/en-us/kb/2533794
In MS Access, sometimes you can get a Write Conflict when trying to update a table that is linked to SQL Server. Following is two suggestions on how to resolve the issue.
1. If the SQL Server table has a bit column, then set a default of 0 or 1 and then do not allow NULLS.
2. If the Table has a nvarchar(MAX) or very large field like nvarchar(4000) then try adding a column of datatype "timestamp".