Updating Existing Records from an External Source

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table in Access 2003 that needs to be updated periodically from a
report that runs on a web site. The report (designed by someone else)
displays the information in my web browser in comma-delimited format.
Currently to get it into Access I use the following process:

1. Run the report
2. Use Select All and Copy
3. Paste it into an Excel Spreadsheet
4. Use Text to Columns to convert it to Excel
5. Save it as an Excel File
6. Open my table in Access
7. Select All Records
8. Delete All Records
9. Close the table
10. Use the Import Wizard to import the data back into the table.

Since the data file includes a unique Master ID # for each record, I was
wondering if there was any sort of macro or code function I could set up to
compare master IDs, update existing records, and add any that didn't match,
rather than deleting all the records and importing them back into the table.
Has anybody out there set up something like this? I have several instances
where this sort of thing would be very helpful. TNX
 
Amy Baggott said:
I have a table in Access 2003 that needs to be updated periodically from a
report that runs on a web site. The report (designed by someone else)
displays the information in my web browser in comma-delimited format.
Currently to get it into Access I use the following process:

1. Run the report
2. Use Select All and Copy
3. Paste it into an Excel Spreadsheet
4. Use Text to Columns to convert it to Excel
5. Save it as an Excel File
6. Open my table in Access
7. Select All Records
8. Delete All Records
9. Close the table
10. Use the Import Wizard to import the data back into the table.

Since the data file includes a unique Master ID # for each record, I was
wondering if there was any sort of macro or code function I could set up to
compare master IDs, update existing records, and add any that didn't match
Click to expand...

Sounds like you can do it in two steps:

1. Copy and paste the data to a text file.
2. Execute Jet's INSERT INTO..SELECT SQL DML syntax, using an OUTER
JOIN on the key column(s) in your text file and database table
('master ID'), testing for a null key value in the database table.

Post some more details e.g. example web report data, your database
table schema, etc.

Jamie.

--
 
How do I execute that, as part of a query, or do I need to set it up in VBA
code?

The data file looks like this:

masid,ccl,vbooth,export
000059572-1,Y,N,N
000059572-2,Y,N,N
000059572-3,Y,N,N
000260924-1,Y,N,N
000300980-1,Y,N,N
000305592-1,Y,N,N
000323206-1,Y,N,N
000326926-1,Y,N,N
000368300-1,Y,Y,Y
000375579-1,Y,N,N
000483783-1,Y,Y,N
000629163-1,Y,N,N

only about 1300 records. The table is set up with the same four fields plus
a numeric ID as a key. I'm not sure what you mean by "table schema", but all
four fields are read as text.
 
Amy Baggott said:
I'm not sure what you mean by "table schema", but all
four fields are read as text.
Click to expand...

In order to recreate your scenario, I need to recreate your table so
it would benefit both of use if I don't make too many assumptions e.g.
I don't even know waht your table is called. If you posted SQL DDL to
create your table, would also not have to do all the work <g>. In lieu
of DDL, I'll have to make some assumptions:

CREATE TABLE Amys (
ID IDENTITY,
masid CHAR(11) NOT NULL,
ccl CHAR(1) DEFAULT 'N' NOT NULL,
vbooth CHAR(1) DEFAULT 'N' NOT NULL,
export CHAR(1) DEFAULT 'N' NOT NULL,
CONSTRAINT pk_amys
PRIMARY KEY (masid, ID),
CONSTRAINT ck_amys_masid
CHECK (masid LIKE
('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]-[0-9]')),
CONSTRAINT ck_amys_ccl
CHECK (ccl IN ('N','Y')),
CONSTRAINT ck_amys_vbooth
CHECK (vbooth IN ('N','Y')),
CONSTRAINT ck_amys_export
CHECK (export IN ('N','Y'))
);

(This sql code must be run using ADO and the OLE DB provider but you
shouldn't need to if you already have a similar table).

Using this table and the data as posted saved as C:\My Folder\amy.csv,
the required SQL DML would be:

INSERT INTO Amys
(masid, ccl, vbooth, export)
SELECT
T2.masid, T2.ccl, T2.vbooth, T2.export
FROM
Amys T1 RIGHT JOIN
[Text;HDR=YES;Database=C:\My Folder\;].amy#csv T2
ON T1.masid = T2.masid
WHERE
T1.masid IS NULL
;

Jamie.

--
 
Back
Top