Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jul 2008
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    reading/writing data (Access 2007)

    I'm trying to use a form in an Access database to read data from another access database and write it into an Excel spreadsheet. I'm using this code:

    strSQL = "SELECT * INTO [Excel 8.0;Database=" & strExcelFile & "].[Failure_Data] FROM " & "" & strTable & ""

    but I'm getting an error message saying: "External table is not in the expected format".

    Is the "8.0" right? Does anyone know what i need to do?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reading/writing data (Access 2007)

    The Excel part appears to be OK, I think the problem lies with the value of strTable or with the connection to the other Access database, but you haven't provided any details about those.

  3. #3
    New Lounger
    Join Date
    Jul 2008
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reading/writing data (Access 2007)

    sorry, here's what I've got. The connection to the Access database is always the same file. The excel file to be opened depends on which button is pushed on a form in Access but is set by the on_click
    command by:

    SourceFile = "P:Process EngineeringTest Log 3.0TestLogExcelFailureCategories.xls"




    then a function is called with the following:

    Function Insertdata()

    Dim strExcelFile As String
    Dim strWorksheet As String
    Dim strDB As String
    Dim strTable As String
    Dim objDB As Database
    Dim strSQL As String


    strExcelFile = DestinationFile
    strWorksheet = "FTFU_Data"
    strDB = "P:Process EngineeringTest Log 3.0Test Log 3.0_BE.accdb"




    If strChartType = "FailureCategories" Then
    strTable = "FailureCategories"
    Else
    strTable = "FTFU"
    End If

    Set objDB = OpenDatabase(strDB)



    If strChartType = "FailureCategories" Then
    strSQL = "SELECT * INTO [Excel 8.0;Database=" & strExcelFile & "].[Failure_Data] FROM " & "" & strTable & ""
    Else
    strSQL = "SELECT * INTO [Excel 8.0;Database=" & strExcelFile & "].[FTFU_Data] FROM " & "" & strTable & ""
    End If


    objDB.Execute strSQL

    objDB.Close
    Set objDB = Nothing

    end function

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reading/writing data (Access 2007)

    I have to admit my ignorance here. I don't have Access 2007. Has DAO been updated to work with .accdb databases?

  5. #5
    New Lounger
    Join Date
    Jul 2008
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reading/writing data (Access 2007)

    I guess I'm not really sure what DAO does/is...

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: reading/writing data (Access 2007)

    If you're using Office 2007, you may need to use "Excel 12.0" instead of Excel 8.0, as you would in a connection string.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    New Lounger
    Join Date
    Jul 2008
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: reading/writing data (Access 2007)

    I still get the same thing.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reading/writing data (Access 2007)

    DAO (Data Access Objects) is an interface that lets you interact with data and databases using code. One of the objects in the DAO library is the Database object, you use it in the lines

    Dim objDB As Database
    ...
    Set objDB = OpenDatabase(strDB)
    ...
    objDB.Execute strSQL

    objDB.Close
    Set objDB = Nothing

    Traditionally, DAO worked with .mdb databases, the file format used in Access 2003 and before. I don't know whether the DAO version for Access 2007 has been updated to recognize the new .accdb database file format in Access 2007.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: reading/writing data (Access 2007)

    He's using an .xls file, not an .xlsx file, so Excel 8.0 should be OK.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •