ExcelBanter - Updating Access from Excel Macro

You would most likely use the ADO library to do this. Set a reference to
Microsoft ActiveX Data Objects. Create a Connection object using the
Microsoft.Jet.OLEDB.4.0 provider with the path to your database as the data
source. Then create Commmand Objects with the CommendText property set to
an SQL string for INSERT or UPDATE queries. Create paramaterized commands
assigning the values from the worksheet cells to the Paramaters then execute
your queries.
This would resemble something like this:

// Module Level Declarations or as public main module variables
Private cn As ADODB.Connection
Private cmd As ADODB.Command

// Created before calling update or insert functions
Dim cnStr As String

cnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path &
"\ProductionSchedule.mdb"

Set cn = New ADODB.Connection
cn.Open cnStr

// Called with values from Excel cells
Private Sub UpdateCatData(ByVal catKey As Long, strCat As String, strNotes
As String)

On Error GoTo ErrHandle

Dim strQry As String

strQry = "UPDATE Product_Categories SET Product_Category = @Cat, Notes =
@Notes WHERE Product_Category_Key = @CatKey"

Set cmd = New ADODB.Command

With cmd

.ActiveConnection = cn
.CommandText = strQry
.CommandType = adCmdText
.Parameters.Append .CreateParameter("@Cat", adVarChar, adParamInput,
50, strCat)
.Parameters.Append .CreateParameter("@Notes", adVarChar,
adParamInput, 250, strNotes)
.Parameters.Append .CreateParameter("@CatKey", adInteger,
adParamInput, 4, catKey)
.Execute
End With

Exit Sub

ErrHandle:
// Error handling removed for clarity
End Sub

// called with values from Excel cells
Private Sub InsertCatData(strCat As String, strNotes As String)

On Error GoTo ErrHandle

Dim strQry As String
strQry = "INSERT INTO Product_Categories (Product_Category, Notes)
VALUES(@Cat,@Notes)"

Set cmd = New ADODB.Command

With cmd

.ActiveConnection = cn
.CommandText = strQry
.CommandType = adCmdText
.Parameters.Append .CreateParameter("@Cat", adVarChar, adParamInput,
50, strCat)
.Parameters.Append .CreateParameter("@Notes", adVarChar,
adParamInput, 250, strNotes)
.Execute
End With

Exit Sub

ErrHandle:
// Leaving error handling in do demonstrate ADO Errors collection (this
is not quite how you would do it from Excel!)
Dim errObj As Error
Dim strErr As String
Dim idx As Integer
Dim errs As Errors

On Error Resume Next

idx = 1

If Not cn.Errors.Count 0 Then

strErr = "VB Error # " & Str(Err.Number)
strErr = strErr & vbCrLf & " Generated By " & Err.Source
strErr = strErr & vbCrLf & " Description: " & Err.Description
Else

Set errs = cn.Errors
For Each errObj In errs

With errObj

strErr = strErr & vbCrLf & " Error # " & idx & "; "
strErr = strErr & vbCrLf & " ADO Error # " & .Number
strErr = strErr & vbCrLf & " Description: " & .Description
strErr = strErr & vbCrLf & " Source: " & .Source
idx = idx + 1
End With
Next errObj
End If

Err.Raise 5858 Or vbObjectError, "InsertCatData", strErr

End Sub

-- Graham (My favourite ADO reference is the Rob Mac Donald book Serious ADO
from APress!)

"David Slicer" wrote in message
...
working with Excel macro to create a tracking system, the
types of reports and results required are easiest
performed in excel however the amount of data really
should be in a database ie. Access, I know how to query
the information into a sheet, but have been unsuccessful
at getting an update to the database to work from the macro

Where can I get some information on how to do this, or how
do I accomplish this?

VBA Help has been very little help and confussing