getting database records

[jarnold]

I am new to VB and am looking for a way to merge(sql) eight access tables together to search various data records. If anyone knows of a sample program that illustrates this problem, I would be interested. My problems are as follows:

1. Some of the tables have the field name "UNION"(I am using access to link to dbf tables and it would be difficult to change the field names and UNION is a SQL reserved word).

2. Should I link the tables or should I search each table one at a time. Some of the tables have a few dissimilar fields.

[JHausmann]

1. Some of the tables have the field name "UNION"(I am using access to link to dbf tables and it would be difficult to change the field names and UNION is a SQL reserved word).

Slapping a coat of whitewash over a poor design still leaves a poor design. Never use reserved words, even if it lets you.

DO Once

1) create 1 MSAccess (or Jet) DB
2) create all of the tables in the 8 DBF's into this one DB
3) rename the badly named fields

Do whenever you need to access the data
1) delete all entries from the MSAccess (or Jet) database
2) copy in the entries from the .dbf's

2. Should I link the tables or should I search each table one at a time. Some of the tables have a few dissimilar fields.

Depends on what you want to do...



[This message has been edited by JHausmann (edited 08-15-1999).]

[jarnold]

I want to do a search by casenumber and the different tables hold different case types. After pressing the command button I would like to display the various data for that particular recordset. The idea I have is to go from table to table until the record is found. I have tried the following code but it only works for records in the last table.

Private Sub Command1_Click()
data.RecordSource = "SELECT * FROM aa_case ORDER BY casenumber"
data.Refresh
data.Recordset.FindFirst _
"CASENUMBER = '" & UCase(txtsearch.Text) & "'"
If data.Recordset.NoMatch Then
End If
data.RecordSource = "SELECT * FROM ad_case ORDER BY casenumber"
data.Refresh
data.Recordset.FindFirst _
"CASENUMBER = '" & UCase(txtsearch.Text) & "'"
If data.Recordset.NoMatch Then
MsgBox "RECORD NOT FOUND"
End If
End Sub