Insert query at runtime into Access database 1

[jopaumier]

I am using VB6 and ADO to connect to an Access database. I have created a database and a large table in the database at runtime. Now I need to perform queries against the table to create a report. Due to the requirements of the report format, it will take multiple queries to create the recordset on which the report is based. I know I could create intermediate tables, but I would like to try to add the queries to the database at runtime then run the (last?) query to create the recordset for the report (just like Access in which you can query a query). Part of the reason is that once one report is done, the user will have the option of creating a new report, so rather than clutter the database with tables, I will clutter it with queries.

I found a MS Knowledgebase 'HowTo' on using "ADO and ADOX to modify the base query of an Access QueryDef Object in Visual Basic" (# 255782), but I cannot seem to make (i.e., understand) the leap from modifying an existing query to creating a new query. Is it possible and what is the syntax? Also, how do I run the query in VB to create the needed recordset?

thanks,
Jim

 

[DarenNM]

You need to create an ADODB Command object, set its CommandText to the query you want to create, and then append the command object to the ADOX Catalog object.

There is an article here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/adocreateq.asp

Hope that helps

Daren


Must think of a witty signature

 

[jopaumier]

Daren,

This looks like just what I need. A lot of useful information in that article. However, being a newbie to the world of working with databases, I am not clear on how to execute the query from within my VB program. The example 'Creating a Query...' creates the query, but does not execute it. After running CreateQuery, what next?

Jim

 

[DarenNM]

Hi Jim

ADO functionality is quite flexible. You don't need to do things in quite the regimented way as, say, DAO (i.e. workspace->database->recordset, etc.). For example, you can declare an ADO recordset, give it a query and a connection string and it returns the data you want.

The on-line help is quite good (that's where I started, and I'm still learning!).

If you want to run a stored procedure which doesn't return any records, you could use this:

    Dim adocmdMyCommand     As New ADODB.Command
    
    Dim adorsMyRecords      As New ADODB.Recordset
    
    '* * * * * * * * * * * * * * * * * * * * * * * * *
    
    With adocmdMyCommand
    
        .ActiveConnection = sMyConnectionString
        
        .CommandType = adCmdStoredProc
        
        .CommandText = "StoredProcedureName"
        
        .Parameters.Refresh 'Worth doing if you re-use the COMMAND object
        
        .Parameters(ParamName1) = Param1Value
        
        .Parameters(ParamName2) = Param2Value
        
        .Execute
        
    End With 'adocmdMyCommand

If you want to retrieve records:

     With adocmdMyCommand
    
        .ActiveConnection = sMyConnectionString
        
        .CommandType = adCmdStoredProc
        
        .CommandText = "StoredProcedureName"
        
        .Parameters.Refresh 'Worth doing if you re-use the COMMAND object
        
        .Parameters(ParamName1) = Param1Value
        
        .Parameters(ParamName2) = Param2Value
        
        Set adorsMyRecords = .Execute
        
    End With 'adocmdMyCommand

As I mentioned, you can get records without using a COMMAND object:

    adorsMyRecords.Open "SELECT Field1 FROM MyTable", sMyConnectionString

The on-line help also gives details of connection strings if you need it. I tend to connect an ADO control to the database and then examine the resulting connection string which I then build programmatically to use the data source name I need. This is useful as we have different database for testing, development and production.

Hope that helps.

Daren


Must think of a witty signature

 

[jopaumier]

Daren,

Thanks you for the additional information.

Jim