Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opening database (Office 2000)

    I want to open a database from within Excel and use the DoCmd object to import my Excel data into a table. I've created an object variable to refer to Access, used CreateObject to open access and then used the OpenDatabase method to open the specific database I need.. but then the DoCmd object says that it 'requires an open database'??! Isn't the database already open? Andy.

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

    Re: Opening database (Office 2000)

    Andrew,

    Since this question is about Access VBA (even though you want to use it from Excel), you should post it in the Access forum.

    BTW, you should use OpenCurrentDatabase to open a database in the Access application, not OpenDatabase (a DAO method)

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening database (Office 2000)

    Sorry, I should have said. I'm using Access 2000 but I want to use DAO rather than ADO to achieve this.

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

    Re: Opening database (Office 2000)

    DoCmd works in the Access interface, whereas DAO (or ADO for that matter) works in code.

    If you want to use DoCmd to export something to Excel, there must be a database open in the interface, just as if you opened it interactively using File/Open...

    DoCmd doesn't "see" a database opened with OpenDatabase.

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening database (Office 2000)

    So how can I program Excel to send data into an Access table?? Thanks, Andy.

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

    Re: Opening database (Office 2000)

    You were on the right track, only you should use OpenCurrentDatabase (I'm starting to repeat myself). Something along the lines of

    Dim oAccess As Access.Application
    ' Open instance of Access
    Set oAccess = CreateObject("Access.Application")
    With oAccess
    ' Open a database
    .OpenCurrentDatabase "C:TestMyDatabase.mdb"
    ' Import data from spreadsheet
    .DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, _
    "tblImport",C:TestMySpreadsheet.xls", True, "A1:G12"
    ' Close the database
    .CloseCurrentDatabase
    ' Quit Access
    .Quit
    End With
    ' Release memory
    Set oAccess = Nothing

  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening database (Office 2000)

    Excellent cheers. Andy

Posting Permissions

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