Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    4

    Question Unanswered: Running Access Stored Queries in Excel VBA

    I am busy creating an Excel VBA application using MS Access 2002. I am trying to work out how to call stored queries from access.

    I am able to call a simple query and get the results out in my VBA app. For example :

    My Access stored query below is called "select_all". So now this code below works fine :

    ____________________________________________

    Dim rst As DAO.Recordset
    Dim dbs As DAO.Database

    Set dbs = DBEngine.Workspaces(0).OpenDatabase("c:\g\storedpr oc.mdb", dbDriverPrompt + dbRunAsync, False)

    Set rst = dbs.OpenRecordset("select_all", dbOpenDynaset)

    With rst

    etc... etc ....
    ______________________________________________



    What I cannot seem to do is call an Insert Query named "select_name" where I have variables in the query.
    Here is what the query is on my Access database:

    _____________________________

    INSERT INTO [Names] ( Name )
    VALUES ([@newName]);
    _____________________________


    Please could someone show me how I write the Excel VBA code to call this Stored Query from Access inserting a value in my variable "@newName"


    Cheers

    IAA

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    OK,

    there is basic misunderstanding.... MS Access has TWO separate object models.

    1. Access Object Model - helps you to open forms and reports, .....
    2. Data Object Models (ADO and DAO) - manipulates tables and queires


    I don't want to talk about number 1.

    You can manipulate data and queires using ADO or older DAO. ADO is very very universal solution and I really recommend it. It has two separate DLLs - ADODB (for data manipulation - read data, update data, ....) and ADOX for data definition (create field, create index, ....). It's little bit more confusing: you can use ADODB for data definition (if you use pure SQL and DDL - data definition language CREATE TABLE, ....). But remember if you want to dig for data use ADODB, if you want to create new column, table or index, use ADOX.

    ADO is universal top layer for OLE DB connectors. You can use ADO for almost everything and thanks to OLE DB for ODBC drivers, you can use it also for ODBC.

    ADO has one very small problem. It goes ANSI way, so it supports STORED PROCEDURES and VIEWS and NOT queires. So if you need to create query or read query, use DAO. By query I mean really query stored in MDB file, not SQL statement (something like select * from mytable).

    I can see your case as classic ADO problem. You work with Excel and you need to insert some values to existing MDB table. Forget whole MS Access and use pure ADO with JET OLE DB. You can insert something to MDB table even without installing Access on that PC.


    what you need to do is to open connection to MDB file and execute your SQL statement (I guess).


    Sub RunThisStatement()
    Dim ADOComm As New ADODB.Command
    Dim myConnection as String
    Dim mySQL as string

    myConnection = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data " & _
    "Source=C:\My Documents\MyTable.mdb; "

    mySQL = " INSERT INTO MyTable (Column2, Column3) Values ( 1,'Test');"


    ADOComm.CommandText = mySQL
    ADOComm.ActiveConnection = myConnection
    ADOComm.Execute

    End Sub


    jiri
    Last edited by playernovis; 01-04-03 at 21:36.

  3. #3
    Join Date
    Feb 2003
    Location
    NJ
    Posts
    1

    Question Re: Running Access Stored Queries in Excel VBA

    Any possibility that you could post the 'etc. etc.' part, please??? I need to run an Access query from Excel VBA and return the results to Excel.
    Thanks!

    ****


    Originally posted by IAA
    I am busy creating an Excel VBA application using MS Access 2002. I am trying to work out how to call stored queries from access.

    I am able to call a simple query and get the results out in my VBA app. For example :

    My Access stored query below is called "select_all". So now this code below works fine :

    ____________________________________________

    Dim rst As DAO.Recordset
    Dim dbs As DAO.Database

    Set dbs = DBEngine.Workspaces(0).OpenDatabase("c:\g\storedpr oc.mdb", dbDriverPrompt + dbRunAsync, False)

    Set rst = dbs.OpenRecordset("select_all", dbOpenDynaset)

    With rst

    etc... etc ....
    ______________________________________________



    What I cannot seem to do is call an Insert Query named "select_name" where I have variables in the query.
    Here is what the query is on my Access database:

    _____________________________

    INSERT INTO [Names] ( Name )
    VALUES ([@newName]);
    _____________________________


    Please could someone show me how I write the Excel VBA code to call this Stored Query from Access inserting a value in my variable "@newName"


    Cheers

    IAA

  4. #4
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    there are many different solutions, but the easiest non-programmer way would be to forget whole Access and use pure Excel techniques...

    if you go to Data-Get External Data-New Database Query you can get any datasouce....


    now, you can do the same with QueryTables.Add method ....

    so turn on RECORDER (tools-macro-record new macro) then go to Data-GetExternal Data-New Database Query and import your data, then stop the recording and go to VBA and check what Excel created..... you can use it and import any data using SQL.... anytime.






    jiri
    Last edited by playernovis; 02-10-03 at 23:29.

Posting Permissions

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