ADODB Problem with transferring data from Excel to Access
 

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 spent all afternoon to figure out how to do that but with no
results. Can somebody help?

Thank you in advance!!!

Gr,

Chris

Sub UploadP(Version, EstNumber, MyFilter)

Dim MyConnect As String
Dim MyAccess As String
Dim MyRecordset As ADODB.Recordset
Dim MyRange As String
Dim MySQL As String
Dim MyTable As ADODB.Recordset

MyConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
"Extended Properties=Excel 8.0"

MyAccess = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=F:\Departments\Business Finance\10 - SOURCES\Database
\FHC_Database.mdb"


Set MyTable = New ADODB.Recordset

Set MyTable = Nothing

MySQL = "SELECT DISTINCT [code],[Shipto_Customer],
[Shipto_Customer_Name],[Material_No]," & _
"[Material_Name],[cal_month], [PRICE]" & _
"FROM [DATA$]" & _
"WHERE([Data type] ='APO') and ([Category]='" & MyFilter &
"')"

Set MyRecordset = New ADODB.Recordset
MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly

Set MyTable = New ADODB.Recordset
MyTable.Open "SELECT * FROM [300_APO PRICELIST]", MyAccess,
adOpenDynamic, adLockOptimistic

Do Until MyRecordset.EOF


MyTable.AddNew


[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 year / month] = [MyRecordset]![cal_month]
[MyTable]![Unit price - average] = [MyRecordset]![Price]

MyTable.Update

MyRecordset.MoveNext
Loop

Set MyTable = Nothing
Set MyRecordset = Nothing


End Sub

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