Results 1 to 6 of 6
  1. #1
    Philosophaie is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    28

    "Tools" and "References"

    I put a reference in "Tools" and "References" for "Microsoft DAO 3.6 Object Library" and "Microsoft Access 14.0 Object Library" but I still get an error on "cn" of "User-Defined Type Not Defined"

    Code:
        Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim ConnStr As String
        ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\path\db1.mdb;"
        cn.Open (ConnStr)
    ...
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing


  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    For a DAO recordset try:

    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Set db = OpenDatabase("\\path\otherdb.mdb")
    Set rs = db.OpenRecordset("SELECT * FROM tablename", dbOpenDynaset)
    ...
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing


    For ADODB recordset need reference to Microsoft ActiveX Data Objects 6.1 Library. Try:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\path\db1.mdb;"
    rs.Open "SELECT * FROM tablename;", cn, adOpenDynamic, adLockOptimistic
    ...
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Philosophaie is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    28
    Quote Originally Posted by June7 View Post
    For ADODB recordset need reference to Microsoft ActiveX Data Objects 6.1 Library. Try:

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\path\db1.mdb;"
    rs.Open "SELECT * FROM tablename;", cn, adOpenDynamic, adLockOptimistic
    ...
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    I am working in Vista reference to Microsoft ActiveX Data Objects 6.0 Library is that ok.

    In this example the ".Field("Primary Key") = PrimaryKey" has Error code "Method or data member not found"


    Code:
        Dim cn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim ConnStr As String
        Set rs = New ADODB.Recordset
        Set cn = New ADODB.Connection
        ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Philosophaie\Documents\a  access\a Chase.mdb;Mode=Share Deny None;"
        cn.Open (ConnStr)
        For k = 5 To Sheets("2013").Cells(Rows.Count, "A").End(xlUp).Row
            With rs
                PrimaryKey = Sheets("2013").Cells(k, 1).Value
                SQLstr = "SELECT * FROM 'a Chase from Excel Data' WHERE 'a Chase from Excel Data.Primary Key'='" & PrimaryKey & "';"
        
                .Open Source:=strSQL, ActiveConnection:=cn, CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdText
                If .EOF = True Then
                  .AddNew ' create a new record
                End If
                .Field("Primary Key") = PrimaryKey
                .Field("DatePaid") = Sheets("2013").Cells(k, 2).Value
                .Field("WhatPaid") = Sheets("2013").Cells(k, 3).Value
                .Field("AmtPaid") = Sheets("2013").Cells(k, 4).Value
                .Field("Total") = Sheets("2013").Cells(k, 5).Value
                .Field("AmtRec") = Sheets("2013").Cells(k, 6).Value
                .Field("WhatRec") = Sheets("2013").Cells(k, 7).Value
                .Field("DateRec") = Sheets("2013").Cells(k, 8).Value
                .Update
            End With
        Next k
        rs.Close
        Set rs = Nothing
        cn.Close
        Set cn = Nothing

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Yes, that if that is the latest library in your version of Access. Mine shows 6.1.

    If the field and table names have spaces, then the references in the SQL need []. The apostrophes are for text criteria, not names.

    "SELECT * FROM [a Chase from Excel Data] WHERE [Primary Key]='"

    If [Primary Key] is a number type, eliminate the apostrophes from the criteria.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Philosophaie is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    28
    Changed all SQL to []. The apostrophes are for text criteria, not names.

    Changed the field name "Primary Key" to "Index" but it still gave me the the same error:

    "Method or data member not found"

    In the Access file from the above file there exists a field name of "Index" in the first position and it is the primary key and unique.

    ?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Since I don't know your db I don't know why it can't find the referenced fields. Are you sure spellings are correct? If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-14-2012, 04:47 PM
  2. Export "Query or Report" to a "Delimited Text File"
    By hawzmolly in forum Import/Export Data
    Replies: 3
    Last Post: 08-31-2012, 08:00 AM
  3. Replies: 11
    Last Post: 03-29-2012, 02:32 PM
  4. Replies: 16
    Last Post: 07-22-2011, 09:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums