The Windows API (Application Program Interface) is a collection of Dynamic-Link Libraries (DLLs) that do most of the common things in Windows. Calls to the Windows API gives you access to routines that do things like drawing menu bars, manipulating bitmaps, playing sound files, and pretty much every other function of Windows.
Basically, you declare a DLL procedure which you can call in your VB program which in turn passes data to and/or retrieves data from the DLL. You should read the section of the VB manual that talks about calling DLLs ("Chapter 24 Calling Procedures in DLLs" in the VB 3.0 Programmer's Guide). This chapter covers the basics of using the Windows API DLLs and calling DLLs in general. Beyond that you may want to find a good book on this subject since it is too large to cover here (see the Book Listing in the Appendix of the General FAQ - Part 1). Don't be too intimidated! Using DLLs (especially many of the Windows API functions) is quite easy, once you learn how to call them. In fact, many of the newer DLLs include VB-compatible modules!
VB 5.0 supports real callback functions, and full (?) access to Windows API functions. See example under AddressOf operator in help/docs.
VB 4.0 and up support callbacks with OLE automation only.VB 4.0 can also create OLE DLLs with VB.
VB 3 does not support callbacks, but various extensions can help. Dan Appleman's
"Visual Basic Programmer's Guide to the Windows API" comes with a floppy disk
which code samples and tools. It also includes a VBX which supports the callbacks which
many API calls require. Dan is also founder and president of Desaware which sells more extensive tools,
including SpyWorks, for VB developers. Dan's update for Windows 95 is called The Visual Basic Programmer's
Guide To the Win32 API.
[Walter Hill <whill@netcom.com>]
This sample code should give a very basic overview on how to access Jet databases using user-defined SQL strings in VB code.
Note that for VB 4 and 5, with Jet 2+, Dynasets are considered evil. Use recordset.
The Microsoft VB FAQ also contains some additional information.
There is a mistake in the 3.0 docs which says you can set the active
index for a recordset. You can't. The data control uses the primary key for tables and
physical order (I think) for dynasets.
[Nic Gibson <nic@skin.demon.co.uk>]
NOTE: You can of course set the indexes yourself using code in VB/Pro (Table objects), but Data Controls can't. Sorry for the problems this possible misunderstanding caused! Thanks to John McGuire <jmcguire@jax.jaxnet.com> for clarifying this.
Applies to VB 3.0: You can thank Microsoft for documenting this topic so poorly. When you compile your VB database application, you must also have an INI file for it which provides the correct pointers to the appropriate database drivers. Therefore, if your application is named INVOICE.EXE, you will need to have a properly configured INVOICE.INI file in your Windows directory. The file, EXTERNAL.TXT, that came with VB should explain all about it.
After version 4.0, I will not hesitate to say: normally, yes!
These are the major weaknesses of VBPro's database functions:
The good news is that lots of companies are willing to sell you products which address one or more of the above weaknesses.
Also, if you build a database application with advanced database relations, it can be a good idea to build the database itself with Access and the front-end with VB.
If you try to retrieve a null value (empty field) from a database, you will get the error: "Invalid use of Null". Here is one way to get around this problem:
I've worked around this problem with the following code:
TextBox.Text = MyTest.Fields("TestFld") & ""
This code converts the Null-Value into an empty string.
[Ralf Metzing <rmm@dragon.stgt.sub.org>]
Contrary to popular belief, Null is not nothing. It's even less than nothing. 8^)
The VB documentation describes all the horrors of misunderstanding the infamous NULL. Since people don't read the documentation, we feel like informing that
If ThisVarIsNull = NULL then DoSomething
will always fail, and the DoSomething can't possibly be executed. You must use IsNull(ThisVarIsNull) which will return True if the var is Null (phew!).
If you want to find out why someone came to think of this strange value, some book on relational database theory should provide a few pointers.
Use a counter or index field - or even better, a Bookmark property - for this.
It is impossible to ask a relational database system to give you e.g. "field number 3 in record number 10" since by definition a relational database does not have row or column numbers. Databases allowing direct access like that is not even remotely relational.
Access (and therefore, VB) is about as close to a real relational database system as you get.
Applies to VB 3.0: You need the compatibility layer availability. The file
COMLYR.EXE is in the MSBASIC library on Compuserve. This file provides all the items
necessary for compatibility between VB 3.0 and Access 2.0.
[Fred Griffin <72321.3230@compuserve.com>]
For unknown reasons you can't install the compatibility layer without Acess 2.0 being
installed, even if you just want to open a database from VB that was created by someone
else.
[Kent Boortz <boortz@sics.se>]
The file comlyr.exe can be downloaded from ftp.microsoft.com. It is located in the directory /softlib/mslfiles. (NOTE: A "DIR" in this directory is rarely a wise course of action. There is an enormous amount of files im /mslfiles.)
Note that some of the above may not apply to VB 4 and 5. Note that recordsets have now replaced dynasets and other methods. Calling stored queries may also be preferrable to direct SQL queries from your VB app.
Well, this is a "feature" courtesy of Microsoft. KB article Q106494 explains this in
detail. Basically, the workaround is to add an empty record to the table before the user
can do anything (or before you try to do any Moves on the Table).
[George Tatge (gat@csn.org)]
One word: stored queries. Failing that, recordsets.
KB article Q109830 gives some hints. Things you should do include:
KB article Q120172 gives additional ideas on speeding up VB database access
Major Weakness: SQL is SLOW! A hand-coded search (with indices) is MUCH faster than an equivalent SQL call, especially with complex search criteria. For example:
SELECT * FROM Table WHERE SSN = '555-33-1234' AND Posted #01-31-95#
is a lot slower than:
Table.Index = "SSN"
Table.Seek "=", "555-33-1234"
If Not Table.NoMatch Then
While Not Table.EOFIf Table("SSN") "555-33-1234" Then
Table.MoveLast 'Forces an EOF
ElseIf Table("Posted") #01-31-95# Then
'Do something
End If
Table.MoveNextWend
End If
Granted, it is a LOT more code, but I ran a VERY similar query that took THIRTY HOURS!
The equivalent hand-written code took ELEVEN MINUTES! That's 163 times faster! I think
basically SQL isn't very good at figuring out which indexes to use (I also think I've read
something to the effect that the newer version, 2.0 or 2.5, IS better at this).
[John McGuire <jmcguire@jax.jaxnet.com>]
See p.466 of the Visual Basic (3.0) Programmer's Guide. It contains a section called "Using Bound Picture Box and Image Controls". Basically you have to bind the VB PictureBox to a field in the Access DB, set the .Picture property in the PictureBox, and then move to the next record or something. VB will then store your picture in Access in a form in which it can be retrieved by VB in the future.
If you store the pictures in Access directly (using Access), VB won't be able to read them (using VB 3.0 and Access 1.1).
You can also store the picture's filename as a text field in the database and use
LoadPicture() to load that file into the VB PictureBox.
[Tim Shea <shea@marcam.com>]
You will get a Reserved Error [-1209] ("There is no message for this error")
when your database is corrupted. Try opening the database using MS Access; if it's
corrupted you should get the option to repair it.
[Joe Abley <joe_abley@originuk.demon.co.uk>]
You should also compact it, after repair. I recommend you add the following to your File menu on your main form:
Case ...
RepairDatabase Curentdatabasename
Case ....
On Error resume next
Kill "temp.MDB"
Name curentdatabasename as "temp.mdb"
on error goto errcompact
compactdatabase "temp.mdb", Currentdatabasename
kill "temp.mbd"
exit sub
errcompact:
msgbox "compaction failed"
name "temp.mdb" as Currentdatabasename
Case ...
[Ayn Shipley <ashipley@hookup.net> , Kym Wilson]
Your Paradox table must have a primary key, or it will be read-only no matter what you
set its properties to.
[Ayn Shipley <ashipley@hookup.net>]
See the Knowledge Base article Q117900 "Reserved Error Numbers Returned by the Jet 2.0 Engine" for a complete list of the new error messages.
Extract:
"Jet_Error/Message_String
-1010 Invalid database ID.
-1016 Can't have more than 10 fields in an index.
-1029 Database engine hasn't been initialized.
-1030 Database engine has already been initialized.
-1034 Query support unavailable."
[Ayn Shipley <ashipley@hookup.net>]
VB has a parser bug which makes it difficult to use database objects declared in a module from within a form.
WORKAROUND: Just perform some method on the table object somewhere before you try to reference fields. Say in a form-based subroutine AAAA_IllBeFirst you have a Tbl.MoveFirst, which is never even executed. Then VB suddenly realises what the object is and all is forgiven.
Credit to Luke Webber and "Joe Foster of Borg". [Ayn Shipley <ashipley@hookup.net>]
What I started with:
A database that I wanted to make secure.
Access 2.0 installed on my hard drive.
No Access permissions or accounts ever played with under Access.
See SetDefaultWorkspace and SetDataAccessOption in VB help for more information on how to set up the data access paramaters in an INI file. Make sure you add error handling for a missing or messed up SYSTEM.MDA file.
I think this is a good basic way to prevent people from looking/changing your database.
Possible flaws are looking at a dump of the .EXE file and finding the name and password
used to open the database (there are various workarounds for this) and also running a
program to look at parameters passed when opening the database (don't know if this is
possible and can't think of a workaround if it is).
[Justin F. Smith <jsw117@psu.edu>]
Setup Wizard for VB 4 and 5 is finally a fully capable product.
Applies to VB 3.0: There were loads of bugs in the setup utilities supplied with VB3. Be sure to get the newest version of SETUPKIT (usually called SETUPK.EXE or -.ZIP). It is available from the sources listed in the beginning of this document, and in the General FAQ.
Alternatively, if you have the older versions, you may have to manually remove the line referring to OLE2UI.DLL in the file SETUPWIZ.INI. See later in this document for dates of newest files on ftp.microsoft.com.
Follow the instructions in SETUPK.TXT exactly. The files actually belong in two separate directories. Not placing them correctly can create strange and unusual side effects -- none of them good(!)
Set all involved EXE, DLL and VBX files to Read-Only so that the setup program doesn't
modify them.
[Charles F. Mulks <21667cfm@msu.edu>]
A very good tip. Actually, make all executables on your system read-only. If not, you can get a sharing violation if you try to run the same DOS executable twice at the same time.
Also, the source code for a SETUP program is included with VB3 Pro. It is quite trivial to tailor it to your specific needs.
The question remains: Is SetupWiz for VB 3 good? No! Good enough? Perhaps.
The documentation tells what parts of the Visual Basic kit you can freely distribute: the VBX files, some DLL's and what the SetupKit includes on your distribution diskettes. Reading software license agreements may be more boring than asking the newsgroup, but is nevertheless a good idea. :-)
There have been some rumours on the newsgroup that you can't redistribute programs written with VB freely. This is nonsense. All applications created with VB can be redistributed freely without royalties (as long as you don't distribute proprietary external files).
The rumours probably originated when Microsoft announced that they will not sell kits allowing third-party software to include the Visual Basic for Applications (VBA) system (Even this is no longer true, btw).
Perhaps the best one is to simply modify the setup app which is supplied with VB. Look in your VB directory for the setupkit\ setup1 directory. There you will find everything you need to do a complete setup program. This sample setup is coded to install a few sample app files and create a program group. You can comment out those lines and change to your files and program mgr. group. There are also a few global variables you will want to change. All of this is contained in the comments in the code.
There are also several third party setup products available.
[George Tatge (gat@csn.org)]
There's an excellent piece of freeware to replace the SETUP.EXE-file provided with VB
(yes, the one that calls your SETUP1.EX_). It is highly configurable, easy to use and has
a number of advantages compared to the original SETUP.EXE supplied by Microsoft. You can
download it from http://www.chapter-one.com/.
[Mathias Schiffer <Mathias.Schiffer@post.rwth-aachen.de>]
Earlier versions of those products and some others do not respond properly to the DLL
commands to create groups and items. More recent versions do. All you can do in this case
is to include some information in your readme.txt file that instructs users of those
products to shut them down and start up program manager before installing.
[George Tatge (gat@csn.org)]
If you are sure that your users have it or can get it, you can easily distribute your
app without vbrunXXX.dll. Simply remove the file from your distribution disk or zip file
and and ALSO remove it from the setup.lst file.
[George Tatge (gat@csn.org)]
There are a couple of DLLs that are almost always in use by windows. Commdlg.dll is the most common example. When faced with this problem, there is no easy way out. The full explanation is several pages long and beyond the scope of this FAQ. The general idea is as follows:
Your setup program will need to create a .BAT file to expand and then copy these files.
Then, it will need to shutdown Windows (see ExitWindowsExec API call) and run the .BAT
file. Then it will need to restart windows and continue your setup program. Your setup
program should delete the temporary .BAT file that is no longer needed.
[George Tatge (gat@csn.org)]
PLEASE- this is one place where everybody's life is much easier if you will follow Microsoft's recommendations. All PUBLIC VBXs and DLLs should be installed in the windows/system directory! A "PUBLIC" DLL or VBX is any which can be purchased on the open market. In other words, if another VB programmer might possibly use the same VBX or DLL, install it in the system directory.
If you have written private VBXs or DLLs that will never be used by any program but yours, you can install them in the same directory where you install your application files.
There are lots of good reasons for doing this, but it makes a short novel to rehearse
them all.
[George Tatge (gat@csn.org)]
Some programmers with background from Pascal can try the following
Dim iA, iB, iC as Integer
and think that all these 3 variables end up as Integer. In fact, the first two end up as default data type, normally Variant.
Instead you should do
Dim iA as Integer
Dim iB as Integer
Dim iC as Integer
which takes up more space, but gives you room to comment your variables (hint, hint); or
Dim iA%, iB%, iC%
which does the whole job.
When you are ready to compile your VB project into your 'finished' EXE, be sure to save the project files, exit VB, restart Windows, run VB, load your project and go straight to compiling. Otherwise, your EXE may be larger in file size than necessary due to 'garbage' getting included in the EXE. For some reason, VB does not fully clean up all of the previously used variables or objects that you may have been playing with while developing your program so these get included in your EXE even though they aren't used. Other VB users have even advocated saving all the project files as ASCII, then loading the ASCII files before compiling to further "clean up" the resulting EXE file.
Suggestion: put the END statement used to exit your program *only* in the Form_Unload event of the main form. Whenever you want to end the program, just tell the main form to unload.
Some have reported that after their program have (supposedly) terminated, it still appears in the task list. This can happen if you only hide secondary forms and forget to unload them when you end/unload the main form.
Also note that the Stop-button on the button-bar of the integrated development environment doesn't really unload anything. It *nukes* the program, which generally is a good idea since it could be a bug in it that caused it to be stuck in an eternal loop or something.
This section is obsolete, but retained for users of VB 3.0 and below.
Date | File to download | Updates files | Description |
3/7/94 | BTR110.EXE | BTRV110.DLL | Btrieve IISAM Driver |
3/7/94 | DATAINDX.EXE | DATAINDX.DOC | "Data Access Guide" Index |
3/7/94 | GENERIC.EXE | \VB\CDK\GENERIC | Sample custom control source |
3/7/94 | VBGRID.EXE | GRID.VBX | Grid control |
3/7/94 | VBHC505.EXE | HC.EXE, HCP.EXE | WinHelp compiler |
3/7/94 | MSAJT.EXE | MSAJT110.DLL | Access Database Engine |
3/8/94 | MSCOMM.EXE | MSCOMM.VBX | Serial Communications\control |
3/7/94 | ORA110.EXE | ORACLE.TXT | Updated ORACLE.TXT file |
6/27/94 | SETUPK.EXE | SETUP.EXE | Setup Toolkit |
3/7/94 | VBRUN300.EXE | VBRUN300.DLL | Visual Basic Runtime Library |
3/7/94 | XBS110.EXE | XBS110.DLL | XBase IISAM Driver |
There is an article in the Microsoft Knowledge Base that points to each of these files and provides more detailed information about the update. To find these articles, query the Microsoft Knowledge Base using the file name and the word "update3.00".
Note the NEW SETUPKIT update!
[Thanks to Marks Harrop <harrop@werple.apana.org.au>]
VBA for Office 97 is a fully capable VBA engine. The major problem seems to be to find the VBA environment from Excel or Word (Answer: press ALT+F11)!
This applies mostly to earlier versions: You are in for some surprises. VBA is more unlike VB 3 than most people thought. Especially the development environment is very different, and the language puts more emphasis on objects. The latter is a trend you can get used to for VB also.
For Excel 5 VBA (not Office 97), be aware that the environment is based on the "workbook" idea Microsoft stole from Borland. Your controls will be placed in one sheet, and the code will be in another. Doubleclicking on the control to open the code window doesn't help. You have to use the "Tools|Assign Macro" menu option.
Also, be aware that the list of events (in older versions) is nowhere close to what VB3 supports! No GotFocus, no MouseMove, no nothing. You'll be very confused if you try to look for "events" in the VBA docs!
No. VBX is a dead end. There will never be 32-bit VBXs, but OCXs using OLE 2. VBA is more a subset of VB 4 than VB 3, but it does not fully support OCX yet. It will, though.
As normal in VB 5. Enter the VBA environment, access the object browser and you're right at home.
For earlier versions: As noted above, VBA is a culture shock to VB programmers. If you create a textbox in VBA, call it txName and try to
cMyVar=txName.Text
the impolite interpreter will give you a "variable not defined" error.
The magic is objects. You have to
Dim txName as Object
Set txName = DialogSheets("NameDialog".EditBoxes("txName"))
And then you can access your properties like you used to in good ol' VB 3. (Anyone volunteer to beat senseless the guy who thought out this?) [Editor's note: With VBA for Office 97, somebody got the clue and changed this. Thanks, Microsoft!)
The documentation is somewhere between sparse and inexistant on this topic. Any info on VBA and SQL would be much appreciated.
Here Microsoft breaks the tradition and you can't use database objects, at least not the way you do in VB. Also, forget dynasets.
I know next to nothing about databases in VBA. I just bring on the following tips from various magazines:
Both SQLOpen and QueryGetData require a 'connection string'. That's about what the doc's say about the parameter. What is it? The doc is also tragically void of useful examples. Someone dug up the following example:
"DSN=My data file;DBQ=c:\access\data.mdb;FIL=RedISAM;"
which is about as understandable as it looks. If you use an empty string, you get a dialog which also can give you the string into a spreadsheet cell.
Also, search for SQLREQUEST in the main help file for Excel 5 (not the VBA help!) for these examples of connection_string's:
dBASE DSN=NWind;PWD=test
SQL Server DSN=MyServer;UID=dbayer;PWE=123;Database=Pubs
ORACLE DNS=My Oracle Data Source;DBQ=MYSER VER;UID=JohnS;PWD=Sesame
There's a KnowledgeBase on Excel 5 on ftp.microsoft.com. Last time I looked, it was void of database stuff. Still, it may be a good idea to download it as the situation may have changed now.
You can actually find information about this in the help file for the product. Not the usual help file, mind you, Microsoft will not allow mere mortalts access to such information, but such an help file is indeed part of the Office 97 package. It is by default not installed by Office Setup if you select Typical setup. Use custom setup, as programmers always do anyway, and add the VBA Help to your Office installation. Thatll be helpful, at least once youve found out where to start looking. Alas, the help file is not very good for telling you where to start.
With Microsoft Office 97 Developers Edition comes some extra books. For those who do not have this edition, Microsoft has kindly made the Office 97/Visual Basic Programmers Guide available on its web-site, presently at http://www.microsoft.com/OfficeDev/Articles/OMG/default.htm. The information in these web pages (or the books) is indispensable, especially for those who struggle with the sparse documentation in the elusive help file. The web pages also give information on how to order this book in printed form.
Even better: The Development Exchange, aka Visual Basic Programmers Journal, has set up a web site dedicated to VBA object models. Go to http://objects.windx.com, or rather http://www.inquiry.com/objects/index.html to see the full object hierarchy of all known VBA products, including 3rd party ones like AutoCAD.
No surprise, the principal source for VBscript and ActiveX is Microsoft themselves. VBScript documentation is currently a bit sparse. You can find some resources at http://www.microsoft.com/vbscript/, including the Using VBScript Tutorial at http://www.microsoft.com/vbscript/us/vbstutor/vbstutor.htm. Note that you also have to know the Scripting Object Model which applies to both VBScript and Java, which you can read at http://www.microsoft.com/intdev/sdk/docs/scriptom/. Upon experimentation, you will see that even MS Internet Explorer 3.0 doesn't fully support this object model.
An increasing number of 'certified' ActiveX controls can be found in the Microsoft Web-Gallery at http://www.microsoft.com/activex/gallery/default.htm. The link from here to "Java resources" will bring you to Gamelan, which also hosts ActiveX controls and information at http://www-b.gamelan.com/pages/Gamelan.activex.html.
With a domain name like http://www.activex.com, you'd expect a bit of resources. You get it.
Since the above domain was already taken, http://www.activextra.com/ was what was left for ActiveXpress.com, who hosts quite a bit of news about VBScript, ActiveX, Java and VB, along with an extensive searchable "controls database."
Yes. What you need is simply a program (any program, even a DOS or NT batch file) that can pass and read command-line parameters, and read and write standard input. The latter is not supported directly in VB code, but you can easily access the API functions from VB.
This is tested and works fine on Microsoft Information Server on NT 3.51 and NT 4.0, as well as the more simple Microsoft Frontpage 1.1 server running under Windows 95. However, VB cgi scripts does not work with Purveyor's Web server.