Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search -Add-Update Data to Access
I thought I had worked out a solution to my coding and correcting a
Looping problems, but it has turned out to be a much greater problem. In our office we have multiple staff members needing to Update and/or Add data to an Access Database from Excel, that is because none of these staff members have direct access to the database, only through Excel. I can access the database, and the proper table and Add data to the Table from the Excel Macro. However, it will add everytime. So if staff add rows later, and push the macro they end up added the same rows over and over. I want the rows which match existing data in the access table to be updated if necessary, and add if not found. Coding is right for the connection to the Database and recordsets. I attempted to use the following : Dim rs As Recordset Dim ColE As Varient ........ For R = 4 to 300 ColE = Worksheets("Contacts").Cells(R,5).Value If ColE = "" Then Exit For End If Do While Not rs.EOF If Worksheets("Contacts").Cells(R,5).Value = rs.Fields("SSN") Then rs.Edit rs.Fields("From") = Worksheets("Contacts").Cells(R,3).Value rs.Fields("Type") = Worksheets("Contacts").Cells(R,4).Value ' etc.etc. (total of 12 fields of data rs.Update Else rs.MoveNext rs.AddNew rs.Fields("From") = Worksheets("Contacts").Cells(R,3).Value rs.Fields("Type") = Worksheets("Contacts").Cells(R,4).Vales ' etc. etc. rs. Update End If Loop Next rs.Close Set rs = Nothing dbs.Close Set dbs = Nothing I can see the problem in that I am looping through the Excel spreadsheet okay, but failing to loop through the Database Table??? No sure how to do this? Thanks for any help. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help How to update linked .xls offline with MS-ACCESS (VB)? | Links and Linking in Excel | |||
Excel update from Access | Excel Discussion (Misc queries) | |||
export access to excel. change access & update excel at same time | Excel Discussion (Misc queries) | |||
Google Search Add-in update | Excel Worksheet Functions | |||
how do i loop in a access search | Excel Programming |