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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search -Add-Update Data to Access
In your Access app:
1. Open the Table in design view 2. Add an index to the table to prevent duplicate records. This is done using the "Indexed" property at the bottom of the screen. It would also help if you had a primary key defined in the table. P "Pete T" wrote in message om... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search -Add-Update Data to Access
Phobos' suggestion to add a primary key to the Access table
would help, but looping the recordset is still needed. Try the following: Dim rs As Recordset Dim ColE As Variant Dim bFound As Boolean . . . . . For R = 4 To 300 ColE = Worksheets("Contacts").Cells(R, 5).Value If ColE = "" Then Exit For bFound = False rs.MoveFirst Do If Worksheets("Contacts").Cells(R, 5).Value = rs.Fields("SSN") Then bFound = True rs.Edit rs.Fields("From") = Worksheets("Contacts").Cells(R, 3).Value rs.Fields("Type") = Worksheets("Contacts").Cells(R, 4).Value ' etc. rs.Update End If rs.MoveNext Loop Until rs.EOF Or bFound = True If bFound = False Then rs.AddNew rs.Fields("From") = Worksheets("Contacts").Cells(R, 3).Value rs.Fields("Type") = Worksheets("Contacts").Cells(R, 4).Vales ' etc. rs.Update End If Next R rs.Close Set rs = Nothing dbs.Close HTH, Merjet |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search -Add-Update Data to Access
Merjet you are the Master... Thanks with some minor adjustment of my
If..Then statement it work bueatifully. Now I need to complicate the If..Then with a double search as we will have multiple users and I need to narrow the search area. Each staff member who will be adding and updating records here has a unique login. The login resides at one location on the Spreadsheet (F1), If I attempt to use the following If..Then : If rs.Fields("Login")=Range("F"&1).Value And _ ColE = rs.Fields(SSN") Then It doesn't work, as the first login value is i.e. "6701" and the F1 Range is 6701 . without quotes. So everything fails to match and the code ends doing nothing? I have heard that there is a way to first create a temporary query sorting out all other Login's then run the If...Then on the SSN only, do you know how? Thanks Again, "merjet" wrote in message .net... Phobos' suggestion to add a primary key to the Access table would help, but looping the recordset is still needed. Try the following: Dim rs As Recordset Dim ColE As Variant Dim bFound As Boolean . . . . . For R = 4 To 300 ColE = Worksheets("Contacts").Cells(R, 5).Value If ColE = "" Then Exit For bFound = False rs.MoveFirst Do If Worksheets("Contacts").Cells(R, 5).Value = rs.Fields("SSN") Then bFound = True rs.Edit rs.Fields("From") = Worksheets("Contacts").Cells(R, 3).Value rs.Fields("Type") = Worksheets("Contacts").Cells(R, 4).Value ' etc. rs.Update End If rs.MoveNext Loop Until rs.EOF Or bFound = True If bFound = False Then rs.AddNew rs.Fields("From") = Worksheets("Contacts").Cells(R, 3).Value rs.Fields("Type") = Worksheets("Contacts").Cells(R, 4).Vales ' etc. rs.Update End If Next R rs.Close Set rs = Nothing dbs.Close HTH, Merjet |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search -Add-Update Data to Access
How about using
If rs.Fields("Login")=Range("F"&1).Text And _ ColE = rs.Fields(SSN") Then the text property should return a text string instead of the actual number value Paul D "Pete T" wrote in message m... Merjet you are the Master... Thanks with some minor adjustment of my If..Then statement it work bueatifully. Now I need to complicate the If..Then with a double search as we will have multiple users and I need to narrow the search area. Each staff member who will be adding and updating records here has a unique login. The login resides at one location on the Spreadsheet (F1), If I attempt to use the following If..Then : If rs.Fields("Login")=Range("F"&1).Value And _ ColE = rs.Fields(SSN") Then It doesn't work, as the first login value is i.e. "6701" and the F1 Range is 6701 . without quotes. So everything fails to match and the code ends doing nothing? I have heard that there is a way to first create a temporary query sorting out all other Login's then run the If...Then on the SSN only, do you know how? Thanks Again, "merjet" wrote in message .net... Phobos' suggestion to add a primary key to the Access table would help, but looping the recordset is still needed. Try the following: Dim rs As Recordset Dim ColE As Variant Dim bFound As Boolean . . . . . For R = 4 To 300 ColE = Worksheets("Contacts").Cells(R, 5).Value If ColE = "" Then Exit For bFound = False rs.MoveFirst Do If Worksheets("Contacts").Cells(R, 5).Value = rs.Fields("SSN") Then bFound = True rs.Edit rs.Fields("From") = Worksheets("Contacts").Cells(R, 3).Value rs.Fields("Type") = Worksheets("Contacts").Cells(R, 4).Value ' etc. rs.Update End If rs.MoveNext Loop Until rs.EOF Or bFound = True If bFound = False Then rs.AddNew rs.Fields("From") = Worksheets("Contacts").Cells(R, 3).Value rs.Fields("Type") = Worksheets("Contacts").Cells(R, 4).Vales ' etc. rs.Update End If Next R rs.Close Set rs = Nothing dbs.Close HTH, Merjet |
Reply |
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 |