Welcome to SPN

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

Sign Up Now!
  1. Monthly (Recurring) Target: $300 :: Achieved: $95
      Become a Supporter    ::   Make a Contribution   

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
    Anyone Have Any Unanswered Questions On Sikhi Or God? Sikh Sikhi Sikhism Jan 7, 2017
    Question About Translations Questions and Answers Dec 20, 2016
    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

  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.
    >> >

    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page