Updating 20k+ rows from Excel to access. vbscript? VBA? SQL?

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Any suggestions?

Some of these rows only need a few columns updated, and others need
all of them updated. If possible if say row 800 column 'S' is filled
in the access DB but is blank on the excel spreadsheet, if possible i
like to keep the existing data in access and over right it with a
blank cell.

The spreadsheet is a export of the access DB, changes were made/merged
from other spreadsheets so the Unique ID is the same and in column A.

A script suggestions to complete this?

Thanks :)
 
dim dbe
dim db
dim ssql

set dbe = createobj("dao.dbengine.36")

set db = dbe.opendatabase("mydatabase")

ssql = "UPDATE Table1 INNER JOIN
[Excel 5.0;HDR=NO;DATABASE=c:\wb3.xls].[RangeOrSheet]
ON Table1.id = Table2.id
SET Table1.nValue = [table2].[avalue];"

db.execute(SSQL)

You may have to use a .35 database (A97)
to get this to work.
 
dude you don't need to use DAO for anything

don't listen do kids that use DAO; they're stuck in the 80s




DAVID said:
dim dbe
dim db
dim ssql

set dbe = createobj("dao.dbengine.36")

set db = dbe.opendatabase("mydatabase")

ssql = "UPDATE Table1 INNER JOIN
[Excel 5.0;HDR=NO;DATABASE=c:\wb3.xls].[RangeOrSheet]
ON Table1.id = Table2.id
SET Table1.nValue = [table2].[avalue];"

db.execute(SSQL)

You may have to use a .35 database (A97)
to get this to work.

Any suggestions?

Some of these rows only need a few columns updated, and others need
all of them updated. If possible if say row 800 column 'S' is filled
in the access DB but is blank on the excel spreadsheet, if possible i
like to keep the existing data in access and over right it with a
blank cell.

The spreadsheet is a export of the access DB, changes were made/merged
from other spreadsheets so the Unique ID is the same and in column A.

A script suggestions to complete this?

Thanks :)
Click to expand...
Click to expand...
 
Back
Top