Menu

ADODB Problem with transferring data from Excel to Access

Search this Thread

#1

Report Post

Posted to microsoft.public.excel.programming

Dick Kusleika[_4_] Dick Kusleika[_4_] is offline

Dick Kusleika[_4_] is offline

external usenet poster

Posts: 595

Default

ADODB Problem with transferring data from Excel to Access

On Fri, 1 Aug 2008 10:45:47 -0700 (PDT), wrote:

Hello,

I want to transfer data from Excel sheet 'DATA' to Access table
'300_APO PRICELIST'.

As you can see in the below script I can only append data to the
Access table, but I also want Access to update the table in case of
already existing data.

I made a few changes to the field names to fit my testing data, so don't
just copy and paste this code.

Do Until MyRecordset.EOF

'Set the record pointer
MyTable.MoveFirst
'This will move the record pointer if code exists
MyTable.Find "[code] = " & [MyRecordset]![code]

'EOF is true if nothing found, so add a new record
If MyTable.EOF Then
MyTable.AddNew
End If

[MyTable]![code] = [MyRecordset]![code]
[MyTable]![Shipto_Customer] = [MyRecordset]![Shipto_Customer]
[MyTable]![Shipto_Customer_Name] =
[MyRecordset]![Shipto_Customer_Name]
[MyTable]![Material_No] = [MyRecordset]![Material_No]
[MyTable]![Material_Name] = [MyRecordset]![Material_Name]
[MyTable]![cal_month] = [MyRecordset]![cal_month]
[MyTable]![Price] = [MyRecordset]![Price]

MyTable.Update

MyRecordset.MoveNext
Loop
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Transferring data from Access to Excel in VBA very slow	Candyman	Excel Programming	0	August 28th 06 06:14 PM
Transferring Data From Access	philhert	Excel Discussion (Misc queries)	2	July 28th 06 02:49 PM
Transferring data from MS Access to MS Excel	Joe	Excel Discussion (Misc queries)	2	April 5th 06 06:24 PM
Access to Excel Automation ADODB Problem	Matt Slattery	Excel Programming	2	September 29th 04 12:59 PM
transferring data from access to excel	dave	Excel Programming	8	September 2nd 03 03:09 PM

All times are GMT +1. The time now is 12:02 PM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement