Re: Import Automation and...

From: John Nurick (j.mapSoN.nurick_at_dial.pipex.com)
Date: 02/04/05 

Date: Fri, 04 Feb 2005 20:56:39 +0000

In that case I'd do it slightly differently inside that inner loop:

1) If there's no existing table, construct and execute a create-table
query (this is different from a make-table query, it creates an empty
table and lets you specify the primary key and stuff, which a make-table
query doesn't).

To find out whether the table already exists, you can use the function
at http://www.mvps.org/access/tables/tbl0001.htm.

The query will look something like this. Look in Access Help for
"Microsoft Jet SQL Reference", and in that for "Data Definition
Language".

  CREATE TABLE TableName (
    F1 CHAR (8),
    F2 CHAR (255) CONSTRAINT PrKey PRIMARY KEY,
    F3 CHAR (255),
    F4 LONG
  );

2) Now, wherever you started, there's a table to append to - so you
construct and execute an append query instead of a make-table query. It
will look something like this (both this and the CREATE TABLE sample
above assume that the second field is the one you want to be the primary
key:

  INSERT INTO TableName
    SELECT * FROM [textfile specification]
    WHERE F2 NOT IN (SELECT F2 FROM TableName);

   

On Thu, 03 Feb 2005 19:46:06 GMT, "Chris via AccessMonster.com"
<forum@AccessMonster.com> wrote:

>Thank You so much for your help, I can't express this enough. This works
>just like I had wanted it too.
>
>I was just wondering...
>
>You left a hole in the code where you said
> '*** You may want to include code here to handle the
> 'situation where there is already a table of this
> 'name
>
>I would actually like to have it append to an existing table if the name
>already exists the only constraint is that a primary key is defined on the
>first table as I can't have duplicate email addresses. (We don't want to
>send the same promo twice to one person).
>
>I have found away to assign primary keys but I have not been able to fit it
>in the code for it to work. 

--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.

Relevant Pages

  • 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: Refreshing Data in a Table
    ... It's not necessary to set this to be the primary key of the Access ... a query like this will append the new records to the ... the sheet name, e.g.. ...
    (microsoft.public.access.externaldata)
  • Re: Combining two row into one
    ... So you will have to remove the primary key from the SELECT ... iam trying to make a query which will return 1 row with n+n columns ... VB & then execute it.... ... Trust iam clear. ...
    (microsoft.public.access.queries)
  • Re: Deleting duplicates entries in Database using MS Access
    ... when i tried to run the append query. ... fields that are primary key field. ... And if you want to retain one of the duplicated records, ...
    (microsoft.public.access.queries)
  • Re: Problem with Access concatenate query
    ... records in the final query. ... You probably need to INNER JOIN the tables, although on what column, I am not sure. ... PriceID -- Primary Key ... ItemID --- Foreign Key ...
    (microsoft.public.access.queries)
Quantcast