Inserimento record db da excel
Ely
Praticamente legge un tot di campi su excel e li inserisce in un db access.
L'unico problema è che, non so come gestire il controllo di scrittura:
Spiego meglio:
il mio db access ha 3 colonne lo stesso vale per excel:
Nome
Cognome
Indirizzo
Se io cancello tutti i record dal db e da xl ed inserisco
Elisa - Betta - Via Casilina (RM)
Lancio la macro ed access riceve i dati nella sua tabella (bp).
Adesso in B1 inserisco:
Francesco - Sammiveroni - Via Appia (RM)
quindi avro:
in riga A Elisa - Betta - Via Casilina (RM)
in riga B Francesco - Sammiveroni - Via Appia (RM)
Vado ad inserire e su Access ho:
Elisa - Betta - Via Casilina (RM)
Elisa - Betta - Via Casilina (RM)
Francesco - Sammiveroni - Via Appia (RM)
Come posso dire ad access che se trova in input una riga che già ha, non la
deve sovrascrivere?
La mia query:
"INSERT INTO bp (nome,cnome,indi) VALUES ('pippo', 'pluto', 'salaria 11');"
Grazie un saluto a tutto il NG!
E.
Mauro Gamberini
> access.
> Praticamente legge un tot di campi su excel e li inserisce in un db
> access.
>
In che *mondo* vivi?
No, non te la prendere. Intendo:
dove siamo? In ADO, in DAO?
Altrove?
Guarda qui:
http://www.erlandsendata.no/english/index.php?t=envbadac
E, più modestamente, qui(è una cosa vecchia):
http://www.webalice.it/maurogsc/esempi/xlsvbaExcelAccess.zip
--
---------------------------
Mauro Gamberini
http://www.riolab.org/
http://blog.maurogsc.eu/
Ely
Ho letto il primo link ma non ho trovato un esempio adattabile alla mia
situazione, vediamo il secondo.
Già che ci siamo, com'è la sintassi per passare variabili nel campo value?
Mauro Gamberini
> situazione, vediamo il secondo.
>
Io forse non ho capito cosa devi fare.
Inoltre, hai una chiave primaria sul db?
> Già che ci siamo, com'è la sintassi per passare variabili nel campo value?
>
Se spieghi un po' meglio, grazie. Campo value sarebbe?
Ely
> > Ho letto il primo link ma non ho trovato un esempio adattabile alla mia
> > situazione, vediamo il secondo.
> >
>
> Io forse non ho capito cosa devi fare.
> Inoltre, hai una chiave primaria sul db?
Si! la chiave primaria è un numero che rappresenta la scheda della rubrica:
esempio
cella a1(di excel)
1 - marco - ciao - roma
2 - franco - hey - milano
3 - beppe - grillo - genova
e cosi via
stessa cosa ho sul db access
la cosa che deve fare la mia routine è:
se la scheda 1 è presente nel DB Access non la inserire, se è diversa,
aggiornala, altrimenti se non esiste, inseriscila.
> > Già che ci siamo, com'è la sintassi per passare variabili nel campo value?
> >
>
> Se spieghi un po' meglio, grazie. Campo value sarebbe?
intendo quando compongo la stringa, s="insert into table (nome,cognome)
values (variabile1, variabile2);"
devo usare " ' " & " ' " quindi comporre manualmente la stringa giusto?
robb...@gmail.com
quello che tu fai credo, visto i risultati è eseguire
una query di accodamento passando ogni volta
tutti i record di excel, ma quello che scrivi fa una cosa
diversa ovvero accoda un solo record.
dovresti aggiungere il codice completo che utilizzi,
dovresti spiegare bene cosa vuoi fare, perchè da quello
che hai spiegato non mi sembra tu stia seguendo
la strada giusta.
tu lanci la query da vb o direttamente da access ovvero
il codice sql viene eseguito col doppio click su access
o da una routine?
sopratutto cosa vuoi ottenere, e perchè vuoi ottenerlo.
saluti
r
Mauro Gamberini
Direi che Excel è molto marginale in tutto questo.
Per la sintassi SQL e Access:
microsoft.public.it.office.access
> se la scheda 1 è presente nel DB Access non la inserire, se è diversa,
> aggiornala, altrimenti se non esiste, inseriscila.
>
http://www.w3schools.com/SQL/default.asp
robb...@gmail.com
> Già che ci siamo, com'è la sintassi per passare variabili nel campo value?
se chiami la query da Excel tramite vb devi semplicemente
ricostruire la stringa sql sostituendo a 'pippo' range("A1") per
esempio
"INSERT INTO bp (nome,cnome,indi) VALUES (" & range("a1") & ",'pluto',
'salaria 11');"
se la chiami da access sempre tramite vb
dovrai aggiungere i riferimenti al file excel
settando l'applicazione excel, la cartella, il foglio
saluti
r
Ely
Excel: foglio 2
ci sono dei dati in una tabella.
Acess: c'è una tabella.
La prima volta apro excel lancio la routine (vedi sotto)
Il DB è vuoto, excel scrive tutto. salva ed esce. OK!
Il giorno seguente riapro Excel, vado nel foglio2 ed inserisco una nuova
riga in fondo a quelle già esistenti.
Lancio la scrittura e access si posiziona all'ultimo rigo del DB riscrive
tutto aggiungendo a quei campi già esistenti!
Quello che vorrei è che access ripartisse dalla prima riga e dicesse:
questa riga ce l'ho uguale? si, allora non l'aggiungo.
E' diversa? si! l'aggiorno.
E' NUOVA? si! l'aggiungo!
e cosi via
Ecco la sudata routine hehe
sub ra()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.CursorLocation = adUseClient
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c.mdb"
rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM bp", cn, adOpenKeyset, adLockOptimistic, adCmdText
With rs
lultriga = Worksheets("Foglio2").range("A" &
Rows.Count).End(xlUp).Row
For i = 1 To lultriga
.AddNew
.Fields("numero") = Worksheets("Foglio2").range("a" & i).Value
.Fields("tipo") = Worksheets("Foglio2").range("b" & i).Value
.Fields("persone") = Worksheets("Foglio2").range("c" &
i).Value
.Fields("primo") = Worksheets("Foglio2").range("d" & i).Value
.Fields("secondo") = Worksheets("Foglio2").range("e" &
i).Value
.Fields("data") = Worksheets("Foglio2").range("r" & i).Value
.Update
salta:
Next
End Withrs.Close
cn.Close
end sub
r
"Ely" ha scritto:
ok adesso è molto più chiaro,
hoperò un'altra domanda, hai detto che
hai una chiave primaria, non ti va in error?
ovvero suppongo che tu passi al db oltre ai
dati anche l'id quindi dovrebbe restituire un
errore ...
se è così basta gestire l'errore
in alternativa come fai a decidere se un
record è doppio?
nome e cognome potrebbe andare bene?
saluti
r
Ely
> hai una chiave primaria, non ti va in error?
> ovvero suppongo che tu passi al db oltre ai
> dati anche l'id quindi dovrebbe restituire un
> errore ...
> se è così basta gestire l'errore
> in alternativa come fai a decidere se un
> record è doppio?
> nome e cognome potrebbe andare bene?
>
> saluti
> r
Ciao r.
Ho messa per bene la chiave primaria sull'id e come hai detto te mi dà errore.
Come posso gestire l'errore in maniera tale che possa verificare se l'intero
record è uguale ed in caso andare al prossimo, oppure aggiornarlo, oppure
inserirlo da 0?
Ely!
r
> Ciao r.
> Ho messa per bene la chiave primaria sull'id e come hai detto te mi dà errore.
> Come posso gestire l'errore in maniera tale che possa verificare se l'intero
> record è uguale ed in caso andare al prossimo, oppure aggiornarlo, oppure
> inserirlo da 0?
>
> Ely!
>
non ho capito cosa vuoi fare se hai l'errore
(e quindi record doppio) :
1) vuoi aggiornare il record con gli ultimi dati
2) proseguire
verificare se i dati sono uguali non serve a nulla
se non sai cosa devi fare dopo, ovvero supponiamo
che venga restituito l'errore e verifichi se i dati sono
uguali:
nel caso sono uguali -> prosegui
nel caso sono diversi ? devi sapere prima cosa fare,
in alternativa potresti avere un messaggio che
ti chiede cosa vuoi fare (ma lo sconsiglierei)
p.s.
il numero dell'errore che viene restituito?
saluti
r
Ely
> (e quindi record doppio) :
> 1) vuoi aggiornare il record con gli ultimi dati
> 2) proseguire
>
> verificare se i dati sono uguali non serve a nulla
> se non sai cosa devi fare dopo, ovvero supponiamo
> che venga restituito l'errore e verifichi se i dati sono
> uguali:
> nel caso sono uguali -> prosegui
> nel caso sono diversi ? devi sapere prima cosa fare,
> in alternativa potresti avere un messaggio che
> ti chiede cosa vuoi fare (ma lo sconsiglierei)
>
> p.s.
> il numero dell'errore che viene restituito?
Ciao r, immagino mi stia rendendo ridicola, non solo a non sapere fare cosa
voglio fare, ma nemmeno a spiegerlo :( chiedo scusa per questo se ti/vi
faccio perdere del tempo.
Come si faceva a scuola, provo a spiegarlo con parole mie, perchè nella mia
testa quello che vorrei fare è semplice ma spiegarlo ad altri non lo è affato
:D, e non a causa vostra.
Dunque dunque:
Ti aggiorno che impostando la chiave primaria sull'ID mi dà errore di
run-time -2147467259 (80004005) - duplicazione blablabla, quando cerco di
dublicare una chiave primaria già inserita. Ed è GIUSTO!
Non deve duplicare se la voce
1 - marco - rossi - roma esiste già! deve semplicemente controllare che
quello che voglio inserire sia -la sua copia esatta!- ed in questo caso và
oltre e lascia invariato.
Altrimenti
Se ad esempio su access ho:
1 - marco - rossi - roma (con 1 chiave primaria)
e su excel ho:
1 - marco - rossi - milano (metti caso ha cambiato residenza il sg marco)
NON deve aggiungerne una nuova, Non deve andare oltre, ma deve cambiare NEL
DB access la città roma con milano!
Quindi ricapitolo ancora!
Su access ho una tabella con 2 righe di dati
1 - marco - rossi - milano
2 - beppe - grillo - genova
su excel ho 3 righe sul mio foglio
1 - marco - rossi - milano
2 - beppe - grillo - torino
3 - marco - rossi - roma
Con un ciclo for gli faccio scorrere le righe e passo ad access il primo
record.
1 - marco - rossi - milano il DB access si accorge che l'ID 1 esiste già,
allora CONTROLLA che sia identico al 100% (intendo tutti i campi, sia l'ID,
che marco, che rossi, che milano) in questo caso lo è, quindi non fà nulla,
ed excel gli spara il successivo.
2 - beppe - grillo - torino.
Access dice: ok allora, nella riga corrispondente all' ID (chiave primaria)
ID2 ce l'ho, beppe pure, grillo anche, e torino?? no!! torino è cambiato
(aggiornato) prima era genova!. Allora AGGIORNIAMO l'intero record con questo
nuovo di excel! via col prossimo.
3 - marco - rossi - roma
Ok questo non ho nemmeno l'id 3! lo inserisco tutto!
Tutto quà! Spero di essere stata chiara, e spero si possa fare quello che
chiedo :D
Un bes
r
>
> Dunque dunque:
>
> Ti aggiorno che impostando la chiave primaria sull'ID mi dà errore di
> run-time -2147467259 (80004005) - duplicazione blablabla, quando cerco di
> dublicare una chiave primaria già inserita. Ed è GIUSTO!
> Non deve duplicare se la voce
>
> 1 - marco - rossi - roma esiste già! deve semplicemente controllare che
> quello che voglio inserire sia -la sua copia esatta!- ed in questo caso và
> oltre e lascia invariato.
> Altrimenti
>
> Se ad esempio su access ho:
> 1 - marco - rossi - roma (con 1 chiave primaria)
> e su excel ho:
> 1 - marco - rossi - milano (metti caso ha cambiato residenza il sg marco)
>
> NON deve aggiungerne una nuova, Non deve andare oltre, ma deve cambiare NEL
> DB access la città roma con milano!
>
ok appena ho 5 minuti scrivo-testo-posto
sempre che qualcuno non risponda prima
saluti
r
Ely
Spero un giorno di poter ricambiare :)
Ely
Mauro Gamberini
> quello che voglio inserire sia -la sua copia esatta!- ed in questo caso và
> oltre e lascia invariato.
>
Ho una domanda.
Se mai avrai 1.000.000 di record,
che fai tutte le volte quando aggiungi
un nuovo utente, un bell'avanti e indietro
fra foglio e db?
Non è meglio inserire un utente per volta
nel db?
Perchè la neccessità di far viaggiare tutti i dati
da e per Access?
E ripeto che la tua domanda con Excel ha a
che fare marginalmente.
Qui:
microsoft.public.it.office.access
ti possono aiutare meglio.
r
"Ely" ha scritto:
ho provato a gestire l'errore ma non capisco come mai
una volta entrato in error non recepisce le modifiche,
quindi ho seguito una strada diversa
ho provato su db (C:\Prova\prova.mdb) con tabella bp
così strutturata:
id (intero lungo e chiave primaria)
nome (testo)
cognome (testo)
indirizzo (testo)
nel foglio2 della cartella, i dati a partire da riga 1 in
A id
B nome
C cognome
D indirizzo
cosa fa la routine:
scorre tutti i record del recordset e verifica se già c'è
un id uguale, si potrebbe anche utilizzare un filtro, comunque ...
se lo trova modifica i vecchi dati con i nuovi
se non trova aggiunge il record
prova e fai sapere:
Sub aggiorna_record()
Dim rst1 As ADODB.Recordset
Dim rng As Excel.Range
Dim Rt As Long
Dim i As Long
Dim arr As Variant
Dim b As Boolean
Set rst1 = New ADODB.Recordset
Set rng = ThisWorkbook.Worksheets("Foglio2").Range("a1")
rst1.ActiveConnection = "provider=microsoft.jet.oledb.4.0;" & _
"data source= C:\Prova\prova.mdb;" '<<da cambiare
rst1.Open "bp", , adOpenKeyset, adLockPessimistic
Rt = UltimaRiga(rng.Parent)
On Error Resume Next
For i = 1 To Rt
With rng.Parent
arr = Array(.Range("a" & i).Value, _
.Range("b" & i).Value, _
.Range("c" & i).Value, _
.Range("d" & i).Value)
End With
With rst1
.MoveFirst
Do Until .EOF
If .Fields("id") = arr(0) Then
.Fields("nome") = arr(1)
.Fields("cognome") = arr(2)
.Fields("indirizzo") = arr(3)
.Update
b = True
Exit Do
End If
.MoveNext
Loop
If b Then
b = False
Else
.AddNew
.Fields("id") = arr(0)
.Fields("nome") = arr(1)
.Fields("cognome") = arr(2)
.Fields("indirizzo") = arr(3)
.Update
End If
End With
Next
rst1.Close
Set rst1 = Nothing
End Sub
Function UltimaRiga(Optional sh As Worksheet, _
Optional rng As Range) As Long
'By Norman Jones modificata restituisce
'l'ultima riga valorizzata
'restituisce 0 se il foglio è pulito
'passando Sh verrà ignorato Rng
'passando Rng verrà ignorato Sh
'non passando argomenti verrà ricercata
'l'ultima riga valorizzata del foglio
'attivo
'utilizzata come UDF è consigliabile
'passare Rng
If sh Is Nothing Then
If rng Is Nothing Then
Set rng = [a1].Parent.UsedRange
End If
Else
Set rng = sh.UsedRange
End If
On Error Resume Next
UltimaRiga = rng.Find(What:="*", _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
saluti
r
Mauro Gamberini
> una volta entrato in error non recepisce le modifiche,
>
Dim sh As Worksheet
Dim lng As Long
'....................................
Set sh = Worksheets("Foglio1")
cn.CursorLocation = adUseClient
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
ThisWorkbook.Path & "\db11.mdb"
rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM Tabella1", cn, adOpenKeyset,
adLockOptimistic, adCmdText
For lng = 1 To 5
With rs
.AddNew
.Fields("id") = sh.Cells(lng, 1).Value
.Fields("nome") = sh.Cells(lng, 2).Value
On Error Resume Next
.Update
End With
Next
'....................................
Mauro Gamberini
Accidenti partito il post....
....voleva essere:
Dim sh As Worksheet
Dim lng As Long
Dim lUlrRiga As Long
'....................................
Set sh = Worksheets("Foglio1")
lUltRiga = sh.Range("A" & Rows.count).end(Xlup).row
'...............................................
For lng = 1 To lUltRida
With rs
.AddNew
.Fields("id") = sh.Cells(lng, 1).Value
.Fields("nome") = sh.Cells(lng, 2).Value
On Error Resume Next
.Update
End With
Next
'..................................................
Ovviamente ho provato su di un db con solo 2 campi,
id e nome.
Credo però non sia difficile da capire.
--
---------------------------
Mauro Gamberini
http://www.riolab.org/
http://blog.maurogsc.eu/
"Mauro Gamberini" <maurogsc...@RIMUOVEREaliceposta.it> ha scritto nel
messaggio news:uHheAoTY...@TK2MSFTNGP03.phx.gbl...
r
> <cut>
> Accidenti partito il post....
> .....voleva essere:
>
> Dim sh As Worksheet
> Dim lng As Long
> Dim lUlrRiga As Long
>
> '....................................
>
> Set sh = Worksheets("Foglio1")
> lUltRiga = sh.Range("A" & Rows.count).end(Xlup).row
> '...............................................
>
> For lng = 1 To lUltRida
>
> With rs
> .AddNew
> .Fields("id") = sh.Cells(lng, 1).Value
> .Fields("nome") = sh.Cells(lng, 2).Value
> On Error Resume Next
> .Update
> End With
>
> Next
>
> '..................................................
>
> Ovviamente ho provato su di un db con solo 2 campi,
> id e nome.
> Credo però non sia difficile da capire.
Mauro non è questo il problema, a parte che non
capisco che senso ha posizionare lì on error, casomai
all'inizio del ciclo, ma tornando al problema, non è
di caricare tutti i record con id diverso! ely chiedeva di più,
ho provato a gestire l'errore più o meno così:
On Error Resume Next
For lng = 1 To lUltRida
With rs
.AddNew
.Fields("id") = sh.Cells(lng, 1).Value
.Fields("nome") = sh.Cells(lng, 2).Value
.Update
End With
if err then
if err.number=-2147467259 then
err.clear
'qui inserivo il codice per far sovrascrivere
'i campi ma restituiva ancora errore -2147467259
'come se comunque l'errore non fosse risolto
end if
end if
Next
non saprei, ho fatto solo un paio di tentativi
poi ho optato per la soluzione già postata
saluti
r
Ely
Grazie Mauro e grazie R. adesso mi metto all'opera e testo tutte le varie
routine che mi avete -GENTILMENTE- regalato.
Vi farò sapere oggi pomeriggio :D
Per adesso grazie di cuore :)
Ely
Mauro Gamberini
Queste le risposte ottenute da Ely sull'ng di Access:
E questa una risposta ad un quesito simile sempre sullo
stesso ng:
r
oltre alla soluzione che ti ho già postato
ho nel frattempo capito perchè non riuscivo
a gestire l'errore (mi ero dimenticato di
cancellare l'update), quindi ecco anche la
soluzione sfruttando la gestione dell'errore:
Sub aggiorna_record()
Dim rst1 As ADODB.Recordset
Dim rng As Excel.Range
Dim Rt As Long
Dim i As Long
Dim arr As Variant
Dim arrT As Variant
Set rst1 = New ADODB.Recordset
Set rng = ThisWorkbook.Worksheets("Foglio2").Range("a1")
rst1.ActiveConnection = "provider=microsoft.jet.oledb.4.0;" & _
"data source= C:\Prova\prova.mdb;" '<<da cambiare
rst1.Open "bp", , adOpenKeyset, adLockPessimistic
Rt = UltimaRiga(rng.Parent)
On Error Resume Next
arrT = Array("id", "nome", "cognome", "indirizzo")
For i = 1 To Rt
With rng.Parent
arr = Array(.Range("a" & i).Value, _
.Range("b" & i).Value, _
.Range("c" & i).Value, _
.Range("d" & i).Value)
End With
With rst1
.AddNew arrT, arr
.Update
If Err Then
If Err.Number = -2147217887 Then
Err.Clear
.CancelUpdate
.Filter = arrT(0) & " = '" & CLng(arr(0)) & "'"
.Fields(arrT(1)) = arr(1)
.Fields(arrT(2)) = arr(2)
.Fields(arrT(3)) = arr(3)
.Update
.Filter = adFilterNone
End If
End If
End With
Next
On Error GoTo 0
Mauro Gamberini
>
Forse non ho capito cosa non deve duplicare.
> a parte che non
> capisco che senso ha posizionare lì on error,
>
Quell'errore verrà generato al momento dell'Update
(se sto duplicando l'id/chiave). Prima/dopo posso
voler gestire diversamente la cosa.
Ripeto ancora che non ho capito
che cosa deve fare l' OP.