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

On Fri, 1 Aug 2008 10:45:47 -0700 (PDT), (E-Mail Removed) 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