Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel to Access (2000)

    Hi there.
    I've been trying to write a few records in Access via Excel VBA, using the following code:
    Dim dbMIS_BE As Database
    Dim rsClientTrans As Recordset
    Dim intNoRecords As Integer



    On Error GoTo ErrorHandler

    Set dbMIS_BE = opendatabase(Name:=gstrDatabase)
    Set rsClientTrans = dbMIS_BE.OpenRecordset(Name:=gstrTableName)
    intNoRecords = Cells(1, 1).End(xlDown).Row
    For intRecordloop = 1 To pintArea
    With rsClientTrans
    .AddNew
    !Agency = TransferArray(intRecordloop).Agency
    !ChannelCode = TransferArray(intRecordloop).Channel
    !Budget = TransferArray(intRecordloop).Budget

    etc
    when I created a reference to Microsoft Access the code did not work. When I added a reference to DAO, the code did work. Could anyone please explain why that is?

    Any answers much appreciated

    Amanda

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel to Access (2000)

    OpenDatabase and OpenRecordset are DAO methods, not Access methods.

    Access is the high-level user interface. It has methods to manipulate forms, reports etc.
    DAO is a lower-level programmatic data interface; it can be used to manipulate recordsets, fields etc.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts

    Re: Excel to Access (2000)

    It's because some of the things you can do in code relate to Access's own objects and user interface, whereas the actual database "bit" of the product is managed by DAO and is independent of Access. So if you want to work with tables, recordsets, querydefs etc from Word, Excel, Powerpoint (or any VBA-supporting client) you set a ref to the DAO library (or ADO if you prefer to work with that).

  4. #4
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel to Access (2000)

    Thanks Hans, that makes sense now

  5. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel to Access (2000)

    Thanks Jeremy

Posting Permissions

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