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
If a value is entered in the textbox, an Error is thrown.
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.
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.
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