excel to Access

Alexxx12 · #1 (**permalink**) 09-17-04, 09:24

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?

zacemmel · #2 (**permalink**) 09-23-04, 19:20

I would suggest using ADO (ActiveX Data Objects)

MikeTheBike · #3 (**permalink**) 09-24-04, 08:35

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

n00bl0rd · #4 (**permalink**) 10-02-04, 10:32

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

Internet Services

Web Development

Digital Marketing

Consumer Tech