Records across multiple tables in access

[fenris]

I have this problem that I have been wrestling with for awhile now. I have some data that needs to be placed in different tables in an access DB but the data has to be related to each other. For example, say I have a customer table with name and address fields and I have a purchase table with items and quantity purchased fields. What I want to be able to do using ADO and sql (without tampering with the database by using access itself, I want to do it all in code) is to be able to present the user with a form that allows them to enter the customer information as well as their order information and then the program takes this and adds it to the database in one fell swoop with a minimum of code.

I can create the database, tables and fields by using ADOX.
From what I gather I will need a way to relate the tables. So I would have to add an index to the customer table, making it look like:

custID custName custAdd orderID

And the purchases table would look like:

orderID items quant

I think that by joining the tables on the orderID I can retrieve the information easily enough. But I am stumped as how to get the information in there? The part that stumps me the most is how to get the orderID field synchronized...


Any help would be appreciated.

Troy Williams B.Eng.
fenris@hotmail.com

 

[sunaj]

Hi Fenris

If I understand your question correctly this is what you what to do:

dbs.Execute ("CREATE TABLE tblship (shipID BYTE CONSTRAINT PK_shipID PRIMARY KEY, name TEXT(30), code TEXT(4))&quot

dbs.Execute ("CREATE TABLE tblstation (stationID LONG CONSTRAINT PK_stationID PRIMARY KEY, shipID BYTE CONSTRAINT FK_shipID REFERENCES tblship (shipID), stn SHORT, lat SINGLE, latprec BYTE, lon SINGLE, lonprec BYTE&quot

dbs.Execute ("CREATE INDEX idxstation ON tblstation (lat, lon)&quot

Here we got 2 tables, each with ID's (shipID and statonID), you could also declare these as COUNTER, if you want the db to keep track of the numbers for you. The tables are linked through the foreign key 'FK_shipID'

Sunaj

 

[fenris]

Thank you for the response, it is appreciated.

Now, say I have a customer with their order information, how would I enter this into the proper tables? I guess what I am getting at is entering the data simlar to retrieving the data?

I would retrieve the data by using a sql join statement and extract the necessary fields to an ado recordset. Would I use a sql join statement to create an ado recordset then merge this with the DB?

Puzzled.......



Troy Williams B.Eng.
fenris@hotmail.com

 

[sunaj]

HI,

I insert values using INSERT INTO, In my case this would be:

dbs.execute("INSERT INTO Tblstation (shipID,stn,lat,latprec,lon,lonprec) VALUES (22,1,55.56,2,-122.3,1)&quot

Now the first value (22) must correspond to a shipID in my TblShip. I usually keep track of these values myself, but it is of course also possible to get the ID value by using a SELECT statment (e.g. SELECT ID FROM TblShip WHERE name="polarstern&quot. Look up help on INSERT INTO.

I hope this helps you.
Sunaj

 

[sunaj]

This code demonstrates it and how to fill a combobox with tablenames...


'Copy this code into a form with a combobox (combo1)
'Set references to Microsoft ADO Ext. 2.5 for dll and security (MSADOX.dll)
'and Microsoft ActiveX Objects 2.5 Library (MSADO25.tlb).

Option Explicit


Dim con As ADODB.Connection
Dim catNew As New ADOX.Catalog
Dim rst As Recordset

Private Sub Form_Load()
Dim StrCon As String, i As Integer

'Create database
If Dir(&quot;c:\test.mdb&quot <> &quot;&quot; Then Kill &quot;c:\test.mdb&quot;
StrCon = catNew.Create(&quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\test.mdb&quot
Set con = catNew.ActiveConnection

'create Tabel project
con.Execute &quot;CREATE TABLE TblProject (ID COUNTER CONSTRAINT PK_projectID PRIMARY KEY,&quot; + _
&quot;name TEXT(50), comment TEXT(50))&quot;

'create Tabel Persons
con.Execute &quot;CREATE TABLE TblPerson (ID COUNTER CONSTRAINT PK_personID PRIMARY KEY,&quot; + _
&quot; projectID LONG CONSTRAINT FK_projectID REFERENCES Tblproject (ID),&quot; + _
&quot; surname TEXT(25), firstname TEXT(30),email TEXT(30))&quot;

'Insert values into tblproject
con.Execute (&quot;INSERT INTO TblProject (name) VALUES ('TESTPROJECT')&quot

'Insert values into tblPerson incl foreign key
Set rst = con.Execute(&quot;SELECT ID FROM TblProject WHERE name='TESTPROJECT'&quot
con.Execute (&quot;INSERT INTO TblPerson (projectID,surname) VALUES (&quot; + CStr(rst.Fields(0)) + &quot;,'testname')&quot

'populate combobox with table names
For i = 0 To catNew.Tables.Count - 1
If Left(catNew.Tables(i).Name, 3) = &quot;Tbl&quot; Then _
Combo1.AddItem catNew.Tables(i).Name
Next i
Combo1 = Combo1.List(0) 'select first column
con.Close
End Sub