Update SQL-Data from Access MDB

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

Guest

Hello @all

ive a daily generated MS-Access Database with round about 250.000 Records. With them i must update a sql-Server DB-Table with three fields.

Actualy i connect to the ms-access DB with an oledb.datareader, read thru the whole Access db, test for every Record existing in the SQL-Database, if not i add the record, if yes i go further.

of course, this is to slow ! Best Practice would be that the SQL-Server itself could update his records from the Access-db, so i must not take care of this over my application

Any hints or suggestions ?

Thx
Chris
 
Hi Christian,

I think that this question is more for the SQL server newsgroups themselves,
here you are always using dotNet code whatever program language you use.

The newsgroup
microsoft.public.SQLserver.programming is a very busy newsgroup.

I hope this helps?

Cor
ive a daily generated MS-Access Database with round about 250.000 Records.
Click to expand...
With them i must update a sql-Server DB-Table with three fields.
Actualy i connect to the ms-access DB with an oledb.datareader, read thru
Click to expand...
the whole Access db, test for every Record existing in the SQL-Database, if
not i add the record, if yes i go further.
of course, this is to slow ! Best Practice would be that the SQL-Server
Click to expand...
itself could update his records from the Access-db, so i must not take care
of this over my application
 
The most efficient way of performing this task would be from the
Access mdb itself. Link to the SQLS table that will receive the data,
and write an update (or append) query in Access with a WHERE clause,
depending on your logic. You should get a repsonse time in seconds.
You can also take the linked server approach from SQL Server, and
write the query in T-SQL, although that will probably be (slightly)
slower. Using client code to perform any kind of bulk operations on
the server is always the slowest way possible.

--Mary
 
Back
Top