my database is on the attachment. Im decent with vb programming but suck with databases! Basically Ive to accomplish the following. I created code for a calendar that allow a user to enter a value for any date in the calendar. I need to take the values they entered into my vb calendar and save the dates with a value to my database. If I were working with one table it wouldnt be a problem. Unfortunately Im using ADO which only allows for 1 table connection at a time. To compensate for this i used 2 ado connections. 1 for each table. The details table shold store the date of an infraction for the employee(infraction-Date that employee screwed their attendance up by a no-call/no-show, coming in late, etc)
My problem is if an infraction is entered it might be the first time that employee got an infraction and i get an error because im moving both tables at the same time. I know basically im supposed to use an if exists clause for this and then next time the details page is available then re synchronize the tables according to employee id. My prob is I only know what it says in a book. Ive no practical experience. I guess what is like to see is a very simple
vb program hooked up to a database with 2 table a main table and a details table. Then I would be fine As i can pick apart the code to see what it does.
Normally Id just attach my project as a whole but as thier are very complicated calculations in it, Im not yet done with my error checking for these calulations.
Also from what I do know of databases and structure I currently have my table setup correctly I wanted to verify that at this point and hopefully ...see a sample program of this as stated above.
Here's a VERY Q&D example that uses the Northwind DB and lists the products ordered for May 1998.
Dim ado As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim sql As String
Private Sub Form_Load()
ado.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=GRAHAMT"
sql = "SELECT OrderID, CustomerID FROM Orders WHERE OrderDate >= '1998/05/01' Order By OrderID"
rs1.Open sql, ado, adOpenForwardOnly, adLockReadOnly
If Not rs1.EOF Then
While Not rs1.EOF
sql = "SELECT ProductID From [Order Details] Where OrderID=" & CStr(rs1!OrderID)
rs2.Open sql, ado, adOpenForwardOnly, adLockReadOnly
If Not rs2.EOF Then
While Not rs2.EOF
Debug.Print rs1!OrderID, rs2!ProductID
Set rs1 = Nothing
Set rs2 = Nothing
Set ado = Nothing
Last edited by grahamt; 05-25-04 at 15:44.
Reason: Format Code
... and before anybody says it, I know the example is lousy coding, it's just to show that you CAN have multiple tables open through one connection. Obviously a JOIN would be more efficient in the select but I'll leave the syntax of that for one of the SQL experts.