| | | Guest |  |
|  Posted: Sun Aug 03, 2008 2:02 pm Post subject: Synchronize Excel sheet with Access table |  |
| |  | |
Hello,
I want to synchronize the data in Excel sheet 'DATA' with 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 |
|
