By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,154 Members | 2,405 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,154 IT Pros & Developers. It's quick & easy.

Calling vb from other application

P: n/a
I would like to run a function of one access file from another
access/excel file, How can i do that?

For example: I want to write a procedure in Excel VB that will call
another function in an access file (suppose i want to call an update vb
procedure in access that will update the data in the access file so i
can read it from the Excel. The user will only have to get to the Excel
file and not the Access file.)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Gal merom <ga***@towersemi.com> wrote in message news:<40*********************@news.frii.net>...
I would like to run a function of one access file from another
access/excel file, How can i do that?

For example: I want to write a procedure in Excel VB that will call
another function in an access file (suppose i want to call an update vb
procedure in access that will update the data in the access file so i
can read it from the Excel. The user will only have to get to the Excel
file and not the Access file.)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


From within Excel's VBA, you could use the DAO Object model to access
an MS Access database and then execute the desired module. Or you
could instantiate an MS Access database object within the Excel VBA
code and then use the object with its methods. Of course, this
assumes that you know the full path and filename of the access
database to open and that you have permissions to do so.

Check the Online help for information regarding DAO.

Cheers,
Steve Cummings
Nov 12 '05 #2

P: n/a
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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

P: n/a
"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


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.