VISUAL BASIC

 PC Hardware  |  PC Software  |  Networking  |  Web Designs  |  News Letter   |  Home

Visual Basic  |  C++  |  Java

DATABASE PROGRAMMING IN VB

How do I use SQL in VB code?

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.

Why can't I use an index with my VB accessed database?

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.

"Can't find installable ISAM" or Why does my compiled VB database app generate an error when it ran just fine in the design environment?

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.

Is the Access Engine and Visual Basic Pro good enough for database work?

After version 4.0, I will not hesitate to say: normally, yes!

These are the major weaknesses of VBPro's database functions:

• No run-time query builder ("how good is your user in SQL programming?") or report builder.
• Limited direct advanced control of the Access 1.1 (or 2) Database Engine (ie. security, optimization, etc).SQL us SLOW. Handcoding is much faster (but harder to code)
• I have heard reports of major weaknesses with major distributed databases using local MDBs being updated against main database in batch jobs. This is supposed to be prone to integrity problems.

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.

How do you avoid the "Invalid use of null" error when reading null values from a database?

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>]

What is "NULL"?

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.

How can I access a record by record number?

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.

How about Access 2.0 compatibility?

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.)

Tips for VB database programming:

1. Use Access and QBE. Once it's "working" (even if the parameters are hardcoded), then open up View.SQL and copy the stuff from the SQL window into your VB code. If you need to insert VB variables, try testing this under Access by using parameters instead. They're then nice & easy to spot when it comes to converting into VB - I always call my parameters "PR_xxxx", so I can just search my VB code for this to find any instances that I've missed.
2. It never works first time. So put an error handler into your VB code that copies the contents of SQLStr onto the clipboard, should the query fail. Now it's quick & easy to switch back to Access, find a scratch query and paste the erroneous SQL into that. It's *much* easier to debug a SQL query in Access, after the variables have been merged in, than it is to do it blind from VB.
3. Use carriage returns to break up your SQL. One before each reserved word is sensible. They're not significant in SQL. I assume you're not stupid enough to put them in the middle of field names - unfortunately Debug.Print is!
4. When merging in the contents of a variable (building a SQL query in a VB string), it should *always* be surrounded by an ampersand and 3 double quotes, or an ampersand and 2 mixed quotes, depending on your local conventions:
   
   SQLStr = SQLStr & "WHERE Username <= """ & Username$ """ "
   
   or
   
   SQLStr = SQLStr & "WHERE Username <= '" & Username$ "' "
5. If you're using dates, then it will *always* be one quote, a hash and an ampersand:
   
   SQLStr = SQLStr & "WHERE Start_Date <= #" & Format$(CutOffDate,"Long Date") & "# "
6. Another tip with dates is to format them with the long date format, not the short date. This is then safe against the transatlantic reversal of month & day position.
7. If you're merging in a field/table name, enclose it in square brackets. That way the SQL will still be valid if the variable contains spaces:
   
   SQLStr = "SELECT * FROM [" & TableName$ "] ;"
   
   When building SQL strings in VB, then you'll often do this on several lines, concatenating SQLStr with the new string. If you leave a space at the end of every string, then you can guarantee you won't have problems with the text from successive lines running into each other.
8. If you're using Access 1, you'll keep running into the 1024 character limit on the length of a SQL string. Keep the table & field names short, especially if many JOINs are concerned. Using underscores in names is shorter than spaces, as you don't need the extra 2 characters for the square brackets around them. If your SQL is slightly too long, then you'll probably see a "Missing semicolon" error, even though the semicolon is obviously there (To you, anyway!).Making a QueryDef is a complicated process that is often slower than executing the query ! Don't mess with the .SQL property, as that is equally slow (Access needs to do a lot of work to turn SQL into its internal query format). Two ways around this: Use ready-built queries, written with Access. If you need to merge in values from variables, then use a query with parameters. Setting parameter values is quick to execute.
9. If you really need to build SQL on the fly -- you need to build an ad hoc query, or to supply table or field names (which can't be done with query parameters), then try using:
   
   database.Execute SQLStr
   
   As this doesn't build a QueryDef, then it's quick.
   [Andy Dingley <dingbat@codesmth.demon.co.uk>]
10. Make sure all Tables, Dynasets, Snapshots, Databases, and other data access objects are properly closed before ending the program. As near as I can tell, the pointers to these objects are not destroyed if your VB program doesn't Close them (including when a program crashes). A Microsoft guy did say he can't find anything that confirms that they close, but (of course) he wouldn't say for certain that they aren't closed. Based on resources after serious crashes (that I couldn't walk the program out of by hand), I don't think they're automatically closed.
    [John McGuire <jmcguire@jax.jaxnet.com>]

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.

How come I get a "No Current Record" error when I use a a Data Control on an empty table?

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)]

How can I speed up my VB database application?

One word: stored queries. Failing that, recordsets.

KB article Q109830 gives some hints. Things you should do include:

• Use Snapshots when possible.
• Use transactions whenever possible.
• Use Dynasets when possible.
• Use SQL action queries when possible.
  [George Tatge (gat@csn.org)]

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.EOF

If Table("SSN") "555-33-1234" Then
Table.MoveLast 'Forces an EOF
ElseIf Table("Posted") #01-31-95# Then
'Do something
End If
Table.MoveNext

Wend
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>]

How do I get a bitmap picture in a field in an Access database?

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>]

What is "Reserved Error -1209"?

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]

"Cannot perform operation. illegal.." with Paradox 3.5 table(s)

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>]

I'm getting error message "Reserved Error [-nnnn] ("There is no message for this error")" from Jet Engine 2.0. Huh?

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>]

Why do I get "object not an array" when I try reference the fields of a global object variable which I have set to a table?

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>]

Steps for securing an Access 2.0 database

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.

1. I ran MS Access Workgroup Administrator and created a new SYSTEM.MDA and assigned a unique workgroup id.
2. Ran Access and created a new database, DB1.MDB.
3. Changed the password for the Admin user using Change Password under the Security menu. Changed the password from nothing to "PASSWORD".
4. Clicked on Users under Security. Added a new user, "JOEUSER" and made it a member of the Admins and Users groups.
5. Closed Access, deleted the new database I had just created, DB1.MDB, and restarted Access. Logged in as the new user, "JOEUSER". Created a new database named DB1.MDB. Changed the password from nothing to "PASSWORD".
6. From the File menu, clicked Add-ins and then Import Database. Imported my database, REAL.MDB, that I wanted to make secure.
7. Went to Permissions under the Security menu and turned off all permissions for all Groups and all Users for the database, all tables, forms, macros, etc. The only permissions I left on was full permissions for all objects for the new user, "JOEUSER". After changing the permissions, I closed Access (note that the database I just used was DB1.MDB).
8. I made a backup of my database REAL.MDB and renamed DB1.MDB to REAL.MDB.
9. Opened my VB code and added the line: SetDefaultWorkspace "JOEUSER", "PASSWORD" right before the line where I open the database.
10. Copied the SYSTEM.MDA from my C:\ACCESS directory to the directory where my VB code is. (Note, I do not have an INI file for my program).
11. Ran my VB program and my database can be opened by my program and nobody else. The SYSTEM.MDA has to be delivered with my program however.

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>]

For more details contact the webmaster