Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Import/Append Question

Discussion in 'Information Technology' started by Parker, Nov 3, 2005.

  1. Parker

    Parker
    Expand Collapse
    Guest

    I am importing an Excel spreadsheet into a table (table B). Then, I am
    using an append query to add the data in table B to my old table (table
    A). I have a form with a "show all records" button. The button event
    performs a SQL statement to select all records in table A and display
    the results in a listbox. The problem is, after running the import
    routine the query does not return the newly added records. I can open
    table A and see the new records. I save and exit and they are still
    there, but they still do not show up in the query result. To get them
    to appear, I have to copy them, delete them, and then paste them back
    into the table. Can someone please explain this to me? Is there a way
    to fix this? I am using Access 97. Thanks in advance for any help.
     
  2. Loading...

    Similar Threads Forum Date
    Question About GGS Questions and Answers Aug 28, 2016
    Marriage And Discrimination Related Questions Blogs Mar 21, 2016
    Sikhi Questioning Faith Sikh Sikhi Sikhism Mar 3, 2016
    Entering into Sikhism: Questions on Reht Maryada. Blogs Oct 22, 2015
    A question about kes/hair? Blogs Oct 21, 2015

  3. Ken Snell [MVP]

    Ken Snell [MVP]
    Expand Collapse
    Guest

    You'll need to give us more details about what you're doing for the "import
    routine", and how you're running it. Do you use macros or VBA code? Do you
    get error messages from the append query, or perhaps the error messages are
    not appearing because you've turned off the warnings?

    Sounds as if the append query is not working correctly, from what you've
    described.
    --

    Ken Snell
    <MS ACCESS MVP>



    "Parker" <ParkerPierce@gmail.com> wrote in message
    news:1130973614.553510.188700@g44g2000cwa.googlegroups.com...
    >I am importing an Excel spreadsheet into a table (table B). Then, I am
    > using an append query to add the data in table B to my old table (table
    > A). I have a form with a "show all records" button. The button event
    > performs a SQL statement to select all records in table A and display
    > the results in a listbox. The problem is, after running the import
    > routine the query does not return the newly added records. I can open
    > table A and see the new records. I save and exit and they are still
    > there, but they still do not show up in the query result. To get them
    > to appear, I have to copy them, delete them, and then paste them back
    > into the table. Can someone please explain this to me? Is there a way
    > to fix this? I am using Access 97. Thanks in advance for any help.
    >
     
  4. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Hve you tried to copy them and then paste them into another table and then
    compare to see what the difference is?

    "Parker" wrote:

    > I am importing an Excel spreadsheet into a table (table B). Then, I am
    > using an append query to add the data in table B to my old table (table
    > A). I have a form with a "show all records" button. The button event
    > performs a SQL statement to select all records in table A and display
    > the results in a listbox. The problem is, after running the import
    > routine the query does not return the newly added records. I can open
    > table A and see the new records. I save and exit and they are still
    > there, but they still do not show up in the query result. To get them
    > to appear, I have to copy them, delete them, and then paste them back
    > into the table. Can someone please explain this to me? Is there a way
    > to fix this? I am using Access 97. Thanks in advance for any help.
    >
    >
     
  5. Parker

    Parker
    Expand Collapse
    Guest

    Thanks for the reply. I am using VB code for the import.
    Here it is:
    Private Sub cmdImport_Click()
    'Refreshes data
    Dim fileE As Boolean
    Dim strDBName As String
    Dim strPath As String

    'Gets current directory of database to use for finding Excel file
    strDBName = CurrentDb().Name
    strPath = Left$(strDBName, Len(strDBName) - Len(Dir$(strDBName)))

    'Checks for Import table. If it exists, Import is deleted
    fileE = FileExists()
    If (fileE = True) Then
    DoCmd.DeleteObject acTable, "Import"
    End If

    'Transfers data from "Import.xls" Excel spreadsheet into db as
    "Import" table
    DoCmd.TransferSpreadsheet acImport, 0, "Import", strPath &
    "Import.xls", True

    'Runs query to append data from Import table to Data2 table
    DoCmd.OpenQuery "ImportQuery"

    'Deletes Import table
    DoCmd.DeleteObject acTable, "Import"
    End Sub

    "ImportQuery" is just an append query that appends all fields in
    "Import" to their match in my larger table. I get no error messages
    from the append or import. In fact, I get the confirmation ("Do you
    want to append X number of records...") messages when I run this event.
    I can see the records in my larger table after running this routine.
    They just do not show up in the listbox after I run the event (button
    click that runs query to return all records from large table) on my
    form. I have to cut them from the larger table, then immediately paste
    them back into the larger table. Then, I can open the form back up, run
    the event, and they show up. Thanks again for your help.


    Ken Snell [MVP] wrote:
    > You'll need to give us more details about what you're doing for the "import
    > routine", and how you're running it. Do you use macros or VBA code? Do you
    > get error messages from the append query, or perhaps the error messages are
    > not appearing because you've turned off the warnings?
    >
    > Sounds as if the append query is not working correctly, from what you've
    > described.
    > --
    >
    > Ken Snell
    > <MS ACCESS MVP>
    >
    >
    >
    > "Parker" <ParkerPierce@gmail.com> wrote in message
    > news:1130973614.553510.188700@g44g2000cwa.googlegroups.com...
    > >I am importing an Excel spreadsheet into a table (table B). Then, I am
    > > using an append query to add the data in table B to my old table (table
    > > A). I have a form with a "show all records" button. The button event
    > > performs a SQL statement to select all records in table A and display
    > > the results in a listbox. The problem is, after running the import
    > > routine the query does not return the newly added records. I can open
    > > table A and see the new records. I save and exit and they are still
    > > there, but they still do not show up in the query result. To get them
    > > to appear, I have to copy them, delete them, and then paste them back
    > > into the table. Can someone please explain this to me? Is there a way
    > > to fix this? I am using Access 97. Thanks in advance for any help.
    > >
     
  6. Ken Snell [MVP]

    Ken Snell [MVP]
    Expand Collapse
    Guest

    Ahhhhh... sounds like you need to requery your list box as the last step in
    your code.

    Me.ListBoxName.Requery


    --

    Ken Snell
    <MS ACCESS MVP>

    "Parker" <ParkerPierce@gmail.com> wrote in message
    news:1131030535.018036.221770@f14g2000cwb.googlegroups.com...
    > Thanks for the reply. I am using VB code for the import.
    > Here it is:
    > Private Sub cmdImport_Click()
    > 'Refreshes data
    > Dim fileE As Boolean
    > Dim strDBName As String
    > Dim strPath As String
    >
    > 'Gets current directory of database to use for finding Excel file
    > strDBName = CurrentDb().Name
    > strPath = Left$(strDBName, Len(strDBName) - Len(Dir$(strDBName)))
    >
    > 'Checks for Import table. If it exists, Import is deleted
    > fileE = FileExists()
    > If (fileE = True) Then
    > DoCmd.DeleteObject acTable, "Import"
    > End If
    >
    > 'Transfers data from "Import.xls" Excel spreadsheet into db as
    > "Import" table
    > DoCmd.TransferSpreadsheet acImport, 0, "Import", strPath &
    > "Import.xls", True
    >
    > 'Runs query to append data from Import table to Data2 table
    > DoCmd.OpenQuery "ImportQuery"
    >
    > 'Deletes Import table
    > DoCmd.DeleteObject acTable, "Import"
    > End Sub
    >
    > "ImportQuery" is just an append query that appends all fields in
    > "Import" to their match in my larger table. I get no error messages
    > from the append or import. In fact, I get the confirmation ("Do you
    > want to append X number of records...") messages when I run this event.
    > I can see the records in my larger table after running this routine.
    > They just do not show up in the listbox after I run the event (button
    > click that runs query to return all records from large table) on my
    > form. I have to cut them from the larger table, then immediately paste
    > them back into the larger table. Then, I can open the form back up, run
    > the event, and they show up. Thanks again for your help.
    >
    >
    > Ken Snell [MVP] wrote:
    >> You'll need to give us more details about what you're doing for the
    >> "import
    >> routine", and how you're running it. Do you use macros or VBA code? Do
    >> you
    >> get error messages from the append query, or perhaps the error messages
    >> are
    >> not appearing because you've turned off the warnings?
    >>
    >> Sounds as if the append query is not working correctly, from what you've
    >> described.
    >> --
    >>
    >> Ken Snell
    >> <MS ACCESS MVP>
    >>
    >>
    >>
    >> "Parker" <ParkerPierce@gmail.com> wrote in message
    >> news:1130973614.553510.188700@g44g2000cwa.googlegroups.com...
    >> >I am importing an Excel spreadsheet into a table (table B). Then, I am
    >> > using an append query to add the data in table B to my old table (table
    >> > A). I have a form with a "show all records" button. The button event
    >> > performs a SQL statement to select all records in table A and display
    >> > the results in a listbox. The problem is, after running the import
    >> > routine the query does not return the newly added records. I can open
    >> > table A and see the new records. I save and exit and they are still
    >> > there, but they still do not show up in the query result. To get them
    >> > to appear, I have to copy them, delete them, and then paste them back
    >> > into the table. Can someone please explain this to me? Is there a way
    >> > to fix this? I am using Access 97. Thanks in advance for any help.
    >> >

    >
     

Share This Page