Microsoft Knowledge Base Wiki

The Access 2002 Runtime Update includes
stability improvements that were developed as a result of user input from both
the Error Reporting Tool in Microsoft Office XP and Microsoft Product Support
feedback.

Public updates for the Access 2002 Runtime that are released
after this update will require the installation of this update.

This
article describes how to download and how to install the Access 2002 Runtime
Update: KB813617.

Note A later version of the Access 2002 Runtime is
available. For additional information,
click the following article number to view the article in the Microsoft
Knowledge Base:

How to Download and How to Install the Update

Important Before you install this update, make sure that you meet the
following requirements:

• Microsoft Windows Installer 2.0

  Before you install
  this update, you must install Windows Installer 2.0 or later. For additional
  information about this requirement, see the “Windows Installer Update
  Requirements” section of this article.

• Microsoft Access 2002 Runtime

Updating Existing Run-time Applications

You can use either of the following two options:

• Direct Users to the Microsoft Office Product Updates Web
  Site
• Distribute the Access 2002 Runtime Client Update

Direct Users to the Microsoft Office Product Updates Web Site

One option for updating computers is to have each user
move to the Microsoft Office Products Updates Web site and then download the
update. If you do not know what updates users may have applied, this option is
an efficient way to make sure that all users have the most current version of
the software and any new security updates. To use this option, make sure that
each user follows these steps:

Note After you install the update, you cannot remove it. To revert to
an installation before the update was installed, you must remove the Access
run-time application and then install it again from the original source.
Remember to back up any data files before you remove the Access run-time
application.

1. Visit the Microsoft Office Products Updates Web site:
2. Click Check for Updates.
3. Click to check Access 2002 Runtime Update:
   KB813617 and then click Start Installation.
4. Click to check the I accept the terms of the
   license agreement check box and then click
   Next.
5. Click Next two times.
6. Click Finish.

Distribute the Access 2002 Runtime Client Update

Another option for updating computers is to download and
to distribute the client update to users. To use this option, follow these
steps:

Note After you install the update, you cannot remove it. To revert to
an installation before the update was installed, you must remove the Access
run-time application and then install it again from the original source.
Remember to back up any data files before you remove the Access run-time
application.

1. Download the following file from the Microsoft Download
   Center:
2. Distribute the file
   Access2002-runtime-kb813617-client-enu.exe to the users.
3. The users must open Microsoft Windows Explorer,
   double-click Access2002-runtime-kb813617-client-enu.exe, and
   then follow the steps to complete the installation.

How to Determine Whether the Update Is Installed

The update contains updated versions of the following
files:

    File name      Version
    -----------------------
    Graph.exe     10.0.4109.0
    Intldate.dll  2.0.0.1930
    Msaccess.exe  10.0.4302.0
    Msaexp30.dll  10.0.4016.0
    Mscal.ocx     10.0.0.4024
    MSCDM.dll     10.0.0.3506
    Mscomctl.ocx  6.1.95.45
    Msdmeng.dll   8.0.534.0
    Msdmine.dll   8.0.534.0
    Msdmine.rll   8.0.534.0
    Msmdcb80.dll  8.0.384.0
    Msmdgd80.dll  8.0.534.0
    Msmdun80.dll  2000.80.382.0
    Mso.dll       10.0.4219.0
    Msolap80.dll  8.0.534.0
    Msolap80.rll  8.0.384.0
    Msolui80.dll  8.0.0.382
    Msowc.dll     9.0.0.6430
    Olapuir.rll   8.0.382.0
    Owc10.dll     10.0.4109.0
    Snapview.ocx  10.0.4115.0
    Soa.dll       10.0.4115.0

To determine if the update is installed on your computer,
follow these steps:

Note Because there are several versions of Microsoft Windows, the
following steps may be different on your computer. If they are, see your
product documentation to complete these steps.

1. Click Start, and then click
   Search.
2. In the search results pane under Search
   Companion, click All files and folders.
3. In the All or part of the file name box,
   type Msaccess.exe, and then click
   Search.
4. In the list of files, right-click
   Msaccess.exe, and then click Properties.
5. Click the Version tab, if the version
   number is 10.0.4302.0 or later, the update is installed on your computer.

Note If the Access 2002 Runtime Update: KB813617 is already installed
on your computer, you may receive the following error message when you try to
install the Access 2002 Runtime Update: KB813617:

Windows Installer Update Requirements

To install the update that is described in this article requires
Windows Installer 2.0 or later. Both Microsoft Windows XP and Microsoft Windows
2000 Service Pack 3 (SP3) include Windows Installer 2.0 or later.

For
additional information about how to install the latest version of Windows
Installer for Microsoft Windows 95, Microsoft Windows 98, and Microsoft Windows
Millennium Edition (Me), visit the following Microsoft Web site:

For additional information about how to install the latest version
of Windows Installer for Microsoft Windows NT 4.0 and Windows 2000, visit the
following Microsoft Web site:

Issues That Are Fixed by the Update

The Access 2002 Runtime Update: KB813617 updates the Access 2002
Runtime files to a version level that is equal to the level of the same files
that are updated by Office XP Service Pack 2 (SP-2).
For additional information about Office XP Service
Pack 2 and what Access 2002 Runtime issues are fixed by Office XP Service Pack
2, click the following article number to view the article in the Microsoft
Knowledge Base:

1. Open the form in Design view.
2. In
   Microsoft Office Access 2003, click Code on
   the View menu.

   In
   Microsoft Office Access 2007, click
   View Code in the Tools group on the Design tab.

3. On the Debug menu, click Compile
   Database Name.

   You receive the
   following error message:

   Compile error: Ambiguous name
   detected: Procedure Name.

4. Notice the procedure name, and then click
   OK.
5. In the code, find the other occurrences of the
   Procedure Name procedure, where
   Procedure Name produced the compile error mentioned
   in step 3.
6. Select and then delete the unwanted procedure.
7. On the Debug menu, click Compile
   Database Name.
8. On the File menu, click
   Close.

Note Before you start these steps, you must backup your Excel
spreadsheet.

1. Open the source spreadsheet in Excel.
2. In the spreadsheet, identify the fields that have data
   values of mixed data types.
3. Select the identified columns and then format the cells in
   the spreadsheet as Text.
4. Create a macro in Excel that contains the following
   procedure:

   Sub Addspace()
   
   Dim cell As Object
   
   For Each cell In Selectioncell.Value = " " & cell.Valuecell.Value = Right(cell.Value, Len(cell.Value) - 1)Next
   
   End Sub
   
   

5. Select the fields in the spreadsheet that contain data
   values of mixed data types. Run the macro that you created in step 4.

   Note If an error occurs when you run the macro on the problem cells,
   ignore the error.

6. Save the spreadsheet.
7. Open your database in Access.
8. From your database, link to the source spreadsheet in
   Excel.

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

For more information about how to create a
macro in Microsoft Excel, click Microsoft Excel Help on the
Help menu, type Create a macro in the
Office Assistant or the Answer Wizard, and then click Search
to view the topic.
For more information about how to run a macro in Microsoft
Excel, click Microsoft Excel Help on the Help
menu, type Run a macro in the Office Assistant or the
Answer Wizard, and then click Search to view the topic.

Article Translations

When you link an Excel spreadsheet to an Access database, and
the columns of the Excel spreadsheet contain more than 255 characters, the
columns are mapped to Memo data types, and the Format property of the Memo field is set to @. Therefore, Access treats
the Memo field as a text field, and you can view only 255 characters of
data.

However, when you import an Excel spreadsheet that has columns
that contain more than 255 characters, the columns are mapped to a Memo field
with no specific format. Therefore, you can view the complete data in the
field.

Description of the Technique

The samples in this article import Excel data by using:

• SQL Server Data Transformation Services (DTS)
• Microsoft SQL Server 2005 Integration Services (SSIS)
• SQL Server linked servers
• SQL Server distributed queries
• ActiveX Data Objects (ADO) and the Microsoft OLE DB Provider for SQL Server
• ADO and the Microsoft OLE DB Provider for Jet 4.0

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:

• Available instance of Microsoft SQL Server 7.0 or Microsoft SQL Server 2000 or Microsoft SQL Server 2005
• Microsoft Visual Basic 6.0 for the ADO samples that use Visual Basic

Portions of this article assume that you are familiar with the following topics:

• Data Transformation Services
• Linked servers and distributed queries
• ADO development in Visual Basic

Samples

Import vs. Append

The sample SQL statements that are used in this article demonstrate Create Table queries that import Excel data into a new SQL Server table by using the SELECT…INTO…FROM syntax. You can convert these statements to Append queries by using the INSERT INTO…SELECT…FROM syntax while you continue to reference the source and destination objects as shown in these code samples.

Use DTS or SSIS

You can use the SQL Server Data Transformation Services (DTS) Import Wizard or the SQL Server Import and Export Wizard to import Excel data into SQL Server tables. When you are stepping through the wizard and selecting the Excel source tables, remember that Excel object names that are appended with a dollar sign ($) represent worksheets (for example, Sheet1$), and that plain object names without the dollar sign represent Excel named ranges.

Use a Linked Server

To simplify queries, you can configure an Excel workbook as a linked server in SQL Server.

For additional information, click the article number below
to view the article in the Microsoft Knowledge Base:

The following code imports the data from the Customers worksheet on the Excel linked server “EXCELLINK” into a new SQL Server table named XLImport1:

SELECT * INTO XLImport1 FROM EXCELLINK...Customers$
				

You can also execute the query against the source in a passthrough manner by using OPENQUERY as follows:

SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
    'SELECT * FROM Customers$')
				

Use Distributed Queries

If you do not want to configure a persistent connection to the Excel workbook as a linked server, you can import data for a specific purpose by using the OPENDATASOURCE or the OPENROWSET function. The following code samples also import the data from the Excel Customers worksheet into new SQL Server tables:

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:testxltest.xls;Extended Properties=Excel 8.0')...Customers$

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:testxltest.xls', Customers$)

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:testxltest.xls', 'SELECT * FROM Customers$')
				

Use ADO and SQLOLEDB

When you are connected to SQL Server in an ADO application by using Microsoft OLE DB for SQL Server (SQLOLEDB), you can use the same “distributed query” syntax from the Using Distributed Queries section to import Excel data into SQL Server.

The following Visual Basic 6.0 code sample requires that you add a project reference to ActiveX Data Objects (ADO). This code sample also demonstrates how to use OPENDATASOURCE and OPENROWSET over an SQLOLEDB connection.

    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;Data Source=;" & _
        "Initial Catalog=;User ID=;Password=
"

    'Import by using OPENDATASOURCE.
    strSQL = "SELECT * INTO XLImport6 FROM " & _
        "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
        "'Data Source=C:testxltest.xls;" & _
        "Extended Properties=Excel 8.0')...Customers$"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and object name.
    strSQL = "SELECT * INTO XLImport7 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:testxltest.xls', " & _
        "Customers$)"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and SELECT query.
    strSQL = "SELECT * INTO XLImport8 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:testxltest.xls', " & _
        "'SELECT * FROM Customers$')"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing
				

Use ADO and the Jet Provider

The sample in the preceding section uses ADO with the SQLOLEDB Provider to connect to the destination of your Excel-to-SQL import. You can also use the OLE DB Provider for Jet 4.0 to connect to the Excel source.

The Jet database engine can reference external databases in SQL statements by using a special syntax that has three different formats:

• Full path to Microsoft Access database.Table Name
• ISAM Name;ISAM Connection String.Table Name
• ODBC;ODBC Connection String.Table Name

This section uses the third format to make an ODBC connection to the destination SQL Server database. You can use an ODBC Data Source Name (DSN) or a DSN-less connection string:

DSN:
    odbc;DSN=;UID=;PWD=


DSN-less:
   odbc;Driver=SQL Server;Server=;Database=;
       UID=;PWD=

				

The following Visual Basic 6.0 code sample requires that you add a project reference to ADO. This code sample demonstrates how to import Excel data to SQL Server over an ADO connection by using the Jet 4.0 Provider.

    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:testxltestt.xls;" & _
        "Extended Properties=Excel 8.0"

    'Import by using Jet Provider.
    strSQL = "SELECT * INTO odbc;Driver=SQL Server;" & _
        "Server=;Database=;" & _
        "UID=;PWD=
.XLImport9 " & _
        "FROM Customers$"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing
				

You can also use this syntax, which the Jet Provider supports, to import Excel data into other Microsoft Access databases, indexed sequential access method (ISAM) (“desktop”) databases, or ODBC databases.

Troubleshooting

• Remember that Excel object names that are appended with a dollar sign ($) represent worksheets (for example, Sheet1$) and that plain object names represent Excel named ranges.
• In some circumstances, especially when you designate the Excel source data by using the table name instead of a SELECT query, the columns in the destination SQL Server table are rearranged in alphabetical order.For additional information about this problem with the Jet Provider, click the article number below
  to view the article in the Microsoft Knowledge Base:
  299484 
  
  (http://support.microsoft.com/kb/299484/EN-US/
  )
  PRB: Columns Are Sorted Alphabetically When You Use ADOX to Retrieve Columns of Access Table
• When the Jet Provider determines that an Excel column contains mixed text and numeric data, the Jet Provider selects the “majority” data type and returns non-matching values as NULLs.For additional information about how to work around this problem, click the article number below
  to view the article in the Microsoft Knowledge Base:
  194124 
  
  (http://support.microsoft.com/kb/194124/EN-US/
  )
  PRB: Excel Values Returned as NULL Using DAO OpenRecordset

For additional information about how to transfer data into Excel, click the article numbers below
to view the articles in the Microsoft Knowledge Base:

To
register Adodb.dll in the GAC, follow these steps:

1. Click Start and then click
   Run.
2. In the Run dialog box, type the following
   command, and then click OK:

   
   C:WINDOWSMicrosoft.NETFrameworkv1.0.3705gacutil /i C:Program
   FilesMicrosoft.NETPrimary Interop Assembliesadodb.dll

Article Translations

For example,
you may export the following table:

UNITS
23411
111111111
1121212

When you then import and view the data in dBase IV, the
data appears similar to the following:

UNITS
23411.00000
111111111.00000
1121212.00000

Note When you create a number field in a table in Access, you use the
FieldSize property to control the amount of space that is allocated for a
particular field. For number fields, you can choose one of the following numeric types from a list:

• Byte
• Integer
• Long Integer
• Single
• Double
• ReplicationID
• Decimal

By default, the setting for number fields is Long Integer. The Long
Integer value stores whole numbers that range from about –2 billion to +2 billion.
Or, you can choose the Double value to store numbers with decimals.

Method 1: Modify the database file in dBase

Modify the design of the database file after it has been added to
the dBase catalog. For example, set the Dec column to 0 (zero). For additional
information about designing database files in dBase, see the dBase
documentation.

Method 2: Use Microsoft Office Excel to format the data

Use Excel to format the data in the table that you
exported. To do this, follow these steps:

1. In Excel, open the table that you exported as type dBase
   IV (*.dbf) from Access.
2. Format the cells that are not correctly formatted. To do
   this, select the cells, and then click Cells on the
   Format menu.
3. After you have formatted the cells, export the spreadsheet
   as a dBase IV file. To do this, follow these steps:
   1. On the File
      menu, click Save As.
   2. In the Save as
      type box, click DBF 4 (dbase IV) (*.dbf), and then click Save.

Method 3: Use sample code to format the data

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

You can use the following code sample to automate
the process of formatting the data. The sample will save a table as a text
file. Then, it will automate Excel to open the text file and then save it in dBase IV
format.

Note You must change the file names in this code sample to match your
file names.

Sub exportFormat() 

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook 

Const SAVETEXT = "C:testValues.txt"
Const SAVEDBF = "C:testDBF.dbf" 

' Save the table as a text file.
DoCmd.TransferText acExportDelim, , "Table1", SAVETEXT, True 

' Set a reference to the Application object.
Set xlApp = CreateObject("Excel.Application") 

' Set a reference to the Workbook object.
Set xlBook = xlApp.Workbooks.Open(SAVETEXT, , , 2) 

' Save the file to dBase IV format.
xlBook.SaveAs Filename:=SAVEDBF, FileFormat:=xlDBF4
xlBook.Close savechanges:=False 

xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing 

End Sub