DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: How to run sub in .MDB from VB using ADO?

  1. #1
    Bo Alexander Guest

    How to run sub in .MDB from VB using ADO?


    I can connect/open/etc my .MDB using ADODB in my VB6 program. I can execute
    queries (stored procedures) using the ADODB.Command. But is there a way
    to execute subs/functions stored in a module in that .MDB?

    Thanks in advance.
    Bo.

  2. #2
    Hilbert Guest

    Re: How to run sub in .MDB from VB using ADO?


    Hi Alexander

    An example on how to connect to a *.mdb

    Public cnITD As ADODB.Connection
    Public rsClientInfo As ADODB.Recordset

    Set cnITD = New ADODB.Connection
    Set rsClientInfo = New ADODB.Recordset

    cnITD.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & & "C:\example.mdb;Persist
    Security Info=False"
    adOpenStatic, adLockBatchOptimistic

    rsClientInfo.CursorLocation = adUseClient
    rsClientInfo.Open "SELECT * FROM tblClientInfo", cnITD, adOpenStatic, adLockBatchOptimistic


    "Bo Alexander" <bo@atlanta.com> wrote:
    >
    >I can connect/open/etc my .MDB using ADODB in my VB6 program. I can execute
    >queries (stored procedures) using the ADODB.Command. But is there a way
    >to execute subs/functions stored in a module in that .MDB?
    >
    >Thanks in advance.
    >Bo.



  3. #3
    Craig Brown Guest

    Re: How to run sub in .MDB from VB using ADO?


    Alexander,

    The following will run a function in Access from within an Excel macro.
    I strongly suspect that it will work within VB as well...

    Craig Brown

    Set app = New Access.Application
    app.OpenCurrentDatabase 'Put your database in here...
    app.DoCmd.OpenModule "Put your module name here", "Put your Function
    Name in here"
    app.Run "Put your Function Name in here"




    "Bo Alexander" <bo@atlanta.com> wrote:
    >
    >I can connect/open/etc my .MDB using ADODB in my VB6 program. I can execute
    >queries (stored procedures) using the ADODB.Command. But is there a way
    >to execute subs/functions stored in a module in that .MDB?
    >
    >Thanks in advance.
    >Bo.



  4. #4
    Douglas J. Steele Guest

    Re: How to run sub in .MDB from VB using ADO?

    It will work in VB, but it's using DAO, not ADO as requested.

    Short answer to the original question is No, there's no way to run a sub or
    function in an MDB using ADO: ADO is only intended to work with Data.

    --

    Doug Steele, Microsoft Access MVP
    Beer, Wine and Database Programming. What could be better?
    Visit "Doug Steele's Beer and Programming Emporium"
    http://I.Am/DougSteele/


    "Craig Brown" <brown.c3@aetna.com> wrote in message
    news:3a49f284$1@news.devx.com...
    >
    > Alexander,
    >
    > The following will run a function in Access from within an Excel macro.
    > I strongly suspect that it will work within VB as well...
    >
    > Craig Brown
    >
    > Set app = New Access.Application
    > app.OpenCurrentDatabase 'Put your database in here...
    > app.DoCmd.OpenModule "Put your module name here", "Put your Function
    > Name in here"
    > app.Run "Put your Function Name in here"
    >
    >
    >
    >
    > "Bo Alexander" <bo@atlanta.com> wrote:
    > >
    > >I can connect/open/etc my .MDB using ADODB in my VB6 program. I can

    execute
    > >queries (stored procedures) using the ADODB.Command. But is there a way
    > >to execute subs/functions stored in a module in that .MDB?
    > >
    > >Thanks in advance.
    > >Bo.

    >




  5. #5
    Join Date
    Oct 2008
    Posts
    1

    Struggling with VB to ADO

    I'm struggling with the same issue. I have a very handy VB function called "AllDependents" that deals with a one-to-many table and concatenates all the many values into one field when given the One value as a parameter. My unresolved issue may follow the same line that the original writer was trying to address.

    Suppose you have two tables, one called major the other dependent. The "major" and "dependent" can join on the field "MAJOR"

    MAJOR TABLE Dependent Table
    MAJOR Major Dependent
    A A Apple
    B A Artichoke
    C A Asperigus
    B Basil
    B Butter
    B Batter
    C Cantalope
    C Cabbage
    C Carrots

    I can use the Major Table, without having to create a join to Dependent, and list all of the dependents as a single field:

    Dependents Query
    Major AllDependents([Major]) as Dependents
    A Apple, Artichoke, Asperigus
    B Basil, Butter, Batter
    C Cantalope, Cabbage, Carrots

    I tried to import this into Excel using MSQuery as a medium, but I always got the message that "AllDependents" is not a defined function. I can see the Dependents field if I use the DAO engine; and the only kluge up with which I could come was to perform a "Make Table" operation on the Access query and then link MSQuery to the made table. However, this puts a degree of separation between the contents of the Dependent table and the Dependents Query. The bookkeeping requirement of having to update the make table to reflect any changes I have made effectively negates the fluid capacity to simply refresh the Excel link to see changes made.

    I find this particularly galling because my understanding of SQL Server, Teradata, and Oracle is that these have a client-server specialization that has the client simply make the request of the server, and then the server contacts the client when the handoff is ready. From what I've observed, ADO insists on interpreting the query on which the Access query is based instead of letting Access do the work and simply hand off the results.

    Is there any way around this dilemma, and I don't care if the answer is short or long.

    Thank you,
    ~Peter Ferber

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Take the query and transfer it to a client front end and then have it run from there against the backend database.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center