'Need to add reference to the
'Microsoft Active X Data Objects 2.0 or Higher
Private Sub saveDataToAccess()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim r As Integer
r = 6
'Use for Access (jet)
'Assumes that the access database is in the same folder as thisworkbook
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& ThisWorkbook.Path & "\NameOfYourmdb.mdb;Persist Security Info=False"

'Use for jet
'sSQL = Name Of Your Access table Change to your
'Table Name
sSQL = "TableName"

Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open strConn

rs.Open sSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdTable
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("Field1") = Range("A" & r).Value
.Fields("Field2") = Range("E" & r).Value
.Fields("Field3") = Range("F" & r).Value
.Fields("Field4") = Range("G" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
cnn.Close
End Sub


"Pam W." wrote:

First, I want to say how valuable this website is. I've gotten so much help
finding information from other posts.

I've used all this valuable information to create macros that have dazzled
my boss. Now, I'm afraid they think I can do anything. LOL. Let me know if
I'm in over my head--or how far over my head. (I am not familiar with
Access) They want to have an Access database and be able to query data using
Excel. Then they want to pull certain fields from one of the records chosen
by the user into an Excel worksheet. Once it's in this worksheet, it will
have other data added to various column. Then they want to be able to save
that record as a new record in the Access database.