Results 1 to 4 of 4

Thread: excel to Access

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

    Unanswered: excel to Access

    Hi,

    wind2k and xp
    access 2k

    I have this problem I can not seem to be able to resolve. It is urgent that I fix it for my project.
    The code bellow is a test: It is to pick up the data from Titles tables in db1.mdb in the c:\ and put in the excel sheet. However it does not work and I am stuck. Please somebody help me.



    Note:

    When highliting the db it is set at 'nothing' I don't understand.
    Code:
    Private Sub CommandButton1_Click()
    On Error Resume Next
       Dim db As Database
       Dim rs As Recordset
       Dim fld As Field
       Dim i As Integer
       
       Set db = OpenDatabase("c:\db1.mdb")
       Set rs = db.OpenRecordset("Titles", dbOpenDynaset, dbReadOnly)
       [A2].CopyFromRecordset rs
       
       For Each fld In rs.Fields
            i = i + 1
            Cells(1, i).Value = fld.Name
       Next fld
       ActiveSheet.Columns.AutoFit
       db.Close
        
    End Sub
    I have MS DAO 3.51 Object Library
    MS Access 9.0 Object Library.
    What other Libraries do I need to make this work?

  2. #2
    Join Date
    Sep 2004
    Posts
    6
    I would suggest using ADO (ActiveX Data Objects)

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I would also use ADO not DAO given a free choice, but I have used DAO in the past, but I assume use use Access moslty?.

    I think you need to add thses two line


    Dim wrkJet As Workspace

    Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)


    and modify the OpenDatabase so

    Set db = wrkJet.OpenDatabase("c:\db1.mdb")


    It worked for me!


    HTH

    MTB

  4. #4
    Join Date
    Oct 2004
    Posts
    8
    hi, this should work fine,
    the only refernce you need is to : MS DAO 3.6 Object Library

    Code:
    Private Sub CommandButton1_Click()
    On Error Resume Next
       Dim db As DAO.Database
       Dim rs As DAO.Recordset
       Dim fld As DAO.Field
       Dim i As Integer
       
       Set db = OpenDatabase("c:\db1.mdb")
       Set rs = db.OpenRecordset("Titles", dbOpenDynaset, dbReadOnly)
       [A2].CopyFromRecordset rs
       
       For Each fld In rs.Fields
            i = i + 1
            Cells(1, i).Value = fld.Name
       Next fld
       ActiveSheet.Columns.AutoFit
       db.Close
        
    End Sub

Posting Permissions

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