Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: from Excel to Access

    Hi,
    win2k and xp
    access 2k

    I want to get some records from the logcall_table in the logcall.mdb db and write them into my excel spreadsheet 1. I took the code bellow from Excel VBA Macro Programming by Richard Shepherd and changed the SQL to accomodate my table, but it does not work. I get this error:
    Run-time error'-2147467259(80004005)':
    Automation error
    Unspecified error

    the help section is not help. What am I doing wrong?

    Note:

    I have checked under preferences the following:
    MS ActiveX Data Object 2.7 Library
    MS ActiveX Data Objects recordset 2.7 Library
    MS ActiveX Data Access Components Installed Version
    MS ActiveX DAO 3.6 Object Library

    The code bellow is in the command1_button on sheet1 of the workbook
    Code:
    Private Sub CommandButton1_Click()
       
        Dim Mycon As New Connection
        Dim rs As Recordset
        Dim co As Integer
       
       
        Mycon.Open "c:\logcall\logcall.mdb"
        Set rs = New Recordset
        rs.Open "select clientname from logcall_table", Mycon, adOpenForwardOnly, adLockReadOnly, adCmdText
        co = 1
        Do Until rs.EOF
            ActiveSheet.Range("a" & co).Value = rs!ClientName
            co = co + 1
            rs.MoveNext
        Loop
        rs.Close
        Mycon.Close
    
    
    End Sub

  2. #2
    Join Date
    Sep 2004
    Posts
    161
    I think you must create an instance of Access before working with your mdb.
    For this maybe you must before add a reference to the Access Library

  3. #3
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    from Excel to Access

    The Instance Of Access Already Exists In The C:\logcall\logcall.mdb.
    I Dont' Understand Your Second Statement.??

  4. #4
    Join Date
    Sep 2004
    Posts
    161
    I'm sorry i have not the libray for create a new connection. But I have try this

    Code:
    Sub test()
        Dim toto As Access.Application
        Dim titi As Database
        Dim rs As Recordset
        Dim co As Integer
        On Error GoTo 0
        Set toto = GetObject(, "Access.Application")
        If Err.Number <> 0 Then
        Set toto = CreateObject("Access.Application")
        End If
    ' If access is not open you must use the following line
    '    toto.NewCurrentDatabase "D:\Jepi\ACCESS\Otadds\otadds_t.mdb"
    
        Set titi = toto.CurrentDb
        Set rs = titi.OpenRecordset("select [Code OI] from [Codes OI]")
        co = 1
        Do Until rs.EOF
            ActiveSheet.Range("a" & co).Value = rs![Code OI]
            co = co + 1
            rs.MoveNext
        Loop
        rs.Close
        
        titi.Close
        toto.Quit
        Set toto = Nothing
    End Sub
    It's work fine.
    If you're using VBA Excel , you must have a reference to library Microsoft Access X.0 Object Library and Microsoft DAO X.0 Object Library. For add this you go on the Tool Menu, Choose reference

Posting Permissions

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