Dave's Technophorical Times

A blog about Microsoft's Technologies!
SharePoint :: MVC :: ASP.NET :: IIS :: SQL Server :: Visual Studio :: MS Access
  1. Dim cn As New ADODB.Connection
  2. Dim rs As ADODB.Recordset
  3. Dim strSQL As String
  5. strSQL = "Select * from Employees ORDER BY [LastName],[FirstName];"
  7. Set rs = New ADODB.Recordset
  9. With rs
  10.   .Source = strSQL
  11.   .ActiveConnection = CurrentProject.Connection
  12.   .CursorType = adOpenDynamic
  13.   .LockType = adLockOptimistic
  14.     .Open
  16.    Do While Not .EOF
  17.      Debug.Print UCase$(![LastName]) & ", " & ![FirstName]
  18.        .MoveNext
  19.    Loop
  20. End With
  22. rs.Close
  23. Set rs = Nothing


You SHOULD replace the Nz with an IIf(IsNull()) construct as it's prone to Errors for NOT reason.

See documentation for IIf and IsNull

When put together:Nz(expr, [valueifnull])
becomesIIf(IsNull(expr), valueifnull, valueifnotnull)

Default: Nz(tbl.A) => IIf(IsNull(tbl.A), '', tbl.A)
With fallback: Nz(tbl.A, tbl.B) => IIf(IsNull(tbl.A), tbl.B, tbl.A)

These are som items that need to be considered/done when upgrading from MS Access backend to SQL Server backend.

1. Replace # with single quote (') for Dates in SQL.
2. Add Timestamp to all SQL tables.
3. Add dbSeeChanges to all DAO CurrentDB.Execute SQL statements. dbFailOnError should become dbSeeChanges + dbFailOnError
4. Convert all MulitValue fields to flat fields. This usually like multi-select combo baoxes that have to be converted to Listboxes.
5. Convert all Attachment Fields to VarBinary subTables!

If you convert your MS Access backend to SQL Server backend you will lose the datagrid/datasheet filter dropdown list.

To get it back you need to go to File-Options-Current Database. Under the Filter Look Up you will see ODBC Fields. Just check that box, close and open the app. Then it's available.

One point to watch for is that if the data is read-only, it will not filter.




You created a DLL in Visual Studio 2010 so you can reference it in MS Access VBA.

It works fine in 32-bit Office but you get an error (error 429 activex component can't create object) when trying to run in 64-bit Office.

Solution: Unfortunatley non built-in ActiveX objects cannot be created in 64-bit Office and there is no direct work around for this.

I ran into a very strange error recently when I took over someone elses MS Access project. The so called developer did not use Option Explicit anywhere in the code so I added it into all modules and fixed all the misspelled variables and missing variables. Then the final Error I got when compiling was "Procedure Too Long"! That was odd as the routine in question was maybe 1000 lines long so it was NOT too long at all. After reviewing the code in more detail I decided to change some of the massive If-Then-Else blocks into Case Statements and just like magic, after the first one, I was able to get past this stupid error.

There must be some sort of limitation on the number of If-Else statements in VBA routines.

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

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"

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)

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.

The Blogger

Dave Stuart I'm a Developer with a passion for coding. I enjoy the challengers that come with the job! SharePoint is one of my expert areas along with .NET Web Development with MVC and good old MS Access VBA coding. I Blog so that I can remember how I did that way back when; PLUS all this stuff is searchable! I constantly study and run my own business, Dafran Inc. I have passed 22 Microsoft Exams since 1998 when I first jumped on the treadmill of knowledge. I hope that you enjoy this Blog as much as I enjoy updating it. All the very best from Calgary, Alberta, Canada. contact me at linkedin @ dafran.ca


<<  January 2019  >>

View posts in large calendar

Sign in