Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADODB Problem with transferring data from Excel to Access
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transferring data from Access to Excel in VBA very slow | Excel Programming | |||
Transferring Data From Access | Excel Discussion (Misc queries) | |||
Transferring data from MS Access to MS Excel | Excel Discussion (Misc queries) | |||
Access to Excel Automation ADODB Problem | Excel Programming | |||
transferring data from access to excel | Excel Programming |