help

[alxw3 via AccessMonster.com]

i'm exporting data ,10000 to 15000 rows, from excel to an access 2003 table.
it stops in middle due to duplicate records in the table. my table can not
have duplicated rows. How do i check if a row (field1, field2, field3)
already exist not to add?. what's is best practice?.

Dim conn As ADODB.Connection, rs As ADODB.Recordset
dim count as long

rs.Open "mytable1", conn, adOpenKeyset, adLockOptimistic, adCmdTable

while loop
with rs
.addnew
.Fields("field1") = Range("A" & count).Value
.Fields("field2") = Range("B" & count).Value
.Fields("field3") = Range("C" & count).Value
.update
end with
count = count + 1

 

[Alex Dybenko]

Hi,
I think best way is to import first to a temporary table, without
constrains, then check data for consistency there, and copy to destination
table only data you need, using append query

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[alxw3 via AccessMonster.com]

any other way?.

Hi,
I think best way is to import first to a temporary table, without
constrains, then check data for consistency there, and copy to destination
table only data you need, using append query

i'm exporting data ,10000 to 15000 rows, from excel to an access 2003
table.

[quoted text clipped - 16 lines]

end with
count = count + 1

 

[John W. Vinson]

i'm exporting data ,10000 to 15000 rows, from excel to an access 2003 table.
it stops in middle due to duplicate records in the table. my table can not
have duplicated rows. How do i check if a row (field1, field2, field3)
already exist not to add?. what's is best practice?.

Dim conn As ADODB.Connection, rs As ADODB.Recordset
dim count as long

rs.Open "mytable1", conn, adOpenKeyset, adLockOptimistic, adCmdTable

while loop
with rs
.addnew
.Fields("field1") = Range("A" & count).Value
.Fields("field2") = Range("B" & count).Value
.Fields("field3") = Range("C" & count).Value
.update
end with
count = count + 1

I'd *LINK* to the spreadsheet rather than importing it (using
TransferSpreadsheet in VBA), and run an Append query. The duplicate values
will be rejected with a trappable warning message rather than crashing you
out. This will be much more efficient than your loop and recordset.

Or, you can put an On Error condition in your code to trap the duplicate
values error and either ignore it or handle it properly (for example add the
duplicate records to a second table if you need to know which records caused
the problem).

 

[alxw3 via AccessMonster.com]

where can i find information on how to create Append query?. where do i
create it? on the sending table or on the receiving table?. if there is
complete example, i like to see it. How does it get scheduled? i am new to
access and append query technique.

i'm exporting data ,10000 to 15000 rows, from excel to an access 2003 table.
it stops in middle due to duplicate records in the table. my table can not

[quoted text clipped - 15 lines]

end with
count = count + 1

I'd *LINK* to the spreadsheet rather than importing it (using
TransferSpreadsheet in VBA), and run an Append query. The duplicate values
will be rejected with a trappable warning message rather than crashing you
out. This will be much more efficient than your loop and recordset.

Or, you can put an On Error condition in your code to trap the duplicate
values error and either ignore it or handle it properly (for example add the
duplicate records to a second table if you need to know which records caused
the problem).

 

[John W. Vinson]

where can i find information on how to create Append query?. where do i
create it? on the sending table or on the receiving table?. if there is
complete example, i like to see it. How does it get scheduled? i am new to
access and append query technique.

You can use File... Get External Data... Link, and select ".xls" as Files Of
Type, or do so programmatically in VBA using the TransferSpreadsheet method.
See the vba help for TransferSpreadsheet.

An Append query is... just a query. Create a query based on the linked
spreadsheet (as a "table"). Select the fields that you want to append. Change
it to an Append query by using the Query menu option, or the query type
dropdown tool in the toolbar. You'll be asked what target table should get the
data.

Scheduling is up to you - you could have an Access database with an Autoexec
macro which does nothing but run this query and then quit, which you could run
from the Windows Scheduler. There's various ways to do it depending on what
you need. I wouldn't use Access itself as a scheduler though.