VBA to VB

[GeorgeMar]

Does anyone know where I may find some documentation on a
dictionary of VBA methods and their equivalence in VB and
vice versa? I am converting a VBA apps into VB EXE.

many thanks
george

 

[Graeme Wigg]

Hi

VBA is a subset of VB. If you are having problems with a particular
programme, you may wish to check that you have the correct refereces within
the VB project and the interface with the application that you aim to
control.

Have a look at www.msdn.com

Regards

 

[Albert D. Kallal]

The VBA code is VERY similar to VB code. In fact, VB6 and access 2000 and
later share the SAME compiler and code base. They are for all purposes the
same langue.

However, what is a major difference is the forms object model, and the
reports object model. In VB you have neither ms-access forms, or ms-access
reports.

So, while the code is generally the same between VB and the VBA, the objects
you work with are VERY different. So, in VB you don't have sub-forms, you
don't have multi-column listbox, or multi-column combo box.

VB forms also has about 50% less events for a form (no before update, no
after update, no on insert (this list is very long). So, forms in VB are
very easy to use, but very limited when compared to ms-access forms
(ms-access forms are FAR MORE complex, and have a much STEEPER learning
curve then does VB). VB in this regards is quite lame.

So, what happens is that in VB you have to write a ton of code to simulate
what is standard fair in ms-access forms.

So, from a code syntax point of view, VB, and ms-access's VBA are virtually
identical. What is much different is the objects that you work with (ie: VB
forms are very different then VBA forms).

 

[GeorgeMar]

Thank you Graeme and Albert.

I have found out about VB's limitations on forms. One of
the problems of the MDE is how big it gets, in tens of
MB. So, what I have decided to do is to split forms in an
MDE and put the commonly used modules in an Activex EXE or
DLL. I have one MDE that has come down from 2MB to 200K
performing the same function.

There are some dissimilarities from VBA to VB such as the
DoCmd. I am having errors with SetWarnings and RunSQL.

many thanks
george

 

[Tim Ferguson]

There are some dissimilarities from VBA to VB such as the
DoCmd. I am having errors with SetWarnings and RunSQL.

Not surprising: the DoCmd object is part of the Access library and is
therefore external to VBA.

You can use the .Execute methods of the DAO.Database object or
ADODB.Connection (or .Command) objects to do the same thing faster and with
more control. Real Access programmers very rarely use the DoCmd object, and
VB programmers should not miss it at all.

HTH


Tim F

 

[GeorgeMar]

Thank you Tim

As per your advice, I am going through and converting all
the Docmd in my Access Applications. I have a hit a
snag. What would be the replacement for SetWarnings,
OpenQuery and TransferText?

many thanks
george

 

[Larry Linson]

Real Access programmers very rarely
use the DoCmd object, and VB
programmers should not miss it at all.

That's a very interesting observation. I've looked at a lot of developed
Access database applications (single-user, multiuser, and client-server) and
the only ones that I ever recall NOT using DoCmd are those that were
strictly novice or casual end-user databases.

In my experience, which dates to the very early days of Access 1.0, I find
that _real_ Access developers (programmer may be too restrictive a word) are
the very ones who do make use of the full facilities of the language.

Larry Linson
Microsoft Access MVP

 

[GeorgeMar]

I have gone through and replaced Docmd.RunSQl with
db.execute and the result is 1 second saving. A report
that has some extensive processing going on, took 26 secs
before and 25 secs after. Is that all the saving I can
expect?

regards
george

 

[david epsom dot com dot au]

TransferText is an append or update query, using
db.Execute.

You don't get warnings unless you use the Application
object (for example Application.DoCmd.RunSQL).

OpenQuery can be used to run an Action query
(db.Execute) or to display a datasheet. If you
want to display a datasheet you need a form
to display it with/on.

(david)

 

[GeorgeMar]

Thank you David

I have been trying to find some code samples of how to use
db.execute with queries that have been saved. All samples
talk about creating a temp query.

Also with the TransferText, If I am using db.execute, how
do you specify the Import Specification for the text file.

regards
george

 

[Tim Ferguson]

That's a very interesting observation. I've looked at a lot of
developed Access database applications (single-user, multiuser, and
client-server) and the only ones that I ever recall NOT using DoCmd
are those that were strictly novice or casual end-user databases.

Fair cop -- what I was thinking about was the pseudo-database commands like
RunSQL, OpenQuery, ApplyFilter, PrintOut, and so on. IMO these are all
better replaced by interacting directly with the dbengine itself.

The DoCmd object also manages all the UI elements like OpenForm and
OpenReport, Close, and so on which are obviously neccessary to make
anything appear on the screen. You are quite right, it would not be much of
an application without them. Then again, these all have to be replaced as
part of migrating to VB too.

Thanks for the correction.


Tim F

 

[david epsom dot com dot au]

db.Execute "MyQueryName"

db.Execute "INSERT INTO [TBL_fred] SELECT * FROM
[Text;DATABASE=c:\].[mytext.txt];"

Export the table first, which also creates a text file
called schema.ini, which is the import/export specification.

(david)

 

[Larry Linson]

The DoCmd object also manages all
the UI elements like OpenForm and
OpenReport, Close, and so on which
are obviously neccessary to make
anything appear on the screen. You are
quite right, it would not be much of
an application without them. Then again,
these all have to be replaced as part
of migrating to VB too.

Certainly they would have to be replaced, and the lack of a good replacement
has been one of the drawbacks to converting.

On the other hand, for applications suitable for implementation in Access,
there is usually little benefit to expending the effort to convert to
classic VB (and questionably, as yet, to VB.NET). In fact, I did a
presentation on using Access or (classic) VB as a front-end to databases for
my user group -- it can be downloaded from
http://appdevissues.tripod.com/downloads.htm . My conclusion was that there
are a few very specific needs that would make VB a better choice, all
unusual for what I perceive to be "normal business database applications".
They are sufficiently unusual that I haven't run across any of them in my
work since I left the mainframe world in 1991.

I've used classic VB since v 1.0, but not for database applications.

Thanks for the correction.

Not intended as a "correction", just an observation.

Larry Linson
Microsoft Access MVP

 

[Larry Linson]

I have gone through and replaced Docmd.RunSQl with
db.execute and the result is 1 second saving. A report
that has some extensive processing going on, took 26 secs
before and 25 secs after. Is that all the saving I can
expect?

The savings of db.execute over DoCmd.RunSQL will be minimal or nonexistent,
because it only affects the sending/issuing the SQL to the database engine.
The SQL, presumably, would be the same action query, and thus would take the
same time to be run by the database engine once issued/received.

Why would you be running SQL with either method in conjunction with running
a Report? It is rarely a good idea to run SQL to be viewed by the user in
datasheet view, so the only use normally for either DoCmd.RunSQL or
db.Execute would be for Action Queries: Append, Update, Delete, or Make
Table -- none of which seem applicable to Reporting.

Just as an aside, you do recognize that there's no comparable reporting
feature in either classic VB or VB.NET, don't you?

Larry Linson
Microsoft Access MVP

 

[david epsom dot com dot au]

The savings of db.execute over DoCmd.RunSQL will be minimal or
nonexistent,

Past versions of Access led to different reports.
I think it might have been because RunSQL by default ran
in a transaction, and db.Execute by default did not.

(david)

 

[GeorgeMar]

Many many thanks David.

You've been most helpful

George

-----Original Message-----
db.Execute "MyQueryName"

db.Execute "INSERT INTO [TBL_fred] SELECT * FROM
[Text;DATABASE=c:\].[mytext.txt];"

Export the table first, which also creates a text file
called schema.ini, which is the import/export specification.

(david)

Thank you David

I have been trying to find some code samples of how to use
db.execute with queries that have been saved. All samples
talk about creating a temp query.

Also with the TransferText, If I am using db.execute, how
do you specify the Import Specification for the text file.

regards
george
use
the

.