XL: How to Create MS Access Database from MS Excel Using DAO

ID: Q151566

The information in this article applies to:

SYMPTOMS

In some cases, you may want to create a Microsoft Access database from a Microsoft Excel for Windows 95 version 7.0 workbook, but are not able to use Access Links. While the preferred method of moving a Microsoft Excel workbook into Microsoft Access is to use Access Links, you can also use data access object (DAO).

CAUSE

The reasons for not being able to use Access Links include (but are not limited to) the following:

WORKAROUND

You can use data access objects (DAO)to create any version of a Microsoft Access database. Although this method is not as complete as Access Links, you can use it to create a Microsoft Access database from a Microsoft Excel workbook. This method should be used only if you are experienced with Visual Basic for Applications and are familiar enough with Microsoft Access databases to be able to edit the tables that are created by this code.

Some things that you may need to change are the data types of each field and whether or not you want indexing.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/

The code in this article will go through each worksheet in a Microsoft Excel version 7.0 workbook and create a Microsoft Access table as specified in the code. There are several requirements for this code to function properly.

NOTE: These requirements are similar to what would be required if you were transferring data using Access Links.

Please ensure that the workbook used has a list on each worksheet consisting of at least two columns.

The requirements are as follows:

The subroutine described below will do the following: ErrorHandler:
     Select Case Err
        Case 3204   ' Database already exists.
           Message = "There has been an error creating the database." & _
                Chr(10) & _
                Chr(10) & "Error Number: " & Err & _
                Chr(10) & "Error Description: " & Error() & _
                Chr(10) & _
                Chr(10) & "Would you like to delete the existing" & _
                "database:" & Chr(10) & _
                Chr(10) & ActiveWorkbook.Path & "\" & _
                Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & _
                ".mdb"
            Title = "Error in Database Creation"
            Response = MsgBox(Message, vbYesNo, Title)
            If Response = vbYes Then
                Kill ActiveWorkbook.Path & "\" & _
                  Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) -4) _
                  & ".mdb"
                Message = ""
                Title = ""
                Resume
            Else
                Message = "In order to run this procedure you need" & _
                    Chr(10) & "to do ONE of the following:" & _
                    Chr(10) & _
                    Chr(10) & "1.  Move the existing database to a " & _
                    "different directory, or " & _
                    Chr(10) & "2.  Rename the existing database, or" & _
                    Chr(10) & "3.  Move the workbook to a different " & _
                    "directory, or" & _
                    Chr(10) & "4.  Rename the workbook"
                Title = "Perform ONE of the following:"
                MsgBox Message, , Title
                Message = ""
                Title = ""
                Exit Sub
            End If

        ' Check to see if the error was Type Mismatch. If so, set the
        ' file to dbText.
        Case 13 ' Type mismatch.
            If CreateFieldFlag = 1 Then
                Set Fd = Td.CreateField(ActiveCell.Value, dbText)
                Fd.AllowZeroLength = True
                Flag = 1
                r = LastCell.Row - 1
                CreateFieldFlag = 0
                Resume Next
            Else
                Message = "You have a ""Type Mismatch"" in the code" _
                    & Chr(10) _
                    & Chr(10) & "Error Number: " & Err _
                    & Chr(10) & "Error Description: " & Error() _
                    & Chr(10) _
                    & Chr(10) & "This procedure will close."

                Title = "Type Mismatch"
                MsgBox Message, , Title
                Message = ""
                Title = ""
            End If

        ' For any other error, display the error.
        Case Else
           Message = "An error has occured in the procedure." _
                & Chr(10) _
                & Chr(10) & "Error Number: " & Err _
                & Chr(10) & "Error Description: " & Error()

            Title = "An error has occured"
            MsgBox Message, , Title
            Message = ""
            Title = ""
     End Select
     End Sub

REFERENCES

Microsoft Access 97

For more information about creating indexes, click the Index tab in Microsoft Access Help, type the following text:

   Indexes, creating

and then double-click the selected text to go to the "Create an index to find and sort records faster."

Microsoft Access 7.0

For more information about indexing, click Answer Wizard on the Help menu in Microsoft Access 7.0, type "Index" (without the quotation marks) in the Search box, and click "Decide if and when to use an index."

Microsoft Access 2.0

For more information about indexing, click Search on the Help menu in Microsoft Access version 2.0, type "Index" (without the quotation marks) in the Search box, click "Index (see also indexes)," and then click "Creating an Index" under Topics.

Additional query words: 7.00 7.00a 8.00 97 xl97

Keywords          : kbprg kbdta kbdtacode KbVBA kbhowto 
Version           : WINDOWS:7.0,7.0a,97
Platform          : WINDOWS

Last Reviewed: May 18, 1999