1. Why can't I use an index with my VB accessed database?
-
There is a mistake in the 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 Control's can't. Sorry for
the problems this possible misunderstanding caused!
Thanks to John McGuire (jmcguire@jax.jaxnet.com) for clarifying
this.
[Top of Page][Table of Contents][Top of FAQ]
-
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.
[Top of Page][Table of Contents][Top of FAQ]
-
That, of course, depends. Generally the answer is "yes",
but you may need some third-party add-on products.
These are the major weaknesses of VBPro's database functions:
- Limited data controls: No add, delete or search button;
no bound list box or masked edit control and - the worst - no bound grid!
- No run-time query builder ("how good is your user in
SQL?") or report builder.
- No direct advanced control of the Access 1.1 (or 2) Database
Engine (ie. security, optimization, etc).
- SQL is SLOW. Handcoding is much faster (but harder to code)
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.
[Top of Page][Table of Contents][Top of FAQ]
-
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)]
[Top of Page][Table of Contents][Top of FAQ]
-
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, read some relational database theory.
[Top of Page][Table of Contents][Top of FAQ]
-
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 ie. "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 can get.
[Top of Page][Table of Contents][Top of FAQ]
-
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 in /mslfiles.)
[Top of Page][Table of Contents][Top of FAQ]
-
- 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.
- 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.
- 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!
- Searching Strings & Dates
- 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$ "' "
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") & "#"
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.
- 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$ "];"
- Use multiple statements to build SQL
-
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.
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.
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.
[Previous Tips by Andy Dingley
(dingbat@codesmth.demon.co.uk)]
- 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)]
[Top of Page][Table of Contents][Top of FAQ]
-
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)]
[Top of Page][Table of Contents][Top of FAQ]
-
KB article
Q109830
gives some hints. Things you should do [George Tatge (gat@csn.org)]
- Use Snapshots when possible.
- Use transactions whenever possible.
- Use Dynasets when possible.
- Use SQL action queries when possible.
- 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)]
[Top of Page][Table of Contents][Top of FAQ]
-
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)]
[Top of Page][Table of Contents][Top of FAQ]
-
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: [Ayn Shipley
(ashipley@hookup.net) , Kym Wilson]
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
[Top of Page][Table of Contents][Top of FAQ]
-
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)]
[Top of Page][Table of Contents][Top of FAQ]
-
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: [Ayn Shipley (ashipley@hookup.net)]
"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."
[Top of Page][Table of Contents][Top of FAQ]
-
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)]
[Top of Page][Table of Contents][Top of FAQ]
-
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.
- I ran MS Access Workgroup Administrator and created a new
SYSTEM.MDA and assigned a unique workgroup id.
- Ran Access and created a new database, DB1.MDB.
- Changed the password for the Admin user using Change Password
under the Security menu. Changed the password from nothing to
"PASSWORD".
- Clicked on Users under Security. Added a new user, "JOEUSER"
and made it a member of the Admins and Users groups.
- 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=7F "PASSWORD".
- From the File menu, clicked Add-ins and then Import Database.
Imported my database, REAL.MDB, that I wanted to make secure.
- 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).
- I made a backup of my database REAL.MDB and renamed DB1.MDB
to REAL.MDB.
- Opened my VB code and added the line: SetDefaultWorkspace
"JOEUSER", "PASSWORD" right before the line
where I open the database.
- 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).
- 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)]