Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Red face Unanswered: Very simple Q re Append Query

    Hi all

    This is my first posting - so please be gentle.

    I am currently trying to establish a dynamic link between an Excel table and and Access table. Working in Office XP.

    I have a simple Excel sheet with info arranged in 4 columns - field heading at top of column - I have an Access table with the same fields plus additional fields. I would like to establish a link somehow so that when info is entered into the SS, it is automatically updated in the DB.

    Is this possible? I'm sure it used to be in older versions of Office.

    I have created a linked Excel table in Access that is dynamic ie if I update my Excel file, the linked Excel table in Access updates and visa-versa and I am now trying to create an Append Query to append this linked table to my Access table - no go - the append process seems to work but the query contains no data.

    I know this is probably the simplest of questions - so hopefully someone out there can help. I've been everywhere trying to find a solution!

    I'm doing this for a study assignment - due on Monday - why do I always leave these things to the last minute????

    Thanks in anticipation.

    Lisa

  2. #2
    Join Date
    May 2003
    Location
    Costa Rica
    Posts
    40
    I dont remember but in this forum or in EXPERTS EXCHANGE forum I have seen exactly the same question and the answer. Review it and I will also try to search it. You have to restring your search with the word "import" or "Importing".

  3. #3
    Join Date
    Aug 2003
    Posts
    2
    Thanks for the advice - I'll keep looking - have tried Experts Exchange yet.

    Lisa

  4. #4
    Join Date
    Jul 2003
    Posts
    38
    If it can't be done with the Access standard functions (and according to your posting there is a bug in Access) you can try this VBA-Code:

    public sub AppendTable
    on error goto err

    dim rs1 as recordset
    dim rs2 as recordset
    dim TableName1, TableName2 as string
    dim i as integer

    TableName1="..."
    TableName2="..."

    set rs1=currentdb.openrecordset(TableName1,dbopendynas et)
    set rs2=currentdb.openrecordset(TableName2,dbopendynas et)

    if not rs1.bof then rs1.movefirst

    while not rs1.eof
    rs2.addnew
    for i=0 to rs1.fields.count -1
    rs2.fields(i)=rs1.fields(i)
    rs2.update
    rs1.movenext
    wend

    exitsub:
    exit sub
    err:
    msgbox err.description
    resume exitsub
    end sub


    To have it work correctly, your tables have to have the same amount of fields, but i think you checked this already.

    regards

Posting Permissions

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