	[x] Posted via EE Mobile
	Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

Question

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

5.2

Updating records from excel to access problem

Asked by sahi0002 in Microsoft Excel Spreadsheet Software, Microsoft Access Database

Hi, I have a database in MS Access.
I have a code for updating my records from excel to access. There is a field CA No and a field "Total Charges". CA No is a text field and total charges ia currency field.

The code below matches the CA No of db with the excel datasheet's CA No column. Whenever CA No matches, a new duplicate record is created for that CA No and all the values of the fields of current record are copied onto the new record. The code was working more or less fine previously. But since I would have so many records with the same CA No after updating the db from excel mulitple times, hence I wanted a date field which will notify me of the date of creation and also only create the duplicate record of that CA No which is the newest ( or rather the latest record out of all the same CA Nos)
The date field is called Created Date.

The query that my module is based on is PBSorted:-

SELECT [PB Listing].[Contract ID], [PB Listing].[CA No], [PB Listing].[Total Charges] and the field list continues...

FROM [PB Listing] INNER JOIN qryLastCA ON [PB Listing].[CA No] = [qryLastCA].[CA No] AND [PB Listing].[Date Created] = [qryLastCA].[MaxCreated]
WHERE [PB Listing].[CA No]
ORDER BY [PB Listing].[CA No];

qryLastCA:-
Select [PB Listing].[CA No], Max([Created Date]) as MaxCreated
FROM [PB Listing]
GROUP BY [PB Listing].[CA No]

View the Solution FREE for 30 Days

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:

           
           
             VBA module (PB Listing is the name of my table):-
 
'UpdateCharges("x","x")
Public Function UpdateCharges(xlPath As String, wsName As String)
wsName = "cons_summary_20081231_starhub l"
 xlPath = "C:\Documents and Settings\Desktop\New Folder\sample.xls"
Stop
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlWs As Excel.Worksheet
'Dim rsData As New ADODB.Recordset
Dim i As Long
Dim fCollection As New Collection
Dim fld As dao.Field
Dim rsData As dao.Recordset
Dim tblName As String
Dim CID As Variant
tblName = "PB Listing" ' Replace this with the name of your table
 
On Error Resume Next
' Try get a handle to a pre-existing copy of Excel
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ' Excel not open
    Set xlApp = New Excel.Application
    xlApp.Visible = True
End If
 
Set xlWb = xlApp.Workbooks.Open(xlPath, , True) ' open the workbook
Set xlWs = xlWb.Worksheets(wsName) ' get the correct worksheet
 
Set rsData = CurrentDb.OpenRecordset("PBSorted")  ' open our table
 
For i = 1 To xlWs.UsedRange.Rows.Count 
 
    rsData.MoveFirst
   CID = DLookup("[Contract ID]", tblName, "[CA No] = " & """" & xlWs.Cells(i, 1).Value & """")
    rsData.FindFirst "[CA No] = " & """" & xlWs.Cells(i, 1).Value & """"   
    Debug.Print rsData.Fields("CA No"), xlWs.Cells(i, 1).Value
    Debug.Print rsData.Fields("Contract ID")
    
    
    If "" = (xlWs.Cells(i, 1).Value) Then
    MsgBox ("Done")
    GoTo funclosethisout
    End If
    For Each fld In rsData.Fields ' keep track of current field values
        fCollection.Add fld.Value, fld.Name
    Next
   'If no match then need the new CA No.
   If rsData.NoMatch Then fCollection("CA No") = xlWs.Cells(i, 1).Value
 '  Stop
    rsData.AddNew ' create our new record
    
    For Each fld In rsData.Fields
        fld.Value = fCollection(fld.Name)       '  fld.Value = fCollection(fld.Name)
    
    Next
  'Stop
    ' reset our collection
    Set fCollection = Nothing
    Set fCollection = New Collection
    
    rsData.Fields("Total  Charges").Value = xlWs.Cells(i, 2).Value ' put in the new value
     
 rsData.Update
Next
funclosethisout:
xlWb.Close
xlApp.ActiveWorkbook.Close
xlApp.ActiveWindow.Close
Set xlApp = Nothing
End Function

           
         

20091021-EE-VQP-81 - Hierarchy / EE_QW_3_20080625

Hall of Fame

1. rorya2,443,610
2. saurabh7262,410,891
3. brettdj1,557,794
4. barryhou...1,280,089
5. chris_bot...897,558
6. matthews...882,565
7. patrickab712,241
8. nutsch528,037
9. zorvek468,027
10. WarCrimes382,121
11. byundt377,266
12. RichardS...359,304
13. pari123346,965
14. jppinto274,933
15. webtubbs262,020
16. ProdOps231,615
17. Cbrine213,117
18. ahammar210,725
19. folderol203,400
20. spattewar200,958
21. riteshparakh193,444
22. bhwithun189,580
23. JONvdHe...169,379
24. tbsgadi154,818
25. epaclm137,431

1. zorvek10,681,647
2. rorya8,516,802
3. byundt8,211,782
4. saurabh7265,032,848
5. matthews...3,987,171
6. brettdj3,271,235
7. patrickab3,116,913
8. jeverist2,907,207
9. chris_bot...2,042,236
10. roos011,928,098
11. webtubbs1,854,710
12. mvidas1,537,253
13. barryhou...1,514,659
14. nutsch1,259,140
15. fanpages1,236,922
16. MalicUK1,060,396
17. ahammar916,414
18. spattewar748,603
19. mgh_mgh...649,110
20. LoNeRaV...602,049
21. ture572,551
22. pari123546,873
23. epaclm495,895
24. jpaulino487,973
25. gtgloner477,227

Updating records from excel to access problem

Asked by sahi0002 in Microsoft Excel Spreadsheet Software, Microsoft Access Database

About this solution