ole link from excel to access

J

JCanyoneer

I have an excel spreadsheet that currently will run a macro (when a button is
clicked) which will open an access file, create a new record in a certain
table, and fill in certain fields. I have added an OLE field to the table
which I would like to be linked to the excell file. I have figured out how to
do this from access, manually for each record, but would like the macro in
excel to do this automatically when it is filling in the other fields. Can
anyone help me with the code for this? Here is the current code I have for
doing what I have described without the OLE part:

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=S:\Time Clock\NJC.mdb"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Jobs", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Date") = Now()
.Fields("Company") = Range("D4")
If Range("E2") > 10000 Then
.Fields("Description") = "Accessories for APS Pickup
veh# " & Range("E2").Value & "."
Else
.Fields("Description") = "Accessories for APS Pickup
veh# 0" & Range("E2").Value & "."
End If
.Fields("HourlyCost") = 54
.Fields("HourlyPrice") = 72
.Fields("Status") = "C"
.Fields("EstimateTot") = Range("F25").Value
' add more fields if necessary...
.Update ' stores the new record
End With
Range("E1").Value = rs.Fields("JobNumber")
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top