Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update to Access Multiple tables via VBA
I'm somewhat familiar with updating from Excel to Access via VBA. How can I update to multiple tables in Access that have a one to many relationship using VBA. Table are linked via key. tbl_One is one Many with tbl_Two via Foreign Key tbl_One is one Many with tbl_Three via Foreign Key Set db = OpenDatabase("C:\LinkedTest\LinkTestDB.mdb") ' open the database Set rs = db.OpenRecordset("tbl_One", dbOpenTable) With rs ..AddNew ' create a new record ' add values to each field in the record .Fields("TableFieldName2") = Range("A1").Value .Fields("TableFieldName2") = Range("B1").Value .Fields("TableFieldName2") = Range("C1").Value Here I what to updated data to tbl_Two which linked to tbl_One Here I what to updated data to tbl_Three which linked to tbl_One .Update ' stores the new record End With Thanks Little Penny |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update to Access Multiple tables via VBA
Penny,
After you perform the indert into table1, just do the inserts in tables 2 and 3 the same way. Or are you looking to find the value of the id (autonumber?) from the Table1 insert ? If so, you should be able to read it from the Table1 recordset following the ..Update Tim "Little Penny" wrote in message ... I'm somewhat familiar with updating from Excel to Access via VBA. How can I update to multiple tables in Access that have a one to many relationship using VBA. Table are linked via key. tbl_One is one Many with tbl_Two via Foreign Key tbl_One is one Many with tbl_Three via Foreign Key Set db = OpenDatabase("C:\LinkedTest\LinkTestDB.mdb") ' open the database Set rs = db.OpenRecordset("tbl_One", dbOpenTable) With rs .AddNew ' create a new record ' add values to each field in the record .Fields("TableFieldName2") = Range("A1").Value .Fields("TableFieldName2") = Range("B1").Value .Fields("TableFieldName2") = Range("C1").Value Here I what to updated data to tbl_Two which linked to tbl_One Here I what to updated data to tbl_Three which linked to tbl_One .Update ' stores the new record End With Thanks Little Penny |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update to Access Multiple tables via VBA
Tim
I'm not sure what the best approach is for this. Here is my code. I know I'm probably way off. Any help would be greatly appreciated. Little Penny Sub AccessUpdate() Dim db As Database, rs As Recordset, r As Long, ur As Long Set db = OpenDatabase("C:\ExcelLog\OldLog.mdb") ' open the database Set rs = db.OpenRecordset("tbl_OldLogData", dbOpenTable) With rs ..AddNew ' create a new record ' add values to each field in the record 'update to tbl_OldLogData ..Fields("JobName") = Range("D2").Value ..Fields("SVXJobName") = Range("D3").Value ..Fields("JobType") = Range("D4").Value ..Fields("SVTNumber") = Range("H3").Value ..Fields("Region") = Range("H4").Value ..Fields("Shift") = Range("J2").Value ..Fields("Machine") = Range("J3").Value ..Fields("JobDate") = Range("N2").Value ..Fields("MailDate") = Range("N3").Value ' add more fields if necessary... .Update ' stores the new record End With rs.Close Set rs = Nothing db.Close Set db = Nothing 'tbl_OldLogData (one to Many with tbl_JobLogs) Set rs = db.OpenRecordset("tbl_JobLogs", dbOpenTable) With rs ..AddNew ' create a new record ' add values to each field in the record 'update to tbl_JobLogs ..Fields("BatchNumber") = Range("B9").Value ..Fields("Batch1StrSeq") = Range("C9").Value ..Fields("Batch1Endseq") = Range("D9").Value ..Fields("Batch1Totalenv") = Range("F9").Value ..Fields("Batch1MeterCt") = Range("G9").Value ..Fields("Batch1Retypes") = Range("H9").Value ..Fields("Batch1Miss_Pull") = Range("I9").Value ..Fields("Batch1EnvTotal") = Range("J9").Value ..Fields("Batch1OPname") = Range("K9").Value ..Fields("Batch1OPid") = Range("M9").Value ..Fields("Batch1QCverify") = Range("N9").Value ..Fields("Batch1QCDtTime") = Range("O9").Value ' add more fields if necessary... .Update ' stores the new record End With rs.Close Set rs = Nothing db.Close Set db = Nothing 'tbl_OldLogData (one to Many with tbl_JobLogs) Set rs = db.OpenRecordset("tbl_CheckLogs", dbOpenTable) With rs 'update to tbl_CheckLogs ..AddNew ' create a new record ' add values to each field in the record ..Fields("Check1DT_Time1") = Range("B26").Value ..Fields("Check1sn") = Range("E26").Value ..Fields("Check1Ky") = Range("G26").Value ..Fields("Check1lmcode") = Range("I26").Value ..Fields("Check1St") = Range("J26").Value ..Fields("Check1Cost") = Range("K26").Value ..Fields("Check1seal") = Range("L26").Value ..Fields("Check1_JV_") = Range("N26").Value ..Fields("Check1Signoff") = Range("O26").Value ' add more fields if necessary... .Update ' stores the new record End With rs.Close Set rs = Nothing db.Close Set db = Nothing End Sub On Sat, 5 Dec 2009 19:04:48 -0800, "Tim Williams" wrote: Penny, After you perform the indert into table1, just do the inserts in tables 2 and 3 the same way. Or are you looking to find the value of the id (autonumber?) from the Table1 insert ? If so, you should be able to read it from the Table1 recordset following the .Update Tim "Little Penny" wrote in message .. . I'm somewhat familiar with updating from Excel to Access via VBA. How can I update to multiple tables in Access that have a one to many relationship using VBA. Table are linked via key. tbl_One is one Many with tbl_Two via Foreign Key tbl_One is one Many with tbl_Three via Foreign Key Set db = OpenDatabase("C:\LinkedTest\LinkTestDB.mdb") ' open the database Set rs = db.OpenRecordset("tbl_One", dbOpenTable) With rs .AddNew ' create a new record ' add values to each field in the record .Fields("TableFieldName2") = Range("A1").Value .Fields("TableFieldName2") = Range("B1").Value .Fields("TableFieldName2") = Range("C1").Value Here I what to updated data to tbl_Two which linked to tbl_One Here I what to updated data to tbl_Three which linked to tbl_One .Update ' stores the new record End With Thanks Little Penny |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update to Access Multiple tables via VBA
Ok I have started from scratch by rebuilding the tables and code. I
found some information about the (Dmax) function to get the primary key to add records to the related tables. However I use the function: pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData") I get an error: Complie error: Sub or Function not defined But if I assgn pk to the next primary key value pk = 25 The code works find. I'm stuck any help would be appricated My New code is below Thanks - Little Penny ****************Start Code****************** Sub AccessUpdate() Dim db As Database, rs1 As Recordset, r As Long, ur As Long Dim rs2 As Recordset, rs3 As Recordset Dim pk As Long MsgBox "Running Update!!!", vbExclamation + vbInformation, "Running Update!!!" Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb") ' open the database Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable) Set rs2 = db.OpenRecordset("tbl_JobGrandTotals", dbOpenTable) Set rs3 = db.OpenRecordset("tbl_JobBatches", dbOpenTable) ur = Range("K2").Value With rs1 ..AddNew ' create a new record ' add values to each field in the record ..Fields("JobName") = Range("D2").Value ..Fields("IPWJobName") = Range("D3").Value ..Fields("JobType") = Range("D4").Value ..Fields("IPWNumber") = Range("H3").Value ..Fields("Region") = Range("H4").Value ..Fields("Shift") = Range("J2").Value ..Fields("Machine") = Range("J3").Value ..Fields("InsertDate") = Range("N2").Value ..Fields("MailDate") = Range("N3").Value ..Fields("TradeDate") = Range("N4").Value ..Fields("Comments1") = Range("D22").Value ..Fields("Comments2") = Range("B23").Value ..Update ' stores the new record End With pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData") + 1 'pk = 25 ' next key value With rs2 ..AddNew ' create a new record ' add values to each field in the record ..Fields("OpLogJobDataID") = pk '.Fields("OpLogJobDataID") = ur ..Fields("TotalM_Count") = Range("G20").Value ..Fields("TotalRetypes") = Range("H20").Value ..Fields("TotalMissPull") = Range("I20").Value ..Fields("GrandTotalEnv") = Range("J20").Value ..Fields("ShipVendor") = Range("M20").Value ..Fields("ShipNumber") = Range("N20").Value ..Update ' stores the new record End With With rs3 r = 9 Do While r <= 18 ..AddNew ..Fields("OpLogJobDataID") = pk ..Fields("BatchNumber") = Range("B" & r).Value ..Fields("BatchStrSeq") = Range("C" & r).Value ..Fields("BatchEndseq") = Range("D" & r).Value ..Fields("BatchTotalenv") = Range("F" & r).Value ..Fields("BatchMeterCt") = Range("G" & r).Value ..Fields("BatchRetypes") = Range("H" & r).Value ..Fields("BatchMiss_Pull") = Range("I" & r).Value ..Fields("BatchEnvTotal") = Range("J" & r).Value ..Fields("BatchOPname") = Range("K" & r).Value ..Fields("BatchOPid") = Range("M" & r).Value ..Fields("BatchQCverify") = Range("N" & r).Value ..Fields("BatchQCDtTime") = Range("O" & r).Value ..Update ' stores the new record r = r + 1 If r = 19 Then Exit Do Loop End With rs1.Close rs2.Close rs3.Close Set rs1 = Nothing Set rs2 = Nothing Set rs3 = Nothing db.Close Set db = Nothing End Sub ****************End Code****************** On Sat, 05 Dec 2009 21:03:14 -0500, Little Penny wrote: I'm somewhat familiar with updating from Excel to Access via VBA. How can I update to multiple tables in Access that have a one to many relationship using VBA. Table are linked via key. tbl_One is one Many with tbl_Two via Foreign Key tbl_One is one Many with tbl_Three via Foreign Key Set db = OpenDatabase("C:\LinkedTest\LinkTestDB.mdb") ' open the database Set rs = db.OpenRecordset("tbl_One", dbOpenTable) With rs .AddNew ' create a new record ' add values to each field in the record .Fields("TableFieldName2") = Range("A1").Value .Fields("TableFieldName2") = Range("B1").Value .Fields("TableFieldName2") = Range("C1").Value Here I what to updated data to tbl_Two which linked to tbl_One Here I what to updated data to tbl_Three which linked to tbl_One .Update ' stores the new record End With Thanks Little Penny |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update to Access Multiple tables via VBA
LP,
I'm not that familiar with DAO (usually use ADO though I know there are some advantages to using DAO with Access). In ADO (same in DAO?) you can get the value of the id field (if it's an autonumber) by reading it back from the recordset after running an insert. So, if you had a table: id (autonumber) otherfield (eg. string) you can do something like: '******************* dim pk Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable) rs1.AddNew rs1.Fields("otherfield").value="Blah" rs1.Update pk = rs1.Fields("id").value 'should now be populated with the "autonumber" key '******************* Reading the key by running another select on the table is generally not a great idea, since you can't guarantee another user hasn't inserted a record and you're then reading *their* key value. I'm not certain this applies in this case since I'm not that clear on record vs. table locking in Access, but you can see it would be something best avoided.... Tim "Little Penny" wrote in message ... Ok I have started from scratch by rebuilding the tables and code. I found some information about the (Dmax) function to get the primary key to add records to the related tables. However I use the function: pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData") I get an error: Complie error: Sub or Function not defined But if I assgn pk to the next primary key value pk = 25 The code works find. I'm stuck any help would be appricated My New code is below Thanks - Little Penny ****************Start Code****************** Sub AccessUpdate() Dim db As Database, rs1 As Recordset, r As Long, ur As Long Dim rs2 As Recordset, rs3 As Recordset Dim pk As Long MsgBox "Running Update!!!", vbExclamation + vbInformation, "Running Update!!!" Set db = OpenDatabase("C:\AAAOperatorLog\OperatorLog.mdb") ' open the database Set rs1 = db.OpenRecordset("tbl_OperatorLogJobData", dbOpenTable) Set rs2 = db.OpenRecordset("tbl_JobGrandTotals", dbOpenTable) Set rs3 = db.OpenRecordset("tbl_JobBatches", dbOpenTable) ur = Range("K2").Value With rs1 .AddNew ' create a new record ' add values to each field in the record .Fields("JobName") = Range("D2").Value .Fields("IPWJobName") = Range("D3").Value .Fields("JobType") = Range("D4").Value .Fields("IPWNumber") = Range("H3").Value .Fields("Region") = Range("H4").Value .Fields("Shift") = Range("J2").Value .Fields("Machine") = Range("J3").Value .Fields("InsertDate") = Range("N2").Value .Fields("MailDate") = Range("N3").Value .Fields("TradeDate") = Range("N4").Value .Fields("Comments1") = Range("D22").Value .Fields("Comments2") = Range("B23").Value .Update ' stores the new record End With pk = DMax("[OpLogJobDataID]", "tbl_OperatorLogJobData") + 1 'pk = 25 ' next key value With rs2 .AddNew ' create a new record ' add values to each field in the record .Fields("OpLogJobDataID") = pk '.Fields("OpLogJobDataID") = ur .Fields("TotalM_Count") = Range("G20").Value .Fields("TotalRetypes") = Range("H20").Value .Fields("TotalMissPull") = Range("I20").Value .Fields("GrandTotalEnv") = Range("J20").Value .Fields("ShipVendor") = Range("M20").Value .Fields("ShipNumber") = Range("N20").Value .Update ' stores the new record End With With rs3 r = 9 Do While r <= 18 .AddNew .Fields("OpLogJobDataID") = pk .Fields("BatchNumber") = Range("B" & r).Value .Fields("BatchStrSeq") = Range("C" & r).Value .Fields("BatchEndseq") = Range("D" & r).Value .Fields("BatchTotalenv") = Range("F" & r).Value .Fields("BatchMeterCt") = Range("G" & r).Value .Fields("BatchRetypes") = Range("H" & r).Value .Fields("BatchMiss_Pull") = Range("I" & r).Value .Fields("BatchEnvTotal") = Range("J" & r).Value .Fields("BatchOPname") = Range("K" & r).Value .Fields("BatchOPid") = Range("M" & r).Value .Fields("BatchQCverify") = Range("N" & r).Value .Fields("BatchQCDtTime") = Range("O" & r).Value .Update ' stores the new record r = r + 1 If r = 19 Then Exit Do Loop End With rs1.Close rs2.Close rs3.Close Set rs1 = Nothing Set rs2 = Nothing Set rs3 = Nothing db.Close Set db = Nothing End Sub ****************End Code****************** On Sat, 05 Dec 2009 21:03:14 -0500, Little Penny wrote: I'm somewhat familiar with updating from Excel to Access via VBA. How can I update to multiple tables in Access that have a one to many relationship using VBA. Table are linked via key. tbl_One is one Many with tbl_Two via Foreign Key tbl_One is one Many with tbl_Three via Foreign Key Set db = OpenDatabase("C:\LinkedTest\LinkTestDB.mdb") ' open the database Set rs = db.OpenRecordset("tbl_One", dbOpenTable) With rs .AddNew ' create a new record ' add values to each field in the record .Fields("TableFieldName2") = Range("A1").Value .Fields("TableFieldName2") = Range("B1").Value .Fields("TableFieldName2") = Range("C1").Value Here I what to updated data to tbl_Two which linked to tbl_One Here I what to updated data to tbl_Three which linked to tbl_One .Update ' stores the new record End With Thanks Little Penny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a Combobox in a Userform access multiple dynamic tables | Excel Discussion (Misc queries) | |||
Update multiple pivot tables | Excel Programming | |||
Use Excel to update Access tables | Excel Programming | |||
How to update ms access tables directly from ms excel? | New Users to Excel | |||
How to import multiple sheet of excel into Access database to a diffrent tables?? | Excel Discussion (Misc queries) |