Update access DB record when finished running macro

[amorrison2006]

Hi there,

I need an example macro that can create a new record in a shared
access database once the macro has finished running.

I would need the macro to enter the cell value from A1 for example in
a field and the date, time and if possible the username in another
field so four field in total,

I appreciate any help,

Thanks alot

Andrea

 

[merjet]

http://www.erlandsendata.no/english/index.php?t=envbadac

Try the 2nd or 3rd link under "Document".

Hth,
Merjet

 

[Guest]

this could be a start
Private Sub ADOFromExcelToAccess()
'Make reference to Microsoft ActiveX Data Objects 2.8

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long


Set cn = New ADODB.Connection

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=\\Path To\your database\" _
& "mydatabaseName.mdb;Persist Security Info=False"

' open a recordset
Set rs = New ADODB.Recordset
rs.Open "DatabaseTable", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 1 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldName3") = Range("C" & r).Value
.Fields("FieldName4") = Range("D" & 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 Sub

 

[amorrison2006]

Thanks to both of you,

I'll have a look into it,

Andrea