this macro import a data from an access mdb all is ok!

My problem is:

if i change a record in variuos sheets of wbook is possible to updat
the relative record in the access mdb?

For example insert a button,associated a macro, on the sheet "Update t
Access"...


Code
-------------------
Sub EstrazioneDati()

' --- Esatto nome del Database completo del percorso
' --- nel caso il Database specificato non esista nel percorso, la macro genera un'errore
' --- verificare quindi l'esistenza del Database

Dim NomeDB As String

NomeDB = "\\srv04f9494rmtb\asp\TEMP\DBDELIVERY.MDB"

Dim StringaDiConnessione
StringaDiConnessione = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & NomeDB & ";'"

Dim OggettoConnessione As Object, OggettoRecordsetUno, oggettoRecordsetDue As Object
Set OggettoConnessione = CreateObject("ADODB.Connection")
OggettoConnessione.Open StringaDiConnessione
Set OggettoRecordsetUno = CreateObject("ADODB.Recordset")
Set oggettoRecordsetDue = CreateObject("ADODB.Recordset")

' --- in questa istruzione SQL è possibile impostare condizioni di estrazione nell'argomento Where
' --- in questo caso vengono estratti solo gli Articoli con Qtà positiva

Set OggettoRecordsetUno = OggettoConnessione.Execute("SELECT * from TABELLA1 WHERE ID0")
Set oggettoRecordsetDue = OggettoConnessione.Execute("SELECT * from Tabella2 WHERE ID0")

Sheets("ANAGRAFICA").Select

Range("A4:N65536").ClearContents ' --- Pulisce la zona dove vengono estrapolati i dati del Database

Range("A4").Select ' --- Prima riga di Excel dove vengono incollati i dati

Do While Not OggettoRecordsetUno.EOF

' --- da notare che nelle istruzioni sotto Codice - Descrizione - Qtà corrispondono ai campi della Tabella articoli

ActiveCell = OggettoRecordsetUno("ID")
ActiveCell.Offset(0, 1) = OggettoRecordsetUno("DATA")
ActiveCell.Offset(0, 2) = OggettoRecordsetUno("DENOMINAZIONESOCIALE")
ActiveCell.Offset(0, 3) = OggettoRecordsetUno("SPORTELLO")
ActiveCell.Offset(0, 4) = OggettoRecordsetUno("NUMCONTRATTO")
ActiveCell.Offset(0, 5) = OggettoRecordsetUno("TIPOCONTRATTO")
ActiveCell.Offset(0, 6) = OggettoRecordsetUno("COGNOMEDESTINATARIO") & " " & OggettoRecordsetUno("NOMEDESTINATARIO")
ActiveCell.Offset(0, 7) = OggettoRecordsetUno("INDIRIZZO")
ActiveCell.Offset(0, 8) = OggettoRecordsetUno("CITTA")
ActiveCell.Offset(0, 9) = OggettoRecordsetUno("CAP")
ActiveCell.Offset(0, 10) = OggettoRecordsetUno("PROVINCIA")
ActiveCell.Offset(0, 11) = OggettoRecordsetUno("PREFISSO") & "/" & OggettoRecordsetUno("NUMEROTELEFONICO")
ActiveCell.Offset(0, 12) = OggettoRecordsetUno("NUMEROLETTORI")
ActiveCell.Offset(0, 13) = OggettoRecordsetUno("STATOLAVORAZIONE")
ActiveCell.Offset(1, 0).Select

OggettoRecordsetUno.Movenext

Loop

Range("A4").Select

Sheets("SMART_CARD").Select

Range("A4:H65536").ClearContents ' --- Pulisce la zona dove vengono estrapolati i dati del Database

Range("A4").Select ' --- Prima riga di Excel dove vengono incollati i dati

Do While Not oggettoRecordsetDue.EOF

' --- da notare che nelle istruzioni sotto Codice - Descrizione - Qtà corrispondono ai campi della Tabella articoli
ActiveCell = oggettoRecordsetDue("ID")
ActiveCell.Offset(0, 1) = oggettoRecordsetDue("DATA")
ActiveCell.Offset(0, 2) = oggettoRecordsetDue("COGNOMEFIRMATARIO") & " " & oggettoRecordsetDue("NOMEFIRMATARIO")
ActiveCell.Offset(0, 4) = oggettoRecordsetDue("DATANASCITA")
ActiveCell.Offset(0, 3) = oggettoRecordsetDue("CF")
ActiveCell.Offset(0, 5) = oggettoRecordsetDue("NUMSMARTCARD")
ActiveCell.Offset(0, 6) = oggettoRecordsetDue("NUMCONTRATTO")
ActiveCell.Offset(0, 7) = oggettoRecordsetDue("STATOLAVORAZIONE")
ActiveCell.Offset(1, 0).Select

oggettoRecordsetDue.Movenext

Loop

Range("A4").Select

MsgBox ("IMPORT TERMINATO!")

' --- chiusura degli oggetti

OggettoRecordsetUno.Close
oggettoRecordsetDue.Close
Set oggettoRecordsetDue = Nothing
Set OggettoRecordsetUno = Nothing
OggettoConnessione.Close
Set OggettoConnessione = Nothing

End Sub
--------------------



---
Message posted from http://www.ExcelForum.com/

There are 2 things to consider:

1, you are needing to update the underlying database engine (the ms
Jet engine), which can be done via, Excel (ODBC, DBO etc), but your
code shows that you are not accessing the data in this way. (if you
where you would have this question in the first place, as they
database would mmost likly be updated!)

2. you could import the new file back into acess, thus updating the
DB.

If you don't do loads of updating the second way might be a goer, you
can do it manually or with code (i'm not usre how)

Another option may be to develope some tools in acess to allow you to
do your updating in there, without having to export to excel.

Good Luck
Ross

ross,
You are way off. The OP *is* accessing the Jet engine in the way you
allude to. The OP is using OLEDB and ADO which are data access
technologies (think of them as the 'more modern' equivalent of ODBC).
Anyhow, the database will not be automatically updated because Excel
is not bound to the Jet database, as the presumption is when you use
MS Access forms. I notice you don't offer any details on how to
'import the new file back into acess [sic]'. And there's no need to
'develope [sic] some tools in acess [sic]' because it already has them
e.g. the ability to update the Excel sheet within Jet as a linked
table. But it seems clear the OP wants to do this from the Excel side.

Sal21,
There is no reliable 'automatic' way of doing this. The best way I
know is to look at the worksheets row by row and determine where if
the current row has been changed, inserted or deleted(!!) and then
issue the appropriate SQL i.e. UPDATE, INSERT INTO or DELETE. Make the
connection as before and use the Connection object's Execute method:

OggettoConnessione.Execute "UPDATE TABELLA1 SET DATA='OneDayeWhen'
WHERE ID=1"

BTW in your code, instead of looping through the recordset i.e.

Do While Not oggettoRecordsetDue.EOF ...

you can instead use Excel's CopyFromRecordset method e.g.

ActiveCell.CopyFromRecordset OggettoRecordsetUno

--


(ross) wrote in message om...
There are 2 things to consider:

1, you are needing to update the underlying database engine (the ms
Jet engine), which can be done via, Excel (ODBC, DBO etc), but your
code shows that you are not accessing the data in this way. (if you
where you would have this question in the first place, as they
database would mmost likly be updated!)

2. you could import the new file back into acess, thus updating the
DB.

If you don't do loads of updating the second way might be a goer, you
can do it manually or with code (i'm not usre how)

Another option may be to develope some tools in acess to allow you to
do your updating in there, without having to export to excel.

Good Luck
Ross

onedaywhen,

sorry, you are right, it is jet as shown by!!!!!

Dim StringaDiConnessione
StringaDiConnessione = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & NomeDB & ";'"


sorry!!!!!

What i was suggesting was the Sal21 might be able to access the Access
object modal, along the lines of

dim Access As Application

set Access as Access.Aplliction

then using the access modal to import a file from excel and update the
tables - if the same DB was being used it would be updated for both
apps. As i said i'm not sure of excatlly how this might be achived.

With regards to "tools" i was suggestion that it might be easier to
undertake any changes to the data in access, agian maybe by calling
the excel modal and fuctions? - not that you could update a record.

Any way


good luck

Ross


Sal21,
There is no reliable 'automatic' way of doing this. The best way I
know is to look at the worksheets row by row and determine where if
the current row has been changed, inserted or deleted(!!) and then
issue the appropriate SQL i.e. UPDATE, INSERT INTO or DELETE. Make the
connection as before and use the Connection object's Execute method:

OggettoConnessione.Execute "UPDATE TABELLA1 SET DATA='OneDayeWhen'
WHERE ID=1"

BTW in your code, instead of looping through the recordset i.e.

Do While Not oggettoRecordsetDue.EOF ...

you can instead use Excel's CopyFromRecordset method e.g.

ActiveCell.CopyFromRecordset OggettoRecordsetUno

--


(ross) wrote in message om...
There are 2 things to consider:

1, you are needing to update the underlying database engine (the ms
Jet engine), which can be done via, Excel (ODBC, DBO etc), but your
code shows that you are not accessing the data in this way. (if you
where you would have this question in the first place, as they
database would mmost likly be updated!)

2. you could import the new file back into acess, thus updating the
DB.

If you don't do loads of updating the second way might be a goer, you
can do it manually or with code (i'm not usre how)

Another option may be to develope some tools in acess to allow you to
do your updating in there, without having to export to excel.

Good Luck
Ross