VB6, Access 2000, SQL

[allyeric]

very simple question - I have in my database an employee table, primary key being employee number. When user creates a new employee file, all is fine - but I need the Employee Number to also be added to 3 other tables ... I guess I just thought Access did it automatically, and not quite sure how to get this done. Please help!

 

[Karl Blessing]

There has always been problems with Access2000 And Development, which is why most people(or my entire office for that matter) went to Access97, although you might be able to fix it by seclecting Microsoft ADO 2.1(active Data object) from the project componet list.(has to be 2.1 at least)<br>
Heres a small example that might help in the generic usage of ADO when opening a DB(in the ODBC32 control in Control pannel, goto SystenDSN add a new Access DB , and link the Database Section to the one you are wanting to use, (leave system database frame blank, and DSN name is what you want to call it when you use it in your programs)<br>
<br>
Dim Con As New ADODB.Connection<br>
Dim strSQL As String<br>
strSQL = &quot;SELECT * FROM Table1&quot;<br>
Dim rs As New Recordset<br>
Con.Open &quot;DSN=GNO;UID=;PWD=;&quot;<br>
Set rs = Con.Execute(strSQL)<br>
While Not rs.EOF<br>
List1.AddItem rs(&quot;Groups&quot.Value<br>
rs.MoveNext<br>
Wend<br>
rs.Close<br>
<br>
the strSQL is the variable I used to stick my SQL command, as you notice the Con.Open, I used a DSN name, which is alot easier. further questions? let me know<br>
also any SQL command that does immediate tasks, like Delete, Update, Insert, does not have to be closed(rs.Close), only select query , and anything else that keeps the recordset open for your viewing.<br>
<br>
-hope this helps <p>Karl<br><a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a><br><a href=

http://kb244.8m.com>

</a><br>Experienced in , or have messed with : VC++, Borland C++ Builder, VJ++6(starting),VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(least i didnt start with COBOL)

 

[allyeric]

thanks karl, that part I got already. What my problem is, when I create a new employee, all the information is added to the employee table - which is good. But, I also need the employee number from that table to be inserted into 3 other tables. Is there a way that the employee number can be added to 4 tables when the employee file is being created? Or, can I copy it after from one table to the other 3?

 

[Karl Blessing]

Hmm not sure, probally not, but try this, in Access2000, see if you can get it to work in query view, if you can goto SQL view, and see what the SQL command was for it <p>Karl<br><a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a><br><a href=

http://kb244.8m.com>

</a><br>Experienced in , or have messed with : VC++, Borland C++ Builder, VJ++6(starting),VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(least i didnt start with COBOL)

 

[allyeric]

ok, heres my code so far - after it adds to Employe file.&nbsp;&nbsp;Now what its doing is everytime I run it, it adds all the employee numbers to Emp_Answers, creating duplicate entries, but its not adding the employee numbers to the other two tables <br><br>&nbsp;&nbsp;&nbsp;&nbsp;Set con = New ADODB.Connection<br>&nbsp;&nbsp;&nbsp;&nbsp;con.Open &quot;DSN=Training;UID=;PWD=;&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;strSQL1 = &quot;INSERT into Answers (No_Employe) SELECT No_Employe from Employe_Table&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;strSQL = &quot;INSERT into Emp_Answers (No_Employe) SELECT No_Employe from Employe_Table&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;strSQL2 = &quot;INSERT into Event_Table (No_Employe) SELECT No_Employe from Employe_Table&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = con.Execute(strSQL)<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs1 = con.Execute(strSQL1)<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs2 = con.Execute(strSQL2)

 

[Karl Blessing]

I thought the Insert query was<br>Insert into TABLE (Answers) value (No_Employe)&quot;<br>something like that, its the way i've always used it<br>i mean if you wanted to shorten you code you could just do<br><br>Set rs = con.Execute(&quot;INSERT into Emp_Answers (No_Employe) SELECT No_Employe from Employe_Table&quot<br>Set rs1 = con.Execute(&quot;INSERT into Answers (No_Employe) SELECT No_Employe from Employe_Table&quot<br>Set rs2 = con.Execute(&quot;INSERT into Event_Table (No_Employe) SELECT No_Employe from Employe_Table&quot <br><br>Considering yer not altering the string in any way<br>oh Btw if No_Employe is a variable that wont work<br>you need to do<br>strSQL1 = &quot;Insert into Employe_Table set Answers = '&quot; & No_Employe &&quot;'&quot;<br>remove ' if its a numeric type<br>probally not directly accurate but you get the point. <p>Karl<br><a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a><br><a href=

http://kb244.8m.com>

</a><br>Experienced in , or have messed with : VC++, Borland C++ Builder, VJ++6(starting),VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(least i didnt start with COBOL)

 

[allyeric]

ok, I understand what you are saying - but my question is, how come its adding the employee number to Emp_Answers and not to the others?&nbsp;&nbsp;&nbsp;I only want it to add the new values, but it adds all the records that are in Employe_Table.&nbsp;&nbsp;I have also tried using :<br><br>&quot;Insert into Answers (No_Employe) values &quot; & EmpNo <br><br>EmpNo being an integer<br><br>but that did not work either, thats why I changed it - to get value from table instead of from the form.<br><br>

 

[Karl Blessing]

wsprintf(sqlexec,&quot;Insert into LDAP (PCID, UID, Online, IP) values (%s,%s,1,'%s')&quot;,pcid, userid,ipAddress<br>(ignore the command, just a cut/paste out of VC++)<br><br>i think thats the proper format<br>Insert Into Table (Field1, Field2, Field3) values (ValForF1, ValForF2, ValForF3)<br><br>Also in the code you showed me earlier, you were inserting it into the Same table everytime<br><br>How are you tables setup btw? <p>Karl<br><a href=mailto:kb244@kb244.8m.com>kb244@kb244.8m.com</a><br><a href=

http://kb244.8m.com>

</a><br>Experienced in , or have messed with : VC++, Borland C++ Builder, VJ++6(starting),VB-Dos, VB1 thru VB6, Delphi 3 pro, Borland C++ 3(DOS), Borland C++ 4.5, HTML, ASP(somewhat), QBasic(least i didnt start with COBOL)

 

[allyeric]

thanks again for your help Karl, I got it figured out now, I think!&nbsp;&nbsp;lol<br><br>