Create temporary table in form for each user - designing

[jaryszek]

Hi,



i want to import data from Excel to Access relational database - it is my client requirement and i have no choice - i have to do it.

I see this in that way:

1) User is opening form and new temporary table is created
2) user is importing data into this table and is fullfiling lacking data.
3) Next user click button to insert all data into Access tables using query.
4) User is closing form and temporary table is dropped.

It is a good approach?
Maybe you have another idea?

As always i want to do my job as best as i can.

Best Wishes,
Jacek

[jaryszek]

One more thing - temporary tables can blow up my database design...

Maybe i should create temporary table within another databse, create link to it and delete link after whole operation?

Warm Regards,
Jacek

[ranman256]

you can do it that way. I sometimes do. The temp table is in the users copy of the FE (a local table).

The users clicks a button on the form to select the xl file.
it copies it to the same place everytime, c:\temp\File2Import.xlsx
this file is already attached as an external table.
it then runs the append query to the local table.
it opens the form for user to make corrections.
user clicks 'save' data button, then the append query copies the 'temp' local table to the linked external permanent table.

now the FE does grow after a LOT of imports, but nothing to concern about. just have user run a monthly Compact/repair.
I usu do so many updates that compact is rarely needed.

[jaryszek]

Thank you ranman256!

Your model seems excellent but my process is not standardized.

it then runs the append query to the local table.

Problem in my model is that i can't append data from Excel because there are not complited.
I need user to choose additionally fields for each row.

But i see the solution maybe here?

Append query will attach records from Excel to Local Table.
And after that user can do corrections using form and after exiting Access will be inserting data into my query joining all tables (relational query).

And after all local table will be cleared using DELETE Statement.

What if i am using split database and local table will share records form different Excel's and different users in the same time?

Jacek

[ranman256]

If the temp table is local, in the FE, then each person can import independently. No sharing.
now, they both can import the same xl file, but the editing is local only.

[ssanfu]

- The dB must be split because it is multi-user.
- The BE is on a network accessible drive. EACH user has a copy (not a shortcut) of the FE on their computer.
- In the FE, there is a temp table for importing Excel worksheet data. The table is called a "temp" table because the DATA is temporary, not the table. Constantly creating and deleting tables causes dB bloat and has a greater chance of corruption.
- Import the Excel data, then edit a record. When editing is complete, and before appending to the BE, the data/record should be validated to ensure that the record is not a duplicate. If you decide there is not an existing record that is a duplicate, append the record. You have to decide how close the match (number of fields that are identical) a record is before allowing the record to be appended to the BE.
- So if there is not a record that matches, the record could be appended. If the record matches (to whatever extent), decide if you want to update the BE record with the data just edited. So Person A imports an Excel worksheet and edits a record (in the local FE), then appends it to the BE. 10 minutes later Person B imports the same Excel worksheet and edits the same record (in the local FE) and attempts to append the record to the BE; what is/should be the procedure?

[jaryszek]

Hi Guys,

thank you very much!

ssanfu, couple questions:

In the FE, there is a temp table for importing Excel worksheet data. The table is called a "temp" table because the DATA is temporary, not the table.

How can I implement this step ?
Create link table from Excel table and insert all data into Temporary table using append query?

Next Person A can edit Data with form based on Temporary table.

And after all data can be uploaded into BE.

I am afraid that Person A will be editing Data and Person B would want to do the same after 1 minute and here 2 users will be try to edit data.
Maybe i should secure it by adding Login field and show in a query only data for specific person?

Best Wishes,
Jacek