ADO vs COM? 1

[ddelk]

Is there a comparison or am I talking apples and oranges? Here's where I sit. I have a VB6 front end that uses ADO to retrieve a recordset from an Access 2000 database. I modify that recordset, the do recordset.update to put it back to the Access table. So far, so good.

Within this Access database I have some VBA code that manipulates the tables (updates, new tables created, etc.) and then VBA code that exports those table to text files. I currently use a 32 bit Shell program to open the Access database. The database has a form that loads automatically. That form has the launch codes for the VBA modules in the database. I work with the text files then relaunch the Access database in the Shell. The form knows I'm coming back the second time, and launches the module to pull those text files back into new tables.

Im thinking there is a better way. I need to manipulate the tables (which I know I can do through ADO). But COM exposes Access functionality and I need it, I think, to do my import/export to text files. COM would give me this.

So is there a best way to do these types of things? ADO or COM? Thanks.

 

[ArkM]

ADO implemented on COM basis, so your question contains some kind of terminological confusion. May be you assume OLE automation (call an application as OLE server then control it via COM interfaces).
ADO access does not depend on Access installation (if a target system has a proper Access DB data provider, of course). Call mdb via shell interface means Access is installed.
You can't use Access forms via ADO interface.

 

[ddelk]

I do mean OLE automation.

 

[ArkM]

It seems you may start your VB applications from Access VBA to process exported text files then import results and continue. May be it's more consistent approach (to have one operational entry point without manual switching between Access/VB applications). No OLE auto troubles in that case too. Of course, it assumed that you have full control over VB and VBA sources.

I don't know is it the best solution. For example, we have Access VBA application which invokes C++-based dll codes to make time-consuming database processing (1-2 seconds in C++ instead of 10-20 minuts in VB/VBA. Users does not know anything about this switching, it works OK.

I think, you may also move all VBA from mdb to VB codes (w/o forms?) and process your Access mdbs via ADO interface as data containers only. In any case try to avoid manual ops, make all processing more monolitic.

Alas, it's too abstract advice(s), I know.

 

[BobRodes]

<But COM exposes Access functionality and I need it, I think, to do my import/export to text files.

You don't need Access to export your data to text files. You can use Microsoft Scripting Runtime objects in VB.

HTH

Bob

 

[bjd4jc]

Or you could just use ADO to do it. A keyword search of the forum will give you examples of doing this both in ADO and using FSO.

If you need help with a specifc part of making this work, please post back here...

 

[BobRodes]

>Or you could just use ADO to do it.

bjd4jc, is there a way to directly save recordsets as text files? I tried the save method, and it doesn't, just xml and a proprietary text format. Are you talking about using the old open and write statements?

Bob

 

[bjd4jc]

BobRodes-

This is what I meant. I wasn't thinking along the lines of having a recordset. The original post just said "manipulate tables" which could be done strictly with SQL and not via a recordset (which I would reccommend).

SELECT * INTO [text;Database=c:\temp\TXT].[books.txt] FROM Table1

 

[dilettante]

You can also use the Recordset object's .GetString() method.

 

[BobRodes]

Oh, now there's a trick. I didn't know how to save into text directly with SQL! That's going to save me time in the future. Thanks!

Bob

 

[bjd4jc]

>> didn't know how to save into text directly with SQL

As far as I know this only works with Jet.

 

[BobRodes]

Ah. I usually use SQL Server, but I'll have to remember it for when I'm using Access. So, I looked around, and it doesn't look like you can directly create a text file using SELECT INTO in SQL Server.

Here's an interesting link I found that benchmarks different ways of printing to a file.

http://www.sqlservercentral.com/col...afileusingthesp_oacreatestoredprocedurean.asp

Bob