W
wjc.vrieling
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
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