Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling Function in Access Module from Excel (Access 2000/Excel 2000)

    I have several functions in an Access Modeule which I use for form processing. I want to use those same function through a Macro created in Excel. For some reason, my code in the Excel from Cannot find my Access Module, eventhough I have a connection established to it. Is there anything special I need to do to call a function in an Access Module from an Macro in an Excel Form?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling Function in Access Module from Excel (Access 2000/Excel 2000)

    How did you establish a connection to Access?

    I think the only way to run functions in an Access database is through Automation. Let's say that a database named Test.mdb contains a function TestFunction with one numeric argument. You could call it this way:

    Sub CallAccessCode()
    Dim objAcc As New Access.Application
    objAcc.OpenCurrentDatabase "C:AccessTest.mdb"
    MsgBox objAcc.Run("TestFunction", 37)
    objAcc.Quit
    Set objAcc = Nothing
    End Sub

    Of course, this can be generalized.

    Note: Access forms are very different from Excel UserForms. Code that works in an Access form will not work in an Excel UserForm.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calling Function in Access Module from Excel (Access 2000/Excel 2000)

    You should be aware that Excel and Access do not use the same forms or controls, so unless the code if fairly generic, it may still not run against Excel.
    Charlotte

  4. #4
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Function in Access Module from Excel (Access 2000/Excel 2000)

    OK, I am a little confused here, so I am going to try to explain what I have so far

    First I am using DAO Objects, not ADO Ojects

    I have a connection to the db withBasically what you told me earlier)

    Set wsp = DBEngine.CreateWorkspace(Name:="MyWsp", UserName:="Admin", Password:="", UseType:=dbUseJet)
    Set dbs = wsp.OpenDatabase("C:WindowsPOSFilesdbPOS.mdb")

    Second I have a call a function, passing in the invoiceID, workspace and database. The invoiceID should get returned
    InvoiceID = GetProductsOrdered(InvoiceID, wsp, dbs)

    Third, in the GetProductsOrdered Invoice itself, the call to the function in the access module (among other things)
    Since the .run is not a function of my dbs, I am not sure what the equivilant would be to use. ( it is just shown in the example below though)

    DidInsert = dbs.Run ("InsertNewIntoOrder", InvoiceID, ProductID, QtyOrdered, UnitPrice, ProductStatus)

    I am trying to avoid making another connection to the db since I am already connected, as that is what I get from the example you have given me. I guess, maybe I need the eqivalent of the .run for a DAO object

    As for the code, most of it is just calculates and execution a query, so I think the function should be OK to use with Access and Excel

    Any clarification would be great.

    Thanks

    Shimmer

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling Function in Access Module from Excel (Access 2000/Excel 2000)

    DAO and ADO are methods to get at the tables and queries of a database, neither "knows" anything about forms, reports and modules in an Access database.

    As far as I know, it is impossible to execute a function in an Access module using either DAO or ADO. The Run method belongs to the Access application object, not to the DAO Database object, You need to start the Access application to execute a function in a module - that is called Automation and it is what the code in my previous reply does.

  6. #6
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Function in Access Module from Excel (Access 2000/Excel 2000)

    Disregard that post, I totally misunderstood what you were telling me. Instead, do I need to add a reference to any library to get this to work? I get the error "UserType Not defined" on the line:
    Dim objAcc As New Access.Application

    The current libraries are:



    Thanks
    Attached Images Attached Images

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling Function in Access Module from Excel (Access 2000/Excel 2000)

    Yes, I'm sorry, I should have mentioned that in my first reply. You must select Tools | References... (in Excel's Visual Basic Editor) and check the Microsoft Access 9.0 Object Library, then click OK.

  8. #8
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Function in Access Module from Excel (Access 2000/Excel 2000)

    haha, I could not find the access library before, but I got it now [img]/forums/images/smilies/smile.gif[/img]

    Thanks for the input and clarification

    Shimmer

  9. #9
    Lounger
    Join Date
    Feb 2002
    Location
    Summerville, South Carolina, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Function in Access Module from Excel (Access 2000/Excel 2000)

    Hans, I was looking thru the forum for exactly the code you listed in Post 271838 (Access forum). I copied the conde to my Excel spreadsheet, made sure I had the right references checked, edited it accordingly and ran the macro. It stops at the line where I point to the database. In this case, "C:Marine Time Billing.mdb" and says that its either missing or its opened exclusively by another user. Since the status bar shows MS Access hasn't yet opened and the database clearly exists since I have checked over and over again the spelling, location, typos, etc., I'm at a loss to figure out what is going on. Any pointers?

    Thanks,

    Bill Blazer

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling Function in Access Module from Excel (Access 2000/Excel 2000)

    Bill,

    If you start Access this way (Dim ... As New Access.Application) it will start invisibly, there will be no Access icon in the task bar. The fact that you get the error message you mention means that Access has been opened. You can verify this by looking in the Processes tab of the (Windows) Task Manager. If you see one or more instances of MSAccess.exe there, kill them. After doing this, check that there is no "C:Marine Time Billing.ldb" file; if so, delete it. Then try again.

  11. #11
    Lounger
    Join Date
    Feb 2002
    Location
    Summerville, South Carolina, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Function in Access Module from Excel (Access 2000/Excel 2000)

    Hans, thanks for the quick response. Yes, after I sent the post, it occurred to me to check the Processes tab and I found it there.

    What I want to do is run the Access function from Excel. The database already exists and records are added to it. There are a number of individual spreadsheets with information that needs to be added to the Marine Time Billing.mdb. Actually at the outset of the operation, Acess would already be opened. The intent is to avoid having to first run the macro in Excel to set up the export process, then switch to Accesss and run another macro to actually import the records to the existing database. So essentially, I just want to run the Access macro from Excel to avoid all the switching back and forth. Is there some slight modification in your code that will do that rather than open a new instance of Access?

    Thanks,

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calling Function in Access Module from Excel (Access 2000/Excel 2000)

    Why not run the whole thing from Access then?

    If you still want to do it from Excel, try this variation:

    Sub CallAccessCode()
    Dim objAcc As Access.Application
    Set objAcc = GetObject(, "Access.Application")
    MsgBox objAcc.Run("TestFunction", 37)
    Set objAcc = Nothing
    End Sub

    This assumes that the database has already been opened in Access, and that there is no other instance of Access running.

  13. #13
    Lounger
    Join Date
    Feb 2002
    Location
    Summerville, South Carolina, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Function in Access Module from Excel (Access 2000/Excel 2000)

    Hans, worked like a champ!!!! Thanks you ever so much.

    With respect to the project, my boss asked me to see if I could come up with a way to avoid flipping back and forth between Excel & Access and I agreed, so really, it isn't my project. I had just about given up after having scanned through thousands of messages. I was sure at least one other lounger had wanted to do the same thing. Unfortunately, I started in the Excel forum rather than the Access forum. I'm pretty much a VBA novice and that is a kind description. Most of what I do with VBA is copy existing code and modify it to do what I need done and that is about it.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •