Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update mdb from Excel... (2000 sr 1)

    i have this wbook and mdb.
    My problem is to update the mdb from the Excel file only from column L, M and AA.

    column L into record PROVA12
    column M into record PROVA13
    column AA into record PROVA27

    Into column AB of excel file is present unique ID
    and into record PROVA28 is present unique ID

    i have insert my old macro into module ESPORTA_DA_EXCEL_A_ACCESS but that export all column to all reords....

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update mdb from Excel... (2000 sr 1)

    Do you want to add a new record and fill the fields PROVA12, PROVA13 and PROVA27, or do you want to update an existing record?

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update mdb from Excel... (2000 sr 1)

    ... good question!

    In effect i would want to overwrite if existis a old value if the field, and add a new if the field is blank

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update mdb from Excel... (2000 sr 1)

    Insert the following lines in the INPS_TOTALE procedure between .Update and End If:

    Else
    .Fields("PROVA12") = Range("L" & r).Value
    .Fields("PROVA13") = Range("M" & r).Value
    .Fields("PROVA27") = Range("AA" & r).Value
    .Update

    If you only want to fill PROVA12, PROVA13 and PROVA27 when adding a new record too, remove the lines you don't need between .AddNew and .Update.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update mdb from Excel... (2000 sr 1)

    wow!!!!
    wORK FINE....
    ... dubt, but if i distribute the same excel file with the same module to update a MDB and into same time variuos user update the MDB are a conflict to wtrite into MDB?
    Naturally each user have a excel file with a different ID in AB...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update mdb from Excel... (2000 sr 1)

    Access is a multi-user database, so several users can update records at the same time, as long as they have different IDs.

    But if the number of users that will be updating simultaneously is larger than about 8 or so, the updates will be slow. Access only handles a limited number of simultaneous updates well.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update mdb from Excel... (2000 sr 1)

    oh NO!
    My users are 47!
    Have a strategy....

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update mdb from Excel... (2000 sr 1)

    The important question is not how many users you have in total, but how many users will be updating the database simultaneously (at the same time). If this is more than 8 or so, Access is NOT suitable, you should think about a server-based database system such as SQL Server.

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update mdb from Excel... (2000 sr 1)

    I am sorry, Hans but for reverse mode ACCESS to EXCEL...
    I have this old macro, is possible to import from Access to Excel with the same strategy of Excel to Access
    Same record same rage of axcel...

    Sub IMPORTA_AGENZIE()
    Dim NomeDB As String
    Dim CONT As String

    Set ELENCO = Worksheets("A.T.CAMPANIA")
    NomeDB = gPROVADatabasePath
    CONT = Sheets("A.T.CAMPANIA").Cells(65536, 1).End(xlUp).Row

    Dim StringaDiConnessione
    StringaDiConnessione = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & NomeDB & ";'"
    Dim OggettoConnessione As Object, OggettoRecordset As Object
    Set OggettoConnessione = CreateObject("ADODB.Connection")
    OggettoConnessione.Open StringaDiConnessione
    Set OggettoRecordset = CreateObject("ADODB.Recordset")
    Set OggettoRecordset = OggettoConnessione.Execute("SELECT * from INPS_02")

    Do While Not OggettoRecordset.EOF

    ID = OggettoRecordset("PROVA29")
    Set Found_ID = Sheets("A.T.CAMPANIA").Columns("AB:AB").Find(ID, lookat:=xlWhole)
    If Found_ID Is Nothing Then

    ELENCO.Range("A" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA1")
    ELENCO.Range("B" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA2")
    ELENCO.Range("C" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA3")
    ELENCO.Range("D" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA4")
    ELENCO.Range("E" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA5")
    ELENCO.Range("F" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA6")
    ELENCO.Range("G" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA7")
    ELENCO.Range("H" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA8")
    ELENCO.Range("I" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA9")
    ELENCO.Range("J" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA10")
    ELENCO.Range("K" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA11")
    ELENCO.Range("L" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA12")
    ELENCO.Range("M" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA13")
    ELENCO.Range("N" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA14")
    ELENCO.Range("O" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA15")
    ELENCO.Range("P" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA16")
    ELENCO.Range("Q" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA17")
    ELENCO.Range("R" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA18")
    ELENCO.Range("S" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA19")
    ELENCO.Range("T" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA20")
    ELENCO.Range("U" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA21")
    ELENCO.Range("V" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA22")
    ELENCO.Range("W" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA23")
    ELENCO.Range("X" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA24")
    ELENCO.Range("Y" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA25")
    ELENCO.Range("Z" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA26")
    ELENCO.Range("AA" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA27")
    ELENCO.Range("AB" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA28")
    ELENCO.Range("AC" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA29")

    CONT = CONT + 1
    End If
    OggettoRecordset.MoveNext
    Loop
    OggettoRecordset.Close
    OggettoRecordset.Open "SELECT Count(PROVA29) As Cnt FROM INPS_02", StringaDiConnessione, adOpenKeyset, adLockOptimistic, adCmdText
    Range("I1") = OggettoRecordset!cnt

    OggettoRecordset.Close
    Set OggettoRecordset = Nothing
    OggettoConnessione.Close
    Set OggettoConnessione = Nothing
    End Sub

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update mdb from Excel... (2000 sr 1)

    What [i[exactly[/i] is the question?

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update mdb from Excel... (2000 sr 1)

    My problem is to update the Excel file from the MDB only from column L, M and AA.

    record PROVA12 into column L
    record PROVA27 into column M
    record PROVA28 into column AB

    Into column AC of excel file is present unique ID
    and into record PROVA29 is present unique ID

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update mdb from Excel... (2000 sr 1)

    Above the line End If, insert an Else part, just like the code for the other direction. The row to update is Found_ID.Row

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update mdb from Excel... (2000 sr 1)

    Hans sorry is this correct, the macro not update Excel....

    Sub IMPORTA_AGENZIE()
    Dim NomeDB As String
    Dim CONT As String

    Set ELENCO = Worksheets("A.T.CAMPANIA")
    NomeDB = gPROVADatabasePath
    CONT = Sheets("A.T.CAMPANIA").Cells(65536, 1).End(xlUp).Row

    Dim StringaDiConnessione
    StringaDiConnessione = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & NomeDB & ";'"
    Dim OggettoConnessione As Object, OggettoRecordset As Object
    Set OggettoConnessione = CreateObject("ADODB.Connection")
    OggettoConnessione.Open StringaDiConnessione
    Set OggettoRecordset = CreateObject("ADODB.Recordset")
    Set OggettoRecordset = OggettoConnessione.Execute("SELECT * from INPS_02")

    Do While Not OggettoRecordset.EOF

    ID = OggettoRecordset("PROVA29")
    Set Found_ID = Sheets("A.T.CAMPANIA").Columns("AC:AC").Find(ID, lookat:=xlWhole)
    If Found_ID Is Nothing Then

    CONT = CONT + 1
    Else

    ELENCO.Range("L" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA12")
    ELENCO.Range("M" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA13")
    ELENCO.Range("AB" & Trim(Str(CONT))).Value = OggettoRecordset("PROVA28")

    End If

    OggettoRecordset.MoveNext
    Loop
    OggettoRecordset.Close
    OggettoRecordset.Open "SELECT Count(PROVA29) As Cnt FROM INPS_02", StringaDiConnessione, adOpenKeyset, adLockOptimistic, adCmdText
    Range("I1") = OggettoRecordset!cnt

    OggettoRecordset.Close
    Set OggettoRecordset = Nothing
    OggettoConnessione.Close
    Set OggettoConnessione = Nothing
    End Sub

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update mdb from Excel... (2000 sr 1)

    Why have you removed the code to add a new row if the ID is not found?
    In the Else part, you shouldn't use CONT, but as I wrote in my previous reply Found_ID.Row

  15. #15
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update mdb from Excel... (2000 sr 1)

    Correct?
    Sub IMPORTA_AGENZIE()
    Dim NomeDB As String
    Dim CONT As String

    Set ELENCO = Worksheets("A.T.CAMPANIA")
    NomeDB = gPROVADatabasePath

    'CONT = 2 'heets("A.T.CAMPANIA").Cells(65536, 1).End(xlUp).Row

    Dim StringaDiConnessione
    StringaDiConnessione = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & NomeDB & ";'"
    Dim OggettoConnessione As Object, OggettoRecordset As Object
    Set OggettoConnessione = CreateObject("ADODB.Connection")
    OggettoConnessione.Open StringaDiConnessione
    Set OggettoRecordset = CreateObject("ADODB.Recordset")
    Set OggettoRecordset = OggettoConnessione.Execute("SELECT * from INPS_02")

    Do While Not OggettoRecordset.EOF

    ID = OggettoRecordset("PROVA29")
    Set Found_ID = Sheets("A.T.CAMPANIA").Columns("AC:AC").Find(ID, lookat:=xlWhole)
    If Found_ID Is Nothing Then


    Else

    Found_ID.Row

    ELENCO.Range("L" & Trim(Str(Found_ID.Row))).Value = OggettoRecordset("PROVA12")
    ELENCO.Range("M" & Trim(Str(Found_ID.Row))).Value = OggettoRecordset("PROVA13")
    ELENCO.Range("AB" & Trim(Str(Found_ID.Row))).Value = OggettoRecordset("PROVA28")

    'CONT = CONT + 1

    End If

    OggettoRecordset.MoveNext
    Loop
    OggettoRecordset.Close
    OggettoRecordset.Open "SELECT Count(PROVA29) As Cnt FROM INPS_02", StringaDiConnessione, adOpenKeyset, adLockOptimistic, adCmdText
    Range("I1") = OggettoRecordset!cnt

    OggettoRecordset.Close
    Set OggettoRecordset = Nothing
    OggettoConnessione.Close
    Set OggettoConnessione = Nothing
    End Sub

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •