Update to Access Multiple tables via VBA

[Little Penny]

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

 

[Tim Williams]

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]

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

 

[Little Penny]

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******************

 

[Tim Williams]

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

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******************

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