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"
  Else
    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));"
    Else
      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
    rs.Close
  End If
Next relno
newdb.Close
End Function

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

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.

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

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

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

	Error 3706: ADO could not find the specified provider

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
        m_DBConnection.Open

    DSN=oracle_database;UID=rod;PWD=testpassword

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.

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

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

       Provider=Microsoft.Jet.OLEDB.3.51;
       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.

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.

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)
        Next
        Rs.MoveNext                       ' Go on to the next record
    Loop