-
Hi Charlize
Need your brilliant brain again.
I have coding( Below) that im using to update a database with new records bases on a "Day" criteria...
I need to ammend this code to cater for updating as well as adding new records
The logic goes like this
If record exists then
Update records
Else
If Record does not exist then
Add new record
Please help
[VBA]Sub ADOFromExcelToAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\Russell\Desktop\Manager Program\DataStore.mdb;"
Set rs = New ADODB.Recordset
rs.Open "Store", cn, adOpenKeyset, adLockOptimistic, adCmdTable
Columns("A:B").Select
Selection.EntireColumn.Hidden = False
If Range("K1").Value = "Tues" Then
r = 5 'Starts at row specified
Do While (Range("C" & r).Formula) = "Tues"
' repeat until first empty cell in column A
With rs
'If Record.exists = True Then
.update ' updates an existing record
' add values to each field in the record
.Fields("Agent Name") = Range("A" & r).Value
.Fields("Week Number") = Range("B" & r).Value
.Fields("Day") = Range("C" & r).Value
.Fields("Ses") = Range("D" & r).Value
.Fields("Sale Discription") = Range("E" & r).Value
.Fields("Goal Per Ses") = Range("F" & r).Value
.Fields("Lead") = Range("G" & r).Value
.Fields("Pitch") = Range("H" & r).Value
.Fields("Made Sale") = Range("I" & r).Value
.Fields("Sales Can") = Range("J" & r).Value
.Fields("Sales Discription") = Range("K" & r).Value
.Fields("Units Written") = Range("L" & r).Value
.Fields("Goal Forcast") = Range("M" & r).Value
.Fields("Cont No") = Range("N" & r).Value
.Fields("Sus") = Range("O" & r).Value
.Fields("Ds Solid") = Range("P" & r).Value
.Fields("Normal Solid") = Range("Q" & r).Value
'Else
'If Record.exists = False Then
.AddNew ' updates an existing record
' add values to each field in the record
.Fields("Agent Name") = Range("A" & r).Value
.Fields("Week Number") = Range("B" & r).Value
.Fields("Day") = Range("C" & r).Value
.Fields("Ses") = Range("D" & r).Value
.Fields("Sale Discription") = Range("E" & r).Value
.Fields("Goal Per Ses") = Range("F" & r).Value
.Fields("Lead") = Range("G" & r).Value
.Fields("Pitch") = Range("H" & r).Value
.Fields("Made Sale") = Range("I" & r).Value
.Fields("Sales Can") = Range("J" & r).Value
.Fields("Sales Discription") = Range("K" & r).Value
.Fields("Units Written") = Range("L" & r).Value
.Fields("Goal Forcast") = Range("M" & r).Value
.Fields("Cont No") = Range("N" & r).Value
.Fields("Sus") = Range("O" & r).Value
.Fields("Ds Solid") = Range("P" & r).Value
.Fields("Normal Solid") = Range("Q" & r).Value
'End If
'End If
' add more fields if necessary...
.update ' stores the new record
End With
r = r + 1 ' next row
Loop
Else
If Range("K1").Value = "Wed" Then
r = 9 'Starts at row specified
Do While (Range("C" & r).Formula) = "Wed"
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Agent Name") = Range("A" & r).Value
.Fields("Week Number") = Range("B" & r).Value
.Fields("Day") = Range("C" & r).Value
.Fields("Ses") = Range("D" & r).Value
.Fields("Sale Discription") = Range("E" & r).Value
.Fields("Goal Per Ses") = Range("F" & r).Value
.Fields("Lead") = Range("G" & r).Value
.Fields("Pitch") = Range("H" & r).Value
.Fields("Made Sale") = Range("I" & r).Value
.Fields("Sales Can") = Range("J" & r).Value
.Fields("Sales Discription") = Range("K" & r).Value
.Fields("Units Written") = Range("L" & r).Value
.Fields("Goal Forcast") = Range("M" & r).Value
.Fields("Cont No") = Range("N" & r).Value
.Fields("Sus") = Range("O" & r).Value
.Fields("Ds Solid") = Range("P" & r).Value
.Fields("Normal Solid") = Range("Q" & r).Value
.update ' stores the new record
End With
r = r + 1 ' next row
Loop
Else
If Range("K1").Value = "Thurs" Then
r = 13 'Starts at row specified
Do While (Range("C" & r).Formula) = "Thurs"
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Agent Name") = Range("A" & r).Value
.Fields("Week Number") = Range("B" & r).Value
.Fields("Day") = Range("C" & r).Value
.Fields("Ses") = Range("D" & r).Value
.Fields("Sale Discription") = Range("E" & r).Value
.Fields("Goal Per Ses") = Range("F" & r).Value
.Fields("Lead") = Range("G" & r).Value
.Fields("Pitch") = Range("H" & r).Value
.Fields("Made Sale") = Range("I" & r).Value
.Fields("Sales Can") = Range("J" & r).Value
.Fields("Sales Discription") = Range("K" & r).Value
.Fields("Units Written") = Range("L" & r).Value
.Fields("Goal Forcast") = Range("M" & r).Value
.Fields("Cont No") = Range("N" & r).Value
.Fields("Sus") = Range("O" & r).Value
.Fields("Ds Solid") = Range("P" & r).Value
.Fields("Normal Solid") = Range("Q" & r).Value
' add more fields if necessary...
.update ' stores the new record
End With
r = r + 1 ' next row
Loop
Else
If Range("K1").Value = "Frid" Then
r = 17 'Starts at row specified
Do While (Range("C" & r).Formula) = "Frid"
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Agent Name") = Range("A" & r).Value
.Fields("Week Number") = Range("B" & r).Value
.Fields("Day") = Range("C" & r).Value
.Fields("Ses") = Range("D" & r).Value
.Fields("Sale Discription") = Range("E" & r).Value
.Fields("Goal Per Ses") = Range("F" & r).Value
.Fields("Lead") = Range("G" & r).Value
.Fields("Pitch") = Range("H" & r).Value
.Fields("Made Sale") = Range("I" & r).Value
.Fields("Sales Can") = Range("J" & r).Value
.Fields("Sales Discription") = Range("K" & r).Value
.Fields("Units Written") = Range("L" & r).Value
.Fields("Goal Forcast") = Range("M" & r).Value
.Fields("Cont No") = Range("N" & r).Value
.Fields("Sus") = Range("O" & r).Value
.Fields("Ds Solid") = Range("P" & r).Value
.Fields("Normal Solid") = Range("Q" & r).Value
.update ' stores the new record
End With
r = r + 1 ' next row
Loop
Else
If Range("K1").Value = "Sat" Then
r = 21 'Starts at row specified
Do While (Range("C" & r).Formula) = "Sat"
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Agent Name") = Range("A" & r).Value
.Fields("Week Number") = Range("B" & r).Value
.Fields("Day") = Range("C" & r).Value
.Fields("Ses") = Range("D" & r).Value
.Fields("Sale Discription") = Range("E" & r).Value
.Fields("Goal Per Ses") = Range("F" & r).Value
.Fields("Lead") = Range("G" & r).Value
.Fields("Pitch") = Range("H" & r).Value
.Fields("Made Sale") = Range("I" & r).Value
.Fields("Sales Can") = Range("J" & r).Value
.Fields("Sales Discription") = Range("K" & r).Value
.Fields("Units Written") = Range("L" & r).Value
.Fields("Goal Forcast") = Range("M" & r).Value
.Fields("Cont No") = Range("N" & r).Value
.Fields("Sus") = Range("O" & r).Value
.Fields("Ds Solid") = Range("P" & r).Value
.Fields("Normal Solid") = Range("Q" & r).Value
' add more fields if necessary...
.update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End If
End If
End If
End If
End If
Columns("A:B").Select
Selection.EntireColumn.Hidden = True
End Sub[/VBA]
-
To make it simple to know if a record already exists, you could use a helper column to see if the row was already transferred to the database. If helper column has a mark, make selection of unique record and use this recordset to update the recordset, if helpercolumn hasn't got a checkmark, add a recordset.
You're using much the same coding. I would try to change it like this :
1. First see what's in K1 (Mon, Tue, Wed, Thu, Fri)
2. Put this in a stringvariable vDay and use this to do the looping
3. Based on that you store 5 or 9 or ... in r
This is an example of a possible solution (not tested, so be aware, try it first on a trial database)[VBA]Sub ADOFromExcelToAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\Russell\Desktop\Manager Program\DataStore.mdb;"
Set rs = New ADODB.Recordset
rs.Open "Store", cn, adOpenKeyset, adLockOptimistic, adCmdTable
Columns("A:B").Select
Selection.EntireColumn.Hidden = False
'Added a string for the day
Dim vDay As String
vDay = Range("K1").Value
Select Case vDay
Case "Tues"
r = 5
Case "Wed"
r = 9
Case "Thurs"
r = 13
Case "Frid"
r = 17
Case "Sat"
r = 21
End Select
Do While Range("C" & r).Value = vDay
'column M is helper column
If Range("C" & r).Offset(, 10).Value = "Ok" Then
' repeat until first empty cell in column A
With rs
'If Record.exists = True Then
.Update ' updates an existing record
' add values to each field in the record
.Fields("Agent Name") = Range("A" & r).Value
.Fields("Week Number") = Range("B" & r).Value
.Fields("Day") = Range("C" & r).Value
.Fields("Ses") = Range("D" & r).Value
.Fields("Sale Discription") = Range("E" & r).Value
.Fields("Goal Per Ses") = Range("F" & r).Value
.Fields("Lead") = Range("G" & r).Value
.Fields("Pitch") = Range("H" & r).Value
.Fields("Made Sale") = Range("I" & r).Value
.Fields("Sales Can") = Range("J" & r).Value
.Fields("Sales Discription") = Range("K" & r).Value
.Fields("Units Written") = Range("L" & r).Value
.Fields("Goal Forcast") = Range("M" & r).Value
.Fields("Cont No") = Range("N" & r).Value
.Fields("Sus") = Range("O" & r).Value
.Fields("Ds Solid") = Range("P" & r).Value
.Fields("Normal Solid") = Range("Q" & r).Value
Else
'If Record.exists = False Then
.AddNew ' updates an existing record
' add values to each field in the record
.Fields("Agent Name") = Range("A" & r).Value
.Fields("Week Number") = Range("B" & r).Value
.Fields("Day") = Range("C" & r).Value
.Fields("Ses") = Range("D" & r).Value
.Fields("Sale Discription") = Range("E" & r).Value
.Fields("Goal Per Ses") = Range("F" & r).Value
.Fields("Lead") = Range("G" & r).Value
.Fields("Pitch") = Range("H" & r).Value
.Fields("Made Sale") = Range("I" & r).Value
.Fields("Sales Can") = Range("J" & r).Value
.Fields("Sales Discription") = Range("K" & r).Value
.Fields("Units Written") = Range("L" & r).Value
.Fields("Goal Forcast") = Range("M" & r).Value
.Fields("Cont No") = Range("N" & r).Value
.Fields("Sus") = Range("O" & r).Value
.Fields("Ds Solid") = Range("P" & r).Value
.Fields("Normal Solid") = Range("Q" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
End If
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
Columns("A:B").Select
Selection.EntireColumn.Hidden = True
End Sub[/VBA]
-
That looks good, interesting... one question though...
I have a template that i update the database with and it changes according to the agent name i put into it, so how would i determain which records have and have not been put into the database (Helper colomb)???
Its not possible becuase once i change the agents name on the template it will write new data to the database
Calculation = 4 sessions a day, 5 days a week, 52 weeks a year.
That is for 1 agent, there are a changing amount of agents.
What i have tried to do is to add a unique identifier to both Excel and Access, the problem is that i first have to insert data into access to be able to do a check on the code...
-
Can you make a sample of your template and how that you can see when a record is new or not. Or do you work with unique idno. Let's say my file is CHAR for charlize and then 01 for first fileno. How do you know that it's that file. Do they tell it to you ... or do you have to guess ? Or use CHAR-1. With split function it's easier to get the last no.
-
Hi Charlize, dont think that is going to help... its what you do with the template and after thats confusing...
Imagine you have a sheet in Excel.
this sheet is layed out as the one attached.
You then select the week(I1), agent(J1) and day(K1) from a drop down menu.
You then fill in the information in the table.
Once you have done this you click on transfer the whole first section( depending on Day chosen(K1) goes to the database (A5:Q8)...
But say you need to go change something you just entered, so you go back and select the same week, agent and day and change what you need to change (now here comes the tricky part)
you then click on transfer, i would like it to go and see if that record for that week, agent, day exists already, if it does i want it to update but if it doesnt find it then it must add it as a new record.
Please help...
-
Well, after some thinking I've created this little thing to let you see what I mean. The trouble will be, how do you adjust this idea to be used with your template. Hope you'll have some fun with this. Two files in attached zip. They must reside in the same directory.
-
Hi Once again, just a quick one
a sample of my code looks like this:
[VBA]SQL = "SELECT * FROM [Workings]" & _
" Where [Workings].[AgtCode]='" & Range("F3").Value & "'" & _
" And (((Workings.Sesdate) Between #" & Range("A1").Value & "# And #" & Range("A2").Value & "#))"[/VBA]
I would like to know if i can have tyhe dates formatted in my code...
The problem im having is that once the data is in Access and a ask for it in Excel it does not bring back the right data due to the date format being wrong...
1. how do i set the date format in both Excel and access to be the same (Preferably dd/mm/yyy)
Thanks again
-
On the worksheet you fill in the dates in the format of dd/mm/yyyy, like 04/12/2007. In your coding you'll have to rearrange the format like this [VBA]"WHERE MyDate BETWEEN #" & _
Format(Worksheets(1).Range("A1").Text, "yyyy/mm/dd") & "# AND #" & _
Format(Worksheets(1).Range("A2").Text, "yyyy/mm/dd") & "#"[/VBA]But it could be that the in between dates aren't included. You'll have to test on that one.
-
is that the format that Access uses for dates set as text??
Last edited by White_Nova; 12-04-2007 at 07:23 AM.
-
Hi Charlize
I really hope you there...
I have an issue here i need your help on...
Im using the same as before, excel to update Access, i have this code to update data from excel to access, but there are 1 or two fields that are updating with a "-1" instead of the value in Excel...
Please help!!!!!
[VBA]Sub UpdateData()
Dim SQL As String
Dim filenm As String
filenm = (ActiveWorkbook.Path & "\Store.mdb")
Dim conn As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim rs As ADODB.Recordset
Set conn = New ADODB.Connection
Set rst = New ADODB.Recordset
Set rs = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.CursorType = adOpenStatic
rst.LockType = adLockBatchOptimistic
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & filenm & ";Persist Security Info=False"
conn.Open
Sheets("Access").Select
If Range("A2").Value <> "" Then
r = 2 'Starts at row specified
Do While (Range("A" & r).Formula) <> ""
rst.ActiveConnection = conn
rst.Open "SELECT [Store1].[Uni1] FROM [Store1]" & _
" Where [Store1].[Uni1]= '" & Range("AZ" & r).Value & "';"
If (rst.RecordCount > 0) Then
rst.Close
rst.ActiveConnection = conn
rst.Open "Update [Store1] " & _
"set [Club]='" & Range("A" & r).Value & "',[Dev]='" & Range("B" & r).Value & "',[Res]='" & Range("C" & r).Value & _
"',[Unit]='" & Range("D" & r).Value & _
"',[Mod]='" & Range("E" & r).Value & _
"',[Size]='" & Range("F" & r).Value & _
"',[RCI]='" & Range("G" & r).Value & _
"',[Sea]='" & Range("H" & r).Value & _
"',[Wee]='" & Range("I" & r).Value & _
"', [TranSt]='" & Range("J" & r).Value & _
"', [ShaCertno]='" & Range("K" & r).Value & _
"', [StocSource]='" & Range("L" & r).Value & _
"', [StartDate]='" & Range("M" & r).Value & _
"', [FinDate]='" & Range("N" & r).Value & _
"', [WeekType]='" & Range("O" & r).Value & _
"', [ArrDate]='" & Range("P" & r).Value & _
"', [Other2007]='" & Range("AC" & r).Value & "', [paidother2007]='" & Range("AD" & r).Value & "', [RentBud2008]='" & Range("AU" & r).Value & "', [RentPaid2008]='" & Range("AV" & r).Value & "', [PaidRent2008]='" & Range("AW" & r).Value & "', [RentInvNo2008]='" & Range("AX" & r).Value & "', [OustRent2008]='" & Range("AY" & r).Value & "', [Uni1]='" & Range("AZ" & r).Value & "', [RentBud2007]='" & Range("AE" & r).Value & "', [RentPaid2007]='" & Range("AF" & r).Value & "', [PaidRent2007]='" & Range("AG" & r).Value & "', [RentinvNo2007]='" & Range("AH" & r).Value & "', [OustRent2007]='" & Range("AI" & r).Value & "', [Levy2006]='" & Range("AJ" & r).Value & "', [ResCode]='" & Range("AK" & r).Value & "', [LevyBud2008]='" & Range("AL" & r).Value & "', [LevyPaid2008]='" & Range("AM" & r).Value & "', [PaidLevy2008]='" & Range("AN" & r).Value & "', [InvNo2008]='" & Range("AO" & r).Value & "', [OustLevy2008]='" & Range("AP" & r).Value & "', [SpecLevy2008]='" & Range("AQ" & r).Value & _
"', [DepDate]='" & Range("Q" & r).Value & _
"', [OrigCurrency]='" & Range("R" & r).Value & _
"', [StocAgNo]='" & Range("S" & r).Value & "', [PaidSpecLevy2008]='" & Range("AR" & r).Value & "', [Other2008]='" & Range("AS" & r).Value & "', [PaidOther2008]='" & Range("AT" & r).Value & _
"', [ManFee]='" & Range("T" & r).Value & "' & [MemFee]='" & Range("U" & r).Value & _
"', [LevyBud2007]='" & Range("V" & r).Value & _
"', [LevyPaid2007]='" & Range("W" & r).Value & "' & [PaidLevy2007]='" & Range("X" & r).Value & "' & [InvNo2007]='" & Range("Y" & r).Value & "' & [OustLevy2007]='" & Range("Z" & r).Value & "', [SpecLevy2007]='" & Range("AA" & r).Value & "', [PaidSpecLevy2007]='" & Range("AB" & r).Value & _
"', [ResortCodeID]='" & Range("BB" & r).Value & _
"', [ClubID]='" & Range("BA" & r).Value & "', [Type]='" & Range("BC" & r).Value & _
"' WHERE [Store1].[Uni1]='" & Range("AZ" & r).Value & "'"
Else
rst.Close
rs.Open "Store1", conn, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Club") = Range("A" & r).Value
.Fields("Dev") = Range("B" & r).Value
.Fields("Res") = Range("C" & r).Value
.Fields("Unit") = Range("D" & r).Value
.Fields("Mod") = Range("E" & r).Value
.Fields("Size") = Range("F" & r).Value
.Fields("RCI") = Range("G" & r).Value
.Fields("Sea") = Range("H" & r).Value
.Fields("Wee") = Range("I" & r).Value
.Fields("TranSt") = Range("J" & r).Value
.Fields("ShaCertNo") = Range("K" & r).Value
.Fields("StocSource") = Range("L" & r).Value
.Fields("StartDate") = Range("M" & r).Value
.Fields("FinDate") = Range("N" & r).Value
.Fields("WeekType") = Range("O" & r).Value
.Fields("ArrDate") = Range("P" & r).Value
.Fields("DepDate") = Range("Q" & r).Value
.Fields("OrigCurrency") = Range("R" & r).Value
.Fields("StocAgNo") = Range("S" & r).Value
.Fields("ManFee") = Range("T" & r).Value
.Fields("MemFee") = Range("U" & r).Value
.Fields("LevyBud2007") = Range("V" & r).Value
.Fields("LevyPaid2007") = Range("W" & r).Value
.Fields("PaidLevy2007") = Range("X" & r).Value
.Fields("InvNo2007") = Range("Y" & r).Value
.Fields("OustLevy2007") = Range("Z" & r).Value
.Fields("SpecLevy2007") = Range("AA" & r).Value
.Fields("PaidSpecLevy2007") = Range("AB" & r).Value
.Fields("Other2007") = Range("AC" & r).Value
.Fields("Paidother2007") = Range("AD" & r).Value
.Fields("RentBud2007") = Range("AE" & r).Value
.Fields("RentPaid2007") = Range("AF" & r).Value
.Fields("PaidRent2007") = Range("AG" & r).Value
.Fields("RentInvNo2007") = Range("AH" & r).Value
.Fields("OustRent2007") = Range("AI" & r).Value
.Fields("Levy2006") = Range("AJ" & r).Value
.Fields("ResCode") = Range("AK" & r).Value
.Fields("LevyBud2008") = Range("AL" & r).Value
.Fields("LevyPaid2008") = Range("AM" & r).Value
.Fields("PaidLevy2008") = Range("AN" & r).Value
.Fields("InvNo2008") = Range("AO" & r).Value
.Fields("OustLevy2008") = Range("AP" & r).Value
.Fields("SpecLevy2008") = Range("AQ" & r).Value
.Fields("PaidSpecLevy2008") = Range("AR" & r).Value
.Fields("Other2008") = Range("AS" & r).Value
.Fields("PaidOther2008") = Range("AT" & r).Value
.Fields("RentBud2008") = Range("AU" & r).Value
.Fields("RentPaid2008") = Range("AV" & r).Value
.Fields("PaidRent2008") = Range("AW" & r).Value
.Fields("RentInvno2008") = Range("AX" & r).Value
.Fields("OustRent2008") = Range("AY" & r).Value
.Fields("Uni1") = Range("AZ" & r).Value
.Fields("ClubID") = Range("BA" & r).Value
.Fields("ResortCodeID") = Range("BB" & r).Value
.Fields("Type") = Range("BC" & r).Value
.Update
End With
' stores the new record
rs.Close
End If
r = r + 1 ' next row
Loop
End If
End Sub[/VBA]
-
Which of those fields are getting the wrong values. What is the value in excel and how are those fields defined in the accesstable (date, boolean, text, number ...) ?
-
Morning Charlize
The field i can see are ManFee and Memfee
They are set as "text" in access (have tried "Number" too)
The values are defined by what is inserted in excel, Eg 1000
Thanks
-
any answers to this one???
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules