[dbellerue]

I am developing a VB6 application which connects with an Access 2010 database.
I want the application to execute queries stored in the database.
Some of the queries include Access functions in field names.
For example- FieldName: IIf(Nz([AnotherFieldName],0)=0,3,[AnotherFieldName])"
Is there a way in VB6 to execute the queries natively in the Access database?
A code example would be very helpful.

[BobRodes]

Yes, there is. Google "ole automation", and have a look at this. Start there, see if you can put your own code example together, and if you get stuck post back.

One thing I will say is that you should attempt to minimize the sending of commands and data between vb6 and access. You're working with two separate processes, and sending stuff back and forth between two processes is complicated and slow. (Read up on "marshaling" if you're interested in finding out more.)

[dbellerue]

BobRodes, on 06 March 2013 - 10:01 PM, said:

Yes, there is. Google "ole automation", and have a look at this. Start there, see if you can put your own code example together, and if you get stuck post back.

One thing I will say is that you should attempt to minimize the sending of commands and data between vb6 and access. You're working with two separate processes, and sending stuff back and forth between two processes is complicated and slow. (Read up on "marshaling" if you're interested in finding out more.)

Thanks for your reply. The link you gave covers opening an Access 2000 form. Will this method work for executing Access 2010 queries?

[andrewsw]

Yes, you could use

oAccess.DoCmd.OpenQuery "savedQueryName"

Here is another link:

MSDN link (automating Access)

However, if you are just using Access to store and retrieve data then you would be better off just using Access as the data-store and connecting to it using a connection-string and ADO. OLE automation of Access is really only suitable if you want to start the application and present the user with a form or report.

This would mean, however, that you would need to translate the Access function-calls to their T-SQL equivalents.

[The Access saved queries will be optimized (within Access) but the advantage of this will probably be lost with the overhead of automating the Access application - IMO.]

This post has been edited by andrewsw: 07 March 2013 - 06:44 AM

[andrewsw]

From the page I linked to:

Quote

A developer can work with a Microsoft Access database from Visual Basic .NET using two separate technologies: Automation and Microsoft ActiveX Data Objects (ADO.NET).

ADO.NET is the preferred technology if you want to work with data objects, such as tables and queries in an Access database. Use Automation only if you need Microsoft Access application-specific features, such as the ability to print or to preview an Access report, to display an Access form, or to call Access macros.

This post has been edited by andrewsw: 07 March 2013 - 07:20 AM

[dbellerue]

andrewsw, on 07 March 2013 - 06:35 AM, said:

Yes, you could use

oAccess.DoCmd.OpenQuery "savedQueryName"

Here is another link:

MSDN link (automating Access)

However, if you are just using Access to store and retrieve data then you would be better off just using Access as the data-store and connecting to it using a connection-string and ADO. OLE automation of Access is really only suitable if you want to start the application and present the user with a form or report.

This would mean, however, that you would need to translate the Access function-calls to their T-SQL equivalents.

[The Access saved queries will be optimized (within Access) but the advantage of this will probably be lost with the overhead of automating the Access application - IMO.]

[dbellerue]

Thanks Andrewsw. You said something that provoked a thought of another approach. You said "...translate the Access function-calls to their T-SQL equivalents."

Are you saying that Access 2010 functions embedded within SQL statements, but using T-SQL syntax, can be executed directly by VB6 with DataEnvironment commands?

Executing queries as DataEnvironment commands is working very nicely for me in VB6, provided the SQL statements don't include Access 2010 functions (e.g., iif, Nz, DMin, DFirst, Switch, etc.)

[andrewsw]

I am not familiar with DataEnvironment commands or variables but, yes, T-SQL provides many functions that would substitute for Access functions.

I never use NZ() - it has issues! - and would replace it, probably, with ISNULL or IIF. Switch would probably be replaced with CASE. The Database functions (DFirst, DMin, etc.) would, however, need to be replaced with subqueries and/or table joins.

This post has been edited by andrewsw: 07 March 2013 - 10:50 AM

[dbellerue]

andrewsw, on 07 March 2013 - 10:49 AM, said:

I am not familiar with DataEnvironment commands or variables but, yes, T-SQL provides many functions that would substitute for Access functions.

I never use NZ() - it has issues! - and would replace it, probably, with ISNULL or IIF. Switch would probably be replaced with CASE. The Database functions (DFirst, DMin, etc.) would, however, need to be replaced with subqueries and/or table joins.

Thanks again andrewsw. My problem here is that there are 415 queries in the existing Access 2010 application. Some of the queries are simple, while many are complex (i.e., include Access functions). In my VB6 application, the most critical measure of its viability will be its ability to come up with exactly the same results as the existing Access application.

Maybe another approach would be to have my VB6 application invoke the Access 2010 queries and let the Access engine do the work, but silently so the user doesn't actually see Access.

Do you know if this is possible?

[andrewsw]

dbellerue, on 07 March 2013 - 11:16 AM, said:

Maybe another approach would be to have my VB6 application invoke the Access 2010 queries and let the Access engine do the work, but silently so the user doesn't actually see Access.

Do you know if this is possible?

Yes, this is possible using, for example, DoCmd.OpenQuery as mentioned before. You might set oAccess.Visible = False as well.

I would go into Access and create a procedure to loop through all the saved-queries, printing out their name, creation-date, and SQL-statement. Presumably, though, many of the queries are there only to populate forms and reports (which you won't be using in your VB6 application?).

The main question as far as I can tell is: is your intention to migrate the entire application to VB6? If this is not your intention, and you wish to keep the Access database in-tact, then what benefit are you gaining with the use of VB6? There is nothing that VB6 can do that couldn't be achieved directly in Access using VBA, and all the other designer tools available to you in Access.

If you intend to migrate the entire application to VB6 then I would, personally, just re-build from scratch, using the existing Access database as a guide to what tables, relations, forms and reports are required. (Presumably you have reporting tools available with your version of VB6.)

This post has been edited by andrewsw: 07 March 2013 - 11:48 AM

[andrewsw]

..and, presumably, you have a good reason to use (ancient) VB6 rather than VB.NET.

[andrewsw]

Sub PrintAllQueries()
    Dim db As DAO.Database
    Dim qry As DAO.QueryDef
    
    Set db = CurrentDb
    
    For Each qry In db.QueryDefs
        Debug.Print qry.Name
        Debug.Print qry.DateCreated
        Debug.Print qry.SQL
        'Debug.Print qry.Type 'prints a number - use a SELECT CASE to print a word
    Next qry
    Set qry = Nothing
    Set db = Nothing
End Sub

..actually, when looping, you could do some replacing at the same time, to convert them to T-SQL versions with the same name

[BobRodes]

Andrew, Access SQL and T-SQL are two different things. I assume that you are not suggesting that the OP switch to SQL Server, which is why I point this out. Transact-SQL is SQL Server's flavor of SQL.

I'm not so sure that the OP should avoid using Automation, given that he has existing queries in place. Rewriting 400 queries is a hell of a lot of work. Also, I'm not sure whether DAO can work with Access 2010. You might know more about that than I do, but ADO and ADOX can.

One of the things that VB can do that Access can't so well is run Access in the background. In fact, you can run a VB6 program with "unattended execution" and not have any UI at all.