Re: Code Efficiency Suggestions

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Nick Thanks for the Code! I have a question, when determining the 'Fund' in
Excel I have to use an offset as it is two lines below and it doesn't tell
the fund on the actual line of the fund number. When we clean the
irrelevant lines of code, we now no longer know where the fund is..for
example in the clean column you'll see something like "This is the fund
description ZO09876" the "This is the fund description" can be anything so
it would be difficult to key off of any type of text there. If you just set
the column to extract the last word in that column, you'd also get all kinds
of other data as that column contains most of the relevant data. Any
thoughts?
"Nick Hebb" <n.hebb@xxxxxxxxxxx> wrote in message
news:1118429224.958596.264920@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Part II:
>
>
> 8. Create another query and select tmpParsedInputData in the Show table
> dialog. Select all the fields from tmpParsedInputData and drag them
>
> into the fields area. Go to Query on the menu and change the type to
> Append query and select your target table as the table to append to.
>
> Access should automatically figure out which fields append append
> where, but you'll want to double check. Save the query as
>
> qryAppendParsedInputData.
>
> 9. At this point you're ready to run the append query, but you should
> be extra cautious. It's really easy to screw up data in a flash with
>
> databases, so you should test everything first. I would make a copy of
> the original target table and change the target table in the append
>
> query to the copy. Run the append table and check the effect on the
> data. Then I would import the data into the original table using your
>
> traditional method. Then, create a find unmatched query using the
> query wizard and verify that all the records created with the new
> method
>
> match the records created with the old method. If it all works, change
> the target table in the append query back to the original target
>
> table (instead of the copy).
>
> 10. Create a new query and save it as qryDeleteInputData. In SQL view
> paste the folowing:
>
> DELETE tmpInputData.Col1
> FROM tmpInputData
> WHERE (((tmpInputData.Col1) Like "*"));
>
> 11. Go to the macro screen and create a new macro. Add the following
> Actions
> - OpenQuery (query name = qryDeleteInputData)
> - DeleteObject (type = table, name = tmpParsedInputData)
> - TransferText (specification name = to the one you saved earlier, file
> name = your source file, table name = tmpInputData)
> - OpenQuery (query name = qryCleanInputData)
> - OpenQuery (query name = qryMakeParsedInputDataTable)
> - OpenQuery (query name = qryAppendParsedInputData)
> - DeleteObject (type = table, name = tmpParsedInputData) ' run again to
> clean up
> - OpenQuery (query name = qryDeleteInputData) ' run again to clean up
>
> You might also want to Repair and Compact the database since theiur was
> a lot of data created and deleted:
> - RunCommand (command = RepairDatabase)
> - RunCommand (command = CompactDatabase)
>
> Save the macro and you're set.
>


.

Relevant Pages

  • Re: Using Append Queries with Linked Tables
    ... then use the work table as the source to append ... records into the target database. ... Another method would be to generate a query that only has unique records. ... DELETE statement are not valid becauase the primary key, ...
    (microsoft.public.access.queries)
  • Re: creating field with blank data for append query
    ... the target table's name for that is "Pt Initials". ... "Microsoft Access can't append all the records in the append query: ... and 0 recorddue to validation rule violations. ...
    (microsoft.public.access.queries)
  • Re: Importing changing data from Excel
    ... would create duplicate primary key values in the target table. ... 'Cannot insert data with action query' so I got bold and deleted the 'is ... >> primary key value as a record that already exists in the target table. ... >> unless Case No is Null in the records that you're trying to append? ...
    (microsoft.public.access.externaldata)
  • RE: Importing an ODBC database
    ... The query will delete the old ... target and give you just the new information. ... Append the primary key into the target ... people that dont' know about relationships dont' have to worry about ...
    (microsoft.public.access.externaldata)
  • Re: Membership database updates
    ... When you open any Query in Design View, click on the toolbar just next to ... black down-arrow where you can change your query to an Append or Update ... If you have any current members in your Import list, ... You mention Update Queries and Append Queries but I can't find how to ...
    (microsoft.public.access.tablesdbdesign)