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

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

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

Hth,
Merjet

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

" wrote:

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

Thanks to both of you,

I'll have a look into it,

Andrea

On 22 Jun, 03:07, Mike wrote:
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



" wrote:
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- Hide quoted text -

- Show quoted text -