Excel data imports into random areas of table

[STAN]

Hello All,
I have an MS Access table into which i import data from excel. I have an Access form that does this for me. My issue is that the data gets imported into the table but it gets stored at random areas in the table. It does not append to the beginning or end of the table but rather just inserts itself into some area(s) of the table. For example, if i import 7 rows of data from excel, it imports as 7 rows into 7 different areas of the table or sometimes as 7 sequential rows into the middle of the table somewhere but not to the beginning or the end of the table. So basically, the user would have to browse through all the records in the table sequentially to find the specific record(s) that was imported last. I have checked for filters and OrderyBy clauses and have cleared them all and it still does the same thing when i import the data from excel. Is there a a code i can add use to my import code so it imports the data to the end of the table rather than the middle of the table somewhere? Any help would be greatly appreciated!
This is the import code that i use:

Private Sub Import_Click()
On Error GoTo Err_Import_Click

If IsNull(tbfile) Or tbfile = "" Then
MsgBox "Please browse and select a valid file to import.", vbCritical, "Invalid File"
Else
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, _
"Table1", tbfile, True
Me.Form.Recordset.MoveLast
DoCmd.GoToRecord , , acLast
Me.Requery
End If

Exit_Import_Click:
Exit Sub

Err_Import_Click:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Import_Click

End Sub

where "tbfile" is the variable that stores the name/path of the excel file to import.

 

[SOS]

One common misconception is that data is stored in some specific order in the table. This is just not true. If you want to see it in some particular order you have to use a QUERY with an Order By Clause based on some meaningful field or combination of fields.

Tables do not store data in any specific order (even though it may LOOK like it does when you enter data manually, it really doesn't).

 

[gemma-the-husky]

if you ensure you have some unique identifier in the spreadsheet for each row - then the order doesnt matter - thats the principle of a datbase - the order shouldnt matter

 

[STAN]

There are no unique fields in the spreadsheet..thats the challenge that i faced, so i was thinking of working around it. I thought about having access add the system date & time to the records as they are imported, so each record would have a system date/time attached to it as an extra field. That way we can pull up the records based on the time they were imported into the database. Now my challenge is to implement that. If there is a text box on the access form showing the current date and time how would i get it into the table along with the imports. For example, if i import seven records from an excel file to an access table, i would like to have the current time, that shows on the access form, also to join these seven records as an extra field as they enter the table. Could you point me in the right direction here?

 

[SOS]

Import your spreadsheet to an interim table. Then use an append query to append it to the live table and include in the live table the date/time field and the append query can just append with NOW() as the value.

 

[STAN]

Is there some line of code that i can add to my import function(above) so that it transfers the date/time on the form also to the table with each of these records?

 

[SOS]

Is there some line of code that i can add to my import function(above) so that it transfers the date/time on the form also to the table with each of these records?

Okay, let's try this again -

1. Import the spreadsheet to an interim table. That means you have a blank, table that you import these records to. It does NOT include a date.

2. Create an APPEND query. You just need to go create a select query in the QBE grid and then go to the menu or ribbon and change the type to APPEND. It will ask you which table to append to. Select the one which is your live table.

3. Then add a field at the end of your append query where the heading is just

MyDate:Now()

4. Then you have code that runs the import (which imports it to the interim table and you will probably need a delete query to run first to clear that table of any records from a previous import) and then

5. You have code which runs your append query.

 

[STAN]

Ok, i added an interim table into which the records are first imported and then exported to the main table with the current system date/time attached to it. The next challenge is to have the form(with fields that are bound to the main table) to display the records that we just imported. That was my whole purpose. Currently, the imported records just go into some random area of the table and we have to browse through the 70,000 something records to get to the ones we just imported(I can't for the life of me figure out why it doesnt get appended to the end or the beginning of the table) I have a Date/Time picker control on the form which i would like to use to pick a date and it should pull up the record with that specific date and display it on the form. Now, what i did to accomplish this is:
Added the following code to the "AfterUpdate" Event of the Date/Time picker control

Private Sub dtpicker_AfterUpdate(Code As Integer)
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ImportTime:] = " & Str(Nz(Me![dtpicker], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


But even after i choose the date on the Date/Time picker on the form, it doesn't do anything. any ideas?

 

[SOS]

You base your form on a QUERY not the table. And the query can have the data sorted starting by your DATE/TIME field DESCENDING.

 

[The_Doc_Man]

Stan, my answer is going to seem a bit abrupt. It is not intended to be insulting, but it is intended to get you to sit up and take notice of what you are asking.

Listen (read) again: TABLES HAVE NO ORDER. There is no beginning, no end, no middle. A table is defined (in the set theory underlying SQL) as an amorphous set of records. A query can IMPOSE an order on those records AFTER THE FACT. IF there is a prime key associated with your imported data, you will always get records from the table in prime-key order. If no such key exists, a table's record order is not guaranteed.

What you are seeing is the side effect of having omitted something important from your process, which is to say that if order of importation is important, you needed to pre-define how you planned to track that before you started the process. If the information you import does not AND cannot have order-of-appearance information in it, you cannot define that order from the source data. Which means you must add extra information during the import process so you can tell the difference of importation order.

Adding a timestamp might work, but with this caveat: A single import of (as you describe) a few items at a time will probably be fast enough to result in those few items having the same time stamp to the second. Access date/time fields, when stored, don't track fractions of a second even if the system clock could do so. So you would get a GROUP of items with the same time stamp. You could find them clustered by date/time. If that is enough for your needs, then using an intermediate table to which you add a timestamp in an APPEND query (as suggested by SOS) would do the job.

If THAT isn't good enough either (i.e. you need to know the order in which each of the few items was imported within that one operation), you have a flawed concept to begin with. Imposing order after the fact will be arbitrary because.... wait for it... even using an INTERMEDIATE table, the records in the intermediate are not guaranteed to be in any particular order, either.

I understand this answer isn't what you wanted to hear/see, but the truth is that you did not design the process to support something you claim you need. If your data source cannot provide that information you wanted and you cannot find a way to impose what you wanted after the fact during the import process, you cannot have what you wanted. It's that simple.

 

[STAN]

The_Doc_Man, I understand what you're saying about the flawed design...this database was long being used before i could get my hands on it and the users didn't want me to change its structure as it would mess up the reports that they work on. They only wanted to get around having to re-enter data that is already on an excel file. I had to come up with a way to import the data from the excel to Access and then having them access that imported record. That was where i was stuck because the last imported data was not showing up on the form unless we wanted to wade sequentially through 70K+ records.
Anyway, i used SOS' suggestions and created and interim table and a query that appended the date/time field to the main table. Then i used the OrderBy property of the form to arrange the data based on the "ImportTime" field. Thats all that i wanted and its currently usable. Thank you all for sharing your valuable time and ideas.

 

[STAN]

Yes, and if you read my post above..adding the "extra information"(the system time at the time of import) is just what i did to display the records in a specific order. Also this database was created before i was brought in to fix it, so there wasn't a way i could've controlled its design process at creation time.
But Thanks.