Consulting

Page 4 of 4 FirstFirst ... 2 3 4
Results 61 to 73 of 73

Thread: Solved: Excel to access cell for cell import

  1. #61
    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]

  2. #62
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    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]

  3. #63
    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...

  4. #64
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    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.

  5. #65
    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...

  6. #66
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    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.

  7. #67
    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

  8. #68
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    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.

  9. #69
    is that the format that Access uses for dates set as text??
    Last edited by White_Nova; 12-04-2007 at 07:23 AM.

  10. #70
    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]

  11. #71
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    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 ...) ?

  12. #72
    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

  13. #73
    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
  •