In VB6 I am using the Data Environment connected to an Access97 database via OLE DB provider for ODBC. I have a SELECT SQL statement that includes seven tables. The tables are related by the field "ClientID" which is generated by an autonumber in my table clients. When I try to add a new record on the VB form, all the information is added to the database but the ClientID number only comes up in the clients table, not the other tables. There are cascading relationships set up linking the ClientID field to all the tables. It seems like VB does not know the tables are related. What am I missing? I set the one to one relationships up in Access and it is carried over to VB. When I try to enter information directly into the sql builder EXPR1-6 (ClientID) becomes <NULL>. If I try to enter the ClientID into EXPR1-6 manualy then I get an error "[ODBC driver Access data] query to complex." Also the cursor location is client side and lock type is optimistic. note: the query builder automatically entered the alias EXPR for ClientID. Here is part of my query. This is a small sample of the sql, I removed many of the fields for simplicity.
SELECT TblClients.ClientID, TblClients.ActClientID, TblClients.Client, TblClients.LastChangeDate, TblCoat.ClientID AS EXPR1, TblCoat.CoatID, TblCoat.StyleNo, TblMeasure.ClientID AS EXPR4, TblMeasure.MeasureID, TblMeasure.MeasWeight, TblPosture.ClientID AS EXPR5, TblPosture.Incline, TblPosture.HalfErect, TblVest.ClientID AS EXPR2, TblVest.VestFrontOpen, TblVest.VestStyleNo, TblTrouser.ClientID AS EXPR6, TblTrouser.TrouStyleNo, TblShoulderDevice.ClientID AS EXPR3, TblShoulderDevice.sdNormal, FROM TblClients, TblCoat, TblPosture, TblShoulderDevice, TblTrouser, TblVest, TblMeasure WHERE TblClients.ClientID = TblCoat.ClientID AND TblClients.ClientID = TblPosture.ClientID AND TblClients.ClientID = TblShoulderDevice.ClientID AND TblClients.ClientID = TblTrouser.ClientID AND TblClients.ClientID = TblVest.ClientID AND TblClients.ClientID = TblMeasure.ClientID
Any help is greatly appreciated.
I apologize for the long question.
------------------
Regards,
Mike
