Automation of data entry

T

Ted Helgeson

This is all fairly new to me so please be gentle...... i would like to know
if there is a way to automate data entry between excel and access. Here is my
situation,
I have an Excel worksheet that figures money amounts over a selected period
of time, gives a projection, and will save the data/file/worksheet as the
client name. What i would like to do with this data is to have it copy/enter
into an access database automaticly say, during the save, so that it is
usable for future use. Can i use VB to accomplish this and if so, How? and
or, what would be the easiest way for this to be accomplished?

Any help would be greatly appreacated
 
M

Mike

Option Explicit
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 folders 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
 
Top