What's New
Q & A
Tip Jar
Tips & Tricks
These are brief tips and tricks that can make Visual Basic programming easier.
New Control Usage Numerical Stuff
Starting & Stopping I/O Bug Alerts
Software Engineering Printing System Issues
Graphics Coding Techniques Other Programs
Databases Misc Forms


How to find problems in relational data
(By Maurice Calvert)

Just solved this problem last night and thought I'd share it with you. I't happened to me several times, so maybe someone out there has had a similar experience.

You've just imported a stack of tables from another system (mainframe, DB4, whatever) into MSAccess. The data SHOULD be relationally coherent, but when you start setting up the relationships with Access, they all fail because there are references in foreign tables to primary keys that don't exist. For example, there are orders for customers that have been deleted. You have dozens of tables and tens of thousands of records and the boss wants something cool for tomorrow morning.

The solution is simple, create the relationships without requesting referential integrity, run this piece of code, fix the data using the queries created and finally clean up your relationships by setting the Referential integrity checkbox. The code is:

Option Compare Database
Option Explicit
Function Validate()
' Author: Maurice Calvert, maurice@calvert.ch  
' This function checks a database to see if all related fields really have
' relational integrity. You'd use this when you have imported data from another
' system that didn't enforce referential integrity and you need to determine
' the complete list of mis-matches. Instructions:
' 1. You have a database with populated tables. Call it X.mdb. This mdb has data
'    in its tables that do not necessarily respect referential integrity.
'    (E.G. There are orders for customers that don't exist)
' 2. Put this code alone in a module in a new database. Call it Y.mdb.
' 3. In Y.mdb, attach all the tables in x.mdb
' 4. In X.mdb, use Tools, Relationships to create all the relationships, without
'    requesting referential integrity (which would fail, what we are trying to
'    discover is the data that makes it fail)
' 5. Run Validate from the debug window
' 6. A query will be created for each failed relationship.
Dim newdb As Database, rs As Recordset, qry As QueryDef
Dim dbname As String
Dim i As Long, relno As Long
Dim pt As String ' Name of the Primary table in the relationship. Customer
Dim pf As String ' Name of the Field in the Primary table
Dim ft As String ' Name of the Foreign table in the relationship. Order
Dim ff As String ' Name of the Field in the Primary table
Dim sqls As String
' Get 'other' database's path from first attached table 
dbname = CurrentDb.TableDefs(0).Connect
' ;DATABASE=C:\Somewhere\XYZ.mdb
i = InStr(dbname, "DATABASE=")
dbname = Mid(dbname, i + 9) ' C:\Somewhere\XYZ.mdb
Set newdb = DBEngine.Workspaces(0).OpenDatabase(dbname)
' Delete queries created from a previous run
For Each qry In CurrentDb.QueryDefs
  If InStr(qry.Name, "_without_") > 0 Then
    CurrentDb.Execute "DROP TABLE " & qry.Name
  End If
Next qry
' Scan each relation in the database
For relno = 0 To newdb.Relations.Count - 1
  pt = newdb.Relations(relno).Table
  pf = newdb.Relations(relno).Fields(0).Name
  ft = newdb.Relations(relno).ForeignTable
  ff = newdb.Relations(relno).Fields(0).ForeignName
  ' We don't support relations on multiple fields, 
  ' too tricky and anyway it's bad practice
  If newdb.Relations(relno).Fields.Count > 1 Then
    MsgBox "Tables " & pt & " and " & pf & _
      " are joined by multiple fields, can't check integrity"
    pt = newdb.Relations(relno).Table
    pf = newdb.Relations(relno).Fields(0).Name
    ft = newdb.Relations(relno).ForeignTable
    ff = newdb.Relations(relno).Fields(0).ForeignName
    ' If the primary table's field is Long, it's likely that
    ' it's an AutoNumber and the relation has failed because
    ' the foreign table has entries with zero rather than Null.
    ' Fix this be replacing 0 with Null.
    If CurrentDb.TableDefs(pt).Fields(pf).Type = dbLong Then
      sqls = "UPDATE " & ft & " SET " & ff & _
        " = Null WHERE [" & ff & "] = 0"
      CurrentDb.Execute sqls
    End If
    ' Now build a statement like this
    'SELECT Order.*
    'FROM Customer RIGHT JOIN Order ON Customer.ID = Order.Customer
    'WHERE (((Order.Customer) Is Not Null) AND ((Customer.ID) Is Null));
    If pt = ft Then 
      ' Self join, e.g. Customer has Guardian, which refers to Customer
      sqls = "SELECT " & ft & ".* FROM " & pt & " RIGHT JOIN " & ft & _
        " AS " & ft & "1 ON " & pt & "." & pf & " = " & ft & "1." & ff & _
        " WHERE (((" & ft & "1." & ff & ") Is Not NULL) AND ((" & _
        pt & "." & pf & ") Is Null));"
      sqls = "SELECT " & ft & ".* FROM " & pt & " RIGHT JOIN " & ft & _
        " ON " & pt & "." & pf & " = " & ft & "." & ff & _
        " WHERE (((" & ft & "." & ff & ") Is Not NULL) AND ((" & _
        pt & "." & pf & ") Is Null));"
    End If
    Set rs = CurrentDb.OpenRecordset(sqls, DB_OPEN_SNAPSHOT) 
    ' Any mis-matches?
    If Not rs.EOF Then ' Yes, make a query out of that
      CurrentDb.CreateQueryDef ft & "_without_" & pt, sqls
    End If
  End If
Next relno
End Function

Wild Cards in DAO and ADO
(By Prakash.v)

In a SQL Query we use 'LIKE' operator with wildcards to find values in a field that match the specified pattern. However the wildcard characters varies from DAO to ADO.

With DAO:

    Dim db As DAO.Database
    Dim rs As Recordset

    Private Sub Form_Load()
        Set db = OpenDatabase("NWIND.MDB")
        Set rs = db.OpenRecordset( _
            "select * from customers where contactname LIKE 'SM*'")
        ' Matching a single character use '?' eg: 'SMI?H'
    End Sub

With ADO connection:

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql1 As String

    Private Sub Form_Load()
        sql1 = "select * from customers where Contactname LIKE 'SM%'"
        ' Matching a single character use underscore '_' eg: 'SMI_H' 
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=Nwind.mdb;"
        rs.CursorLocation = adUseClient
        rs.Open sql1, cn, adOpenForwardOnly, adLockReadOnly
    End Sub

Building a DataReport at Design Time
(By Rod Stephens)

  1. In the Project menu, select Add DataEnvironment. This creates a DataEnvironment that contains a Connection object. Set the Connection object's Properties.
    1. Right click on the Connection object and select Properties.
    2. On the provider tab, select the data provider you want to use. For example, Jet 4.0.
    3. On the Connection tab, define the database connection. For a Jet provider, enter or select the database.
    4. If you click Test Connection, Visual Basic will make sure it can open the database. This is more useful when you have a data source like a DSN rather than a simple .mdb file.
    5. Click OK.
  2. Right click the Connection object and select Add Command. This creates a new Command object attached to the Connection object. Set the Command object's properties.
    1. Right click on the Command object and select Properties.
    2. Define the Command's method of selecting records. The easiest method is to click on the General tab, click the SQL statement, and enter an SQL SELECT statement in the bog box.
    3. Click OK.
  3. Go back up to the Project menu and select Add DataReport. Initialize its properties.
    1. In the Properties window, set the DataSource property to the DataEnvironment object you created.
    2. In the Properties window, set the DataMember property to the Command object you created.
    3. Use the DataReport toolbox to position RptLabels on the DataReport. Set the Caption property to display a label.
    4. Use the DataReport toolbox to position RptTextBoxes on the DataReport. Set a RptLabel's DataMember property to the Command object. Set its DataField property to a database field selected by the Command object.
This should get you started. Experiment with different format settings, the RptFunction control, different report sections, etc.

Click here for some examples that manipulate DataReports at run time.

Storing Dates

The following date function will be useful if you want to pass a date as string to a procedure or crystal report parameter. And you want to pass same format always ( eg. dd/mm/yyyy ) regardless of regional settings.

    'Input  : Date control 
    'Output : Formatted date string
    Private Function FmtDT(dtPara As Date) As String
        FmtDT = Day(dtPara) & "/" & Month(dtPara) & "/" & Year(dtPara)
    End Function

"Cannot Find Provider" Error

A couple people have written me asking about this error message in ADO programs:

	Error 3706: ADO could not find the specified provider
If you get this error, you probably need a newer version of MDAC (Microsoft Data Access Controls). Click here for more information on this problem and a link to the download site.
Connect to an Oracle Database

The way I do this is I create a data source name (DSN) for the Oracle database. Open the Control Panel and start the ODBC Data Sources applet. On the User or System DSN tab, click the Add button.

Then select the Oracle driver you want to use. I use Microsoft's driver but I know others who have downloaded Oracle's driver from their Web site. After you select the driver, click Finish.

On the next form, enter the data source name. This is the name by which you want to identify the database. Also enter the user ID you want to logon as and optionally a description. I don't worry about which user it is because I'm going to change the user when I connect to the database anyway. Click Ok to finish.

Now to open the database using the DSN, use code like this:

    Private m_DBConnection As ADODB.Connection

        Set m_DBConnection = New ADODB.Connection
        m_DBConnection.ConnectionString = _
            "DSN=" & dsn & _
            ";UID=" & user_name & _
            ";PWD=" & password
where dsn is the DSN name, and user_name and password are the user name and password you want to log in as. When the variables are inserted, this string looks something like:

Using Access 2000 Databases in VB6
(By PD Goss)

I had trouble trying to use Access2000 DB's in my VB6 apps. This is how it is correctly done:

  • In "Project"- "Components" select: "Microsoft ADO Data Control 6.0 (OLEDB)." If you have installed the Visual Studio Service Pack 3 it will look like this: "Microsoft ADO Data Control 6.0 (SP3) (OLEDB)"
  • In references select the: "Microsoft ADO 3.6 Object Library"
  • Add the ADO control to your form and right click on it.
    • Select: "ADODC Properties" and click on "Next."
    • Now click on the "Build" button.
    • The "Data Link Properties" dialog will appear. The "Provider" tab should already be in view. Select: "Microsoft Jet 4.0 OLE DB Provider" and click "Next"
    • The "Connection" Tab will activate. Navigate to the Database (or enter it's path) that you want to use in your project and click "Ok."
    • This will return you to the properties dialog. Click on "RecordSource" and under "CommandType" Select "2-adCmdTable."
    • Under "Table or Stored Procedure Name" select the record you want to use and click "Ok." This will return you to your form.
Now you can place a textbox or label or whatever object you want to use. Let's use a textbox. In Properties Click on "DataSource" and "Adodc1" will appear, click on it. Now click on "DataField" and select which field you want to use. You can run your app now to see if it works. Continue to add object for each field you wish to display etc.
Note: Visual Basic 6 Service Pack 4 contains updates that allow you to use Access 2000 databases in the same way as before: using a Data control.
If the database is password protected, you need to do a little extra work. Use the Project menu's References command to set a reference to the Microsoft DAO 3.6 Object Library.

Create Database and Recordset objects. Open the database using DBEngine.OpenDatabase. Use the database's OpenRecordset method to create the Recordset. Then attach the Recordset to the Data control's Recordset property.

    Dim db As Database
    Dim rs As Recordset

        ' Open the database.
        Set db = DBEngine.OpenDatabase( _
            database_name, False, False, _
            "MS Access;PWD=mypassword")

        ' Create the Recordset.
        Set rs = db.OpenRecordset(table_name)

        ' Attach the Recordset to the Data control.
        Set Data1.Recordset = rs
Click here to download an example program.
ODBC Table Owners
Suppose you are building a database project where each user will log on to the database using a different user ID. Make a new database account to own the tables. Then refer to the tables using the owner's name in your code.

For example, suppose your project's name is MAX. Then create a user named MaxOwner and make all the tables while logged on as that user. Do your development logged in as some other user and refer to tables as in:

    SELECT * FROM MaxOwner.Employees

Building Database Connect Strings

Building ConnectionString by hand is difficult. Here's one easy way to build one correctly.

  1. Open the Project menu and select the Add Data Environment command.
  2. Right click on the Connection1 object created and select Properties.
  3. Select the data provider you want to use. If you do not want to do anything fancy, this will be something like "Microsoft Jet 3.51 OLE DB Provider." Click Next.
  4. Enter the database file name in the Connection box, or click the ellipsis button and browse to find the file.
  5. Click Test Connection to verify that the provider can read the file.
  6. Click Ok.
  7. Look at the Connection1 object's ConnectionSource property in the Properties window. That gives you a connect string you can use. It will look something like this (all on one line):
        Persist Security Info=False;
        Data Source=C:\DataFiles\my_data.mdb
  8. Cut and paste this string into your code and remove the DataEnvironment object from the project.

Using ADO in Access 2000
(By James Rushing)

To use a Microsoft Access 2000 database and ADO you have to add the correct references in your Visual Basic project.

To add the references to your Visual Basic project follow the instruction's below.

  1. Open a project.
  2. From the Project menu, click References.
  3. From the list, select Microsoft ActiveX Data Objects 2.1 Library.
  4. From the list, select Microsoft ADO Ext. 2.1 for DDL and Security.
  5. From the list, select Microsoft Jet and Replication Objects 2.1 Library.
  6. Click OK.
This should let you now use an Access 2000 database with an ADODC control. You will still get the same error you have already been getting "Unrecognized data format" if you try to use the Data Forms Wizard. You can use Data Forms Wizard but it is a very time consuming process which I will not go into here.
Converting DBase files into Access
(By Jim Stefanie)
I have a client project which relies on a set of DBF (DBase) files as the target for a table created (or updated) in Access. The client does not have Access on their system - so VBA commands to transfer the db from Access to DBF wouldn't work.

Since the DBF files will never change, I simply created one Excel Spreadsheet for each DBF, the first row containing the Field Names. Save each worksheet as a DBF (Save As : Dbase IV).

Copy the newly created Dbase files into the same folder as the VB program which generates the Access table.

The rest is programmatic: open and attach the dBase file(s) to their Access counterpart. Copy the data from the Access table to the dBase file using a Loop to run through each record and, since the dBase fields align correctly with the Access fields, use a simple FOR/NEXT to copy over the fields.

    ' Set the location and type of attached data
    Set dbs = OpenDatabase(dbwhere, False, False, "dBASE iv;")

    ' Set the recordset to the dBase file
    Set rsu = dbs.OpenRecordset("winCat", dbOpenTable)

    ' Sets the recordset to the Access table
    Set Rs = db2.OpenRecordset("AddCat", dbOpenTable)

    Do While Not Rs.EOF                   ' Open the recordset
        rsu.AddNew                        ' Add a new record  
        For i = 0 To 1                    ' Loop through each field
            rsu.Fields(i) = Rs.Fields(i)
        Rs.MoveNext                       ' Go on to the next record
You could also use UPDATE (SQL) to do the same thing. Since this was a small db, the loop/for next worked fast and fine... and solved a client's problem.

Send your Tips and Tricks to feedback@vb-helper.com.

Subscribe to the VB Helper newsletter
Copyright © 1997-2001 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
www.vb-helper.com/tips12.htm Updated