Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


MS Access 2000\2002 Conversion to Office 2010

Converting MS Access 2000\2002 Format to 2010 Format

This article describes the steps to convert a .mdb file to a .accdb file. The operating system used was Windows 7 with Office 2010. Some of the items listed were anomalies that were encountered while debugging that were of interest.

Following several unsucessful attempts to convert the database directly, this series of steps worked for me.

1. Make a local copy of the .mdb file to use and then create the .accdb version in MS Access 2010 first. The copy of the mdb file will be used to import the objects into the 2007\2010 version of the database being created. The .mdb file may become corrupt during this process, so make a local copy that may be overwritten in case this happens.

2. Import each set of objects in this order:

   - Modules
   - Tables
   - Queries
   - Reports
   - Forms
   - Macros

3. Convert Sub procedures called out in form objects to Functions. Example: The textbox uses a Sub called subChange in the On Change event.

Public Sub SubChangeTxt(i As Integer)
 If i = 1 Then
  bchange = True
 Else
  bchange = False
 End If
End Sub

Access Text Box Properties

If a value is entered in the textbox, an Error is thrown.

Access Error Message

Change the Sub to a Function to fix the issue.

Public Function SubChangeTxt(i As Integer)
 If i = 1 Then
  bchange = True
 Else
  bchange = False
 End If
End Function

4. Add References to the .accdb file: Microsoft ActiveX Data Objects 2.5 Library – compatible with Office 2000 which is the original format of the database Include any Microsoft Office Object libraries that you might need. This database uses a call to create an Excel object in a module so we include the Microsoft Excel 14.0 Object Library.

References

5. Rename objects in Custom Reports. While Debugging the code behind the file, errors in the Detail_Format section of some of the POC reports pop up.

The field that the textbox txtSalesYTD points to can no longer be found. The textbox must be renamed to the field name (SalesYTD) to eliminate the problem.

Report TextBox

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

 If SalesYTD > 5000000 Then
  Text21.Value = "*"
 Else
  Text21.Value = ""
 End If
End Sub

6. Change the code of the for any files written to the root of C. Most likely, the operating system will be Windows 7 for an installation of Office 2010. Windows 7 does not allow the user to create a file on the root of C. (C:\). Create code to create a C:\temp folder in the event it is not there and place any files written into it instead of C:\.

This sample of code belongs to a form and creates an Excel file

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click

Dim strFile As String
Dim sFile As String
Dim strExt As String
Dim appExcel As Object
Dim wb As Object
Dim qdf As QueryDef
Dim sFolder as String
Dim fs As Object
Dim isthere As Boolean

 Set appExcel = GetObject("", "Excel.Application")
 If appExcel Is Nothing Then
  Set appExcel = CreateObject("Excel.Application")
 End If

 sFile = "C:\temp\vw_Employees"
 strExt = ".xls"
 strFile = sFile & strExt
 Set fs = CreateObject("Scripting.FileSystemObject")
 isthere = fs.FileExists(strFile)

 If isthere = True Then
  fs.deletefile strFile, True
 End If
 sFolder = "C:\temp"
 'create the folder, if it does not exist
 isthere = fs.folderExists(sFolder)
 If isthere = False Then
  fs.createFolder (sFolder)
 End If


Set qdf = CurrentDb.QueryDefs("qryEmployeeList")
qdf.SQL = "SELECT * FROM vw_Employees WHERE [EmployeeID] in(" & Forms!frmquerytest!Text1 & ")"

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
 "qryEmployeeList", strFile, True

  appExcel.Workbooks.Open (strFile)
  appExcel.Visible = True

 Set appExcel = Nothing
 Set fs = Nothing
 DoCmd.Close

Exit_cmdOK_Click:
 Exit Sub
Err_cmdOK_Click:
 MsgBox Err.Description
 Resume Exit_cmdOK_Click

End Sub