"Gal merom" <ga***@towersemi.com> wrote in message
news:40*********************@news.frii.net...
Thanks Steve
I did try reading all the help files that i can find on DAO. I could
call tables,queries but i don't find the object to call for running a VB
code. Can you please write down just one line example to show me how to
call a function called XXX after creating the DAO object.
Thanks
Gal
This example assumes you have a database "C:\Test.mdb" with a table
tblPersons with fields PsnID = Autonumber, PsnFirstName, PsnLastName and you
have a few sample rows. The code written within your Excel workbook will
change the surname of person number 2 to whatever is in the current (active)
cell.
It is not necessarily the way I would actually do this task, but since you
don't say exactly what you are doing I have given an example which makes use
of DAO recordsets. Note that when you paste this code into Excel, make sure
that you choose Tools>References and select Microsoft DAO 3.6 Object Library
and make sure it compiles (Debug>Compile)
Another point to note is that if you are just trying to keep a database and
a spreadsheet synchronized, then sometimes using a linked table in Access
can be very useful as it simply looks up the live data from Excel. This
means you don't have to write any code. Anyway...
Public Sub UpdateRow()
On Error GoTo Err_Handler
Dim dbe As DAO.DBEngine
Dim wks As DAO.Workspace
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strPath As String
Dim strSQL As String
Dim strLastName As String
strLastName = Trim$(Application.ActiveCell)
strPath = "C:\Test.mdb"
strSQL = "SELECT * FROM tblPersons WHERE PsnID = 2"
Set dbe = New DBEngine
Set dbs = dbe.Workspaces(0).OpenDatabase(strPath)
Set rst = dbs.OpenRecordset(strSQL)
If Not rst.EOF Then
rst.Edit
If Len(strLastName) > 0 Then
rst!PsnLastName = strLastName
Else
rst!PsnLastName = Null
End If
rst.Update
MsgBox "Record Updated", vbInformation
Else
MsgBox "Person not found", vbExclamation
End If
Exit_Handler:
On Error Resume Next
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
If Not dbe Is Nothing Then
Set dbe = Nothing
End If
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Sub