Results 1 to 9 of 9

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:

    I have MS DAO 3.5 Object Library.
    also, 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

  2. #2
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Hi,

    With all the references to opening a database with DAO I see:

    Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")


    And you don't have 'DBEngine' included. Try that?

    I don't use DAO, so I'm not sure. I'd be more help if it was ADODB.

    -Warren

  3. #3
    Join Date
    Sep 2004
    Posts
    161
    I have test your preceding code with connection object and it's work fine, if you modify some lines :

    Dim conn As New ADODB.Connection 'Excel have this connection object... and is not the same

    conn.Provider = "Microsoft.Jet.Oledb.3.51" ' set the good version of jet engine
    conn.ConnectionString = "C:\toto.mdb"
    conn.Open

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

    excel to access

    Neither one of those work.
    Any other thoughts??
    I don't know what to do.???

  5. #5
    Join Date
    Sep 2004
    Posts
    161
    Sorry
    The two solutions i give you work fine for me (acc97, NT).
    Have you the same error message ?

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

    Excel To Access

    jEPSI:

    Can you give me more code, that is, can you take the code above and use y our your syntax bellow?

    Code:
    Dim conn As New ADODB.Connection 'Excel have this connection object... and is not the same 
    
    conn.Provider = "Microsoft.Jet.Oledb.3.51" ' set the good version of jet engine
    conn.ConnectionString = "C:\toto.mdb"
    conn.Open

  7. #7
    Join Date
    Sep 2004
    Posts
    161
    I 'm just test my syntax t home with Wxp and A2K2 and if y change the 3.51 version to 4.0 it's work very coooooooool
    May be can you post a sample of your excel workbook with code

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

    Excel to Access

    Jepi

    Can you tell me all the 'References' you have for you code. I think my problem is that either one is either missing or is invalid:

    Bellow are the 'References' I have:

    VB for application
    MS Excel 9.0 Object Library
    OLE Automation
    MS Office 9.0 Object Library
    MS DAO 3.6 Object Library
    MS Forms 2.0 Object Library
    MS Access 9.0 Object Library
    MS ADO Ext. 2.7 for DDL and Security
    MS ActiveX Data Object Recordset 2.7 Library

    Anyway Here is the code:

    When I debbug my code, I get this error :

    'Compile Error'
    'User-defined type not defined

    Code:
    Public Sub UpdateDB()
       
        Dim conn As ADODB.Connection
        Dim cmd As ADODB.Command
        
        
        Set conn = New ADODB.Connection
        Set cmd = New ADODB.Command
        
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppPath & ";Persist Security Info=False"
        conn.ConnectionTimeout = 30
        conn.Open
        
        Set cmd.ActiveConnection = conn
        cmd.CommandText = "UPDATE LOGCALL_table SET LOGCALL_table.clientName = '" & ActiveSheet.Range("A" & i).Value & "' WHERE  LOGCALL_table.clientName = 'RICHARD TEST2';"
        cmd.Execute , , adCmdText
        
        conn.Close
    
    End Sub

  9. #9
    Join Date
    Sep 2004
    Posts
    161
    Fro reference i have :

    Vb for application
    Microsoft Excel 8.0
    OLE Automation
    MS Forms 2.0 Object Library
    MS office 8.0 Object Library
    MS ActiveX Data Object 2.6 Library

Posting Permissions

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