The batch ID passed to the central app ends up being two part. part1 is the driver or the vehicle. part2 is the generated sequence number. Do not concatenate them. Doing that just makes it harder to work with the fields.
Hey Pat... Something just hit me that, to be honest, I should have thought of before but didn't. I have all the master tables located on the SQL server like ,dbo-drivers, dbo-Vehicles, etc.. I also have the off-line version of those tables copied to the local drive. In my entire build of this thing, I save the VehicleID and DrvierID numbers as reference for the trip. When uploaded to the master table, the data should reflect the same driver and vehicle numbers but if the keys on the local DB ever get out of sync, then the master DB won't know how to locate which driver or bus the trip was referring to. For example, if I add a new driver to the master table, how can I make sure all the local tables have the exact same matching ID number for that new driver if the local index ever got out of sync?
To correct this, should I stop storing the ID in favor of the Driver name and the vehicle name? Or should I delete the local driver db, and import the master DB every time the user is uploading data to the master to ensure the keys between the master and the local tables match?
The last option is the best way to do it, imho.
They have to be online to do the upload, so assuming they are they get a fresh up-to-date copy of the key tables.
Thanks Minty... So the next dumb question from this old novice is how? I was digging into options last night. First, I now have a method of verifying I can ping the SQL server prior to doing any uploading. I looked into an update method and an append method but that does not guarantee the DB's will be matching. I was thinking I would create a new table from the SQL called drivers-temp. Once verified, I would delete the original table and rename the temp to the new table. Seems like a lot of work but how does one import a new table, verify it and delete a table silently? Can you point me to a doc I can read up on?
Rather than relying on an autonumber, use a custom ID for the data that gets passed. DO NOT CHANGE how your tables are related. You would still use the autonumbers internally. You only use the customID for external communication. The CustomID would need a unique index so you won't accidentally generate duplicates.
Two examples of a custom sequence number. One is a complex ID with concatenated parts. The other is just a sequence number for detail items and is not used as a PK. It has a renumber feature. See below
I am so close guys... really. And thanks for the help. I am running into a challenge with compile that I can't see why. Getting "Only comments may appear after End, Sub..." error in my module. This was all working before I started to comment my code. Now it's erroring. Not sure if this is the best way to post the code so bear with me.
C#:
Option Compare Database
Public Function FindRecordCount(strSQL As String) As Long
' this function pulls the number of records from the database entered as a string. It will work with any record set, db, query,whatever.
Dim db As DAO.Database
Dim rstRecords As DAO.Recordset
Set db = CurrentDb
'Open record set
Set rstRecords = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
'test for end of file
If rstRecords.EOF Then
FindRecordsCount = 0
Else
rstRecords.MoveLast
FindRecordsCount = rstRecords.RecordCount
End If
'set public variable "UploadCount" eq to function output so it can be read outside the function
UploadCount = FindRecordsCount
'MsgBox (UploadCount)
rstRecords.Close
db.Close
Set rstRecords = Nothing
Set db = Nothing
End Function
Public Function PingOk(Ip As String) As Boolean
PingOk = (0 = CreateObject("Wscript.Shell").Run("%SystemRoot%\system32\ping.exe -n 1 -l 1 -w 5000 " & Ip, 0, True))
End Function
Public Function GetCurrentYear() As String
Dim CurYear As String
If Month(Date) < 7 Then
CurYear = Trim(Str((Year(Date) - 1))) + "-" + Trim(Str(Year(Date)))
Else
CurYear = Trim(Str(Year(Date))) + "-" + Trim(Str((Year(Date) + 1)))
End If
MsgBox (CurYear)
End Function
Public Function GetCountStartDate() As Date
GetCountStartDate = DLookup("CountStartDate", "SchoolYrDates", "SchoolYear = '" & GetCurrentYear() & "'")
End Function
Public Function GetFirstCountDate() As Date
GetFirstCountDate = DLookup("FirstCount", "SchoolYrDates", "SchoolYear = '" & GetCurrentYear() & "'")
End Function
Public Function GetSecondCountDate() As Date
GetSecondCountDate = DLookup("SecondCount", "SchoolYrDates", "SchoolYear = '" & GetCurrentYear() & "'")
End Function
Public Function GetFinalCountDate() As Date
GetFinalCountDate = DLookup("FinalCount", "SchoolYrDates", "SchoolYear = '" & GetCurrentYear() & "'")
End Function
Nothing stands out. What line is getting the compile error?
You should ALWAYS use Option Explicit. Set the property in Access options so that Access always requires variable declarations. But that doesn't fix existing code modules so you need to open every single one of them and add the code. Just use the search to find "Option Compare Database" . Then pase in the Option Explicit statement if it is not already there. It will only take a couple of minutes to clean this up. Then compile and now you have to go back and fix all the errors.
First Pat... thank once again. Adding Option Explicit did help a ton. Turned out it was the PingOK function. When I put a return at the end of the command line, the issue went away. I could not see any hidden chars in there but it stopped erroring out.
The explicit option also helped to clean up my code by forcing me to declare variables that I was not using so I went through and removed what I didn't need.
Now I will implement the BatchID per upload and I will be done.
Here's the adjusted ping code... I harvested it from the web so it must have had something in it I didn't see.
Code:
Public Function PingOk(Ip As String) As Boolean
PingOk = (0 = CreateObject("Wscript.Shell").Run("%SystemRoot%\system32\ping.exe -n 1 -l 1 -w 5000 " & Ip, 0, True))
End Function
Done... Question about db.OpenRecordSet. If you set a variable like db = CurrentDb, then set rs = db.OpenrecordSet. Can you work with a second record set? Like rs = db.OpenrecordSet ("Trip", dbOpenDynaset) then rs2 = db.OpenRecordsSet ("SomeQuery", dbOpenDynaset)???
I am running into a problem with a query recordset being locked for editing and I need some clarity here. I assume this is normal?!?
The table Trips is editable but a query I created, "transferquery" is not and I don't know if this is standard.
Here is part of the code. All I want to do is update the batchID for all records that have a false uploaded flag. The transferquery show the 40 records that need to be uploaded to the master SQL table. Before I do the upload, I want to set the BatchID (which is hostname-day-month-year).
Since the query is locked for editing (I can't edit it in the Access view either) so I will set it the trip table which IS editable.
My QUESTION is, if I step through the query records, does that actually change the pointer in the actual trips table?
Here's the SQL for TransferQuery.
SELECT Trip.[T-Date], Vehicles.VehicleName, Drivers.Intials, [Trans-Type].Code, Trip.[Pre-Insp], Trip.DepartTime, Trip.OD_Depart, Trip.[Post-Insp], Trip.ReturnTime, Trip.OD_Return, Trip.[Basic-Count], Trip.[Sped-Count], Trip.[HS-Count], Trip.[Walk-Count], Trip.MaxCount, Trip.Desc, Trip.UploadedFlag, [Trip]![OD_Return]-[Trip]![OD_Depart] AS Expr1, Trip.BatchID
FROM [Trans-Type] INNER JOIN (Drivers INNER JOIN (Vehicles INNER JOIN Trip ON Vehicles.VehicleUniqID = Trip.VehicleUniqID) ON Drivers.DriverUniqID = Trip.DriverUniqID) ON [Trans-Type].CodeID = Trip.CodeID
WHERE (((Trip.UploadedFlag)=False));
Code:
Private Sub UploadBtn_Click()
Dim db As DAO.Database
Dim rsTripsQ As DAO.Recordset
Dim rsTransferQuery As DAO.Recordset
Dim rsTrip As DAO.Recordset
Dim strSQL As String
Dim SetArchBit As String
Dim LocalDBCount As Long
Dim SQLDBCount As Long
Dim MachID As String
'open the database
Set db = CurrentDb()
'open the trip table
Set rsTrip = db.OpenRecordset("Trip", dbOpenDynaset, dbSeeChanges)
Set rsTransferQuery = db.OpenRecordset("TransferQuery", dbOpenDynaset, dbSeeChanges)
If PingOk("10.8.0.73") Then
'FindRecordCount ("dbo_Trip")
SQLDBCount = FindRecordCount("dbo_Trip")
'FindRecordCount ("TransferQuery")
LocalDBCount = FindRecordCount("TransferQuery")
MsgBox ("Local =" & LocalDBCount & " SQL = " & SQLDBCount)
MachID = MakeBatchID()
MsgBox (MachID)
If Not (rsTransferQuery.EOF And rsTransferQuery.BOF) Then
rsTransferQuery.MoveFirst
Do Until rsTransferQuery.EOF = True
rsTrip.Edit
rsTrip!BatchID = MachID
rsTrip.Update
rsTransferQuery.MoveNext
Loop
End If
I am running into a problem with a query recordset being locked for editing and I need some clarity here. I assume this is normal?!?
The table Trips is editable but a query I created, "transferquery" is not and I don't know if this is standard.
Here is part of the code. All I want to do is update the batchID for all records that have a false uploaded flag. The transferquery show the 40 records that need to be uploaded to the master SQL table. Before I do the upload, I want to set the BatchID (which is hostname-day-month-year).
Since the query is locked for editing (I can't edit it in the Access view either) so I will set it the trip table which IS editable.
My QUESTION is, if I step through the query records, does that actually change the pointer in the actual trips table?
Here's the SQL for TransferQuery.
SELECT Trip.[T-Date], Vehicles.VehicleName, Drivers.Intials, [Trans-Type].Code, Trip.[Pre-Insp], Trip.DepartTime, Trip.OD_Depart, Trip.[Post-Insp], Trip.ReturnTime, Trip.OD_Return, Trip.[Basic-Count], Trip.[Sped-Count], Trip.[HS-Count], Trip.[Walk-Count], Trip.MaxCount, Trip.Desc, Trip.UploadedFlag, [Trip]![OD_Return]-[Trip]![OD_Depart] AS Expr1, Trip.BatchID
FROM [Trans-Type] INNER JOIN (Drivers INNER JOIN (Vehicles INNER JOIN Trip ON Vehicles.VehicleUniqID = Trip.VehicleUniqID) ON Drivers.DriverUniqID = Trip.DriverUniqID) ON [Trans-Type].CodeID = Trip.CodeID
WHERE (((Trip.UploadedFlag)=False));
Code:
Private Sub UploadBtn_Click()
Dim db As DAO.Database
Dim rsTripsQ As DAO.Recordset
Dim rsTransferQuery As DAO.Recordset
Dim rsTrip As DAO.Recordset
Dim strSQL As String
Dim SetArchBit As String
Dim LocalDBCount As Long
Dim SQLDBCount As Long
Dim MachID As String
'open the database
Set db = CurrentDb()
'open the trip table
Set rsTrip = db.OpenRecordset("Trip", dbOpenDynaset, dbSeeChanges)
Set rsTransferQuery = db.OpenRecordset("TransferQuery", dbOpenDynaset, dbSeeChanges)
If PingOk("10.8.0.73") Then
'FindRecordCount ("dbo_Trip")
SQLDBCount = FindRecordCount("dbo_Trip")
'FindRecordCount ("TransferQuery")
LocalDBCount = FindRecordCount("TransferQuery")
MsgBox ("Local =" & LocalDBCount & " SQL = " & SQLDBCount)
MachID = MakeBatchID()
MsgBox (MachID)
If Not (rsTransferQuery.EOF And rsTransferQuery.BOF) Then
rsTransferQuery.MoveFirst
Do Until rsTransferQuery.EOF = True
rsTrip.Edit
rsTrip!BatchID = MachID
rsTrip.Update
rsTransferQuery.MoveNext
Loop
End If
Well, I proved it to myself that looping through a query does not change the record pointer in the table that the query is based on. Why is the query locked for editing? Is it in the SET command???
Well, I proved it to myself that looping through a query does not change the record pointer in the table that the query is based on. Why is the query locked for editing? Is it in the SET command???
I think I figured it out. I don't think you can update a query that has calculated fields or a join from another table. I created a query with just the Upload flag and I can edit the table with that data. It seems count-intuitive to me that I can't edit a record based on a combined table query.
Is there something I am missing or is this normal?
it is quite common that multi table queries are not editable - google something like 'access vba why cant i edit multi tables queries' and you will find plenty of examples. The general rule for forms is one table, one form, associated tables would be in subforms. In a form you can sometimes set the recordset type to 'dynaset (inconsistent updates)' to overcome the issue. You can also try this in a query.
Never tried it by opening a recordset in VBA but you could try using dbInconsistent instead of or as well as dbSeeChanges. Spend a bit of time to Google and understand the openrecordset parameters.
Note that you should be able to edit tables that have calculated fields, but not those fields. On the face of it, calculated fields seem like a good idea. In practice you often hit limitations - the calculations allowed are pretty basic, they can't be indexed and they can't be used in joins, the latter two having a strong detrimental impact on performance for larger datasets.
1. NEVER use a VBA loop to update each record when you can just run an update query to update ALL records. There are very rare occasions where you will need to use a VBA loop to update a set of records but this is not one of them. The action query is ALWAYS the superior method when there is an option.
2. You are making this harder on yourself than it needs to be. Generate the BatchID FIRST and save that record. Then run an update query that selects all records where Batchid Is Null and update BatchID to the newly generated value. The flag is totally irrelevant once you decided to use the superior BatchID method. The BatchID method is superior because it gives you auditability. You can link all the rows that were added at the same time into the same group. You can log the name of the file and the date when you generate the BatchID so you can link individual records to a batch. If you have a problem with the import, you can easily back out all the records of the problem batch.
You didn't post the actual query that you said was not updateable. There has been a lot written here on what makes a query not updateable and it is not a join specifically. I have queries with over a dozen joins and they are updateable and I can create a query with a single join that is not updateable. It is not about the join! Although certain joins will cause Cartesian Products and those would be not updateable. But generally, most people encounter this problem when their query contains aggregation using Group By, Sum, Avg, First, etc, It will NOT be updateable. Furthermore, if you join one of these "totals" queries to a table or other query that used to be updateable, the resulting query will NOT be updatable because EVERY part (i.e. table or query) of a query, must each be updateable by itself or the final query will not be updateable.
Hey Pat... once again, thanks for being a font of knowledge. I know how to iterate through a record set but how can I update all the records in a query with the batchID I crafted. I only know how to walk the record set. Here's what I have. I know this seems arcane but I test to see if I can ping the server before I do any uploading.
Then I walk the "unUploaded" set of records, editing the BatchID field. Then I append the trips to the master table and check for a change in record count on both. I assume there is a better way to address the dbFailOnError like a go-to?!?
Code:
Dim db As DAO.Database
Dim rsTripsQ As DAO.Recordset
Dim rsNotUploaded As DAO.Recordset
Dim rsTrip As DAO.Recordset
Dim strSQL As String
Dim SetArchBit As String
Dim LocalDBCount As Long
Dim SQLDBCount As Long
Dim MachID As String
'open the database
Set db = CurrentDb()
'open the trip table
Set rsTrip = db.OpenRecordset("Trip", dbOpenDynaset, dbSeeChanges)
Set rsNotUploaded = db.OpenRecordset("NotUploaded", dbOpenDynaset, dbSeeChanges)
If PingOk("10.8.0.73") Then
'FindRecordCount ("dbo_Trip")
SQLDBCount = FindRecordCount("dbo_Trip")
'FindRecordCount ("TransferQuery")
LocalDBCount = FindRecordCount("NotUploaded")
'MsgBox ("Local =" & LocalDBCount & " SQL = " & SQLDBCount)
MachID = MakeBatchID()
'MsgBox (MachID)
If Not (rsNotUploaded.EOF And rsNotUploaded.BOF) Then
rsNotUploaded.MoveFirst
Do Until rsNotUploaded.EOF = True
rsNotUploaded.Edit
rsNotUploaded!BatchID = MachID
rsNotUploaded.Update
rsNotUploaded.MoveNext
Loop
End If
CurrentDb.Execute ("AppendTripsQuery"), dbFailOnError
FindRecordCount ("dbo_Trip")
MsgBox ("Master DB Count Prior to upload = " & SQLDBCount & " and after upload = " & UploadCount)
If UploadCount > SQLDBCount Then
MsgBox ("You Successfully Uploaded " & LocalDBCount & " Trips to the Master Database!")
'Set db = CurrentDb
'Set rs = db.OpenRecordset("TransferQuery", dbOpenDynaset)
SetArchBit = "Update rs SET [UploadedFlag] = True"
With rsNotUploaded
.MoveFirst
Do Until .EOF
.Edit
![Trip.UploadedFlag].Value = True
.Update
.MoveNext
Loop
.Close
End With
FindRecordCount ("NotUploaded")
ElseIf LocalDBCount = 0 Then
MsgBox ("There is nothing to upload at this time.")
Else
MsgBox ("Something Went Wrong. You may not be connected to the District Network. Check your internet connection and try again.")
End If
End If
it is quite common that multi table queries are not editable - google something like 'access vba why cant i edit multi tables queries' and you will find plenty of examples. The general rule for forms is one table, one form, associated tables would be in subforms. In a form you can sometimes set the recordset type to 'dynaset (inconsistent updates)' to overcome the issue. You can also try this in a query.
Never tried it by opening a recordset in VBA but you could try using dbInconsistent instead of or as well as dbSeeChanges. Spend a bit of time to Google and understand the openrecordset parameters.
Note that you should be able to edit tables that have calculated fields, but not those fields. On the face of it, calculated fields seem like a good idea. In practice you often hit limitations - the calculations allowed are pretty basic, they can't be indexed and they can't be used in joins, the latter two having a strong detrimental impact on performance for larger datasets.
Thanks CJ... sorry I am not familiar with the dbInconsistent command but I did find by trial and error that if I simply removed the joins, I was able to iterate through the records set. However, I know Pat is right about the join. It was working before I updated the tables... even with the calculated field so I most likely got some field or pointer crossed.
