Link to the table - this link should already be established so maintain the same file name and cleanse the file regularlyĢ. Perhaps you want to include a Now() field that will indicate when data was imported for future updates.Ĥ) Not necessary because it's already done in step 2Īll of the above is something that should be done manually in order to establish a stable table structure that doesn't change structurally.įor future updates to this table you would automate the following:ġ. Now this will create a table structure as well as copy the data from the linked table to the local tableģ) Modify the design of new 'local table' - this is fine but I don't see the need of an AutonumberID. In a database one does not copy and paste records, nor does one continually create/delete local tables or create indexes at will.Ģ) Create a Make-Table query based on your linked table and execute the query. If you aren't adding new records then option 1 will be the favourable approach.Ĭlick to expand.Unfortunately you're still thinking of your Access database as an Excel spreadsheet. now this is a Bulk update/insert which the Jet Engine handles nicely. The join will be between the Instr/Mid field and the respective Speciality field Create an appropriate INNER JOIN between the linked table and the Speciality table using Instr() and Mid().For example, you have to insert one record at a time. Note the major bottlenecks highlighted in blue. The check must use an appropriate search method, i.e.:.For each record in the file you check if a corresponding record exists in the Speciality table.You then also have a recordset of your Speciality table.You load the file into memory using one of the above methods.Loop and FIND method using a recordset/file system object/ Open file/ADO text provider etc methods: You're using Split() but split isn't the only way of identifying the right part of your record string.
0 kommentar(er)
