Import/Append Question

P

Parker

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

Ken Snell [MVP]

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

KARL DEWEY

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

Parker

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

Ken Snell [MVP]

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

Me.ListBoxName.Requery
 
Top