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.
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)???
Yes. That is a common usage but the syntax is different if your querydefs have arguments.
Code:
Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim rs as DAO.Recordset
Set db = CurrentDB()
Set qd = db.QueryDefs!yourqueryname
qd.yourparm1 = Me.Somefield
qd.yourparm2 = Me.SomeOtherField
Set rs = qd.OpenRecordset
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?
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.
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.
strSQL = "UPDATE YourTable SET BatchID = " & Me.BatchID & " WHERE BatchID Is Null"
There are lots of examples of update queries. Replace "YourTable" with your table name. This updates all the rows in the table where the BatchID is null which we would assume to be all the rows that have not yet been updated.
Then you would export all the data with the BatchID you are working with.
To run the update query:
Rich (BB code):
Dim db As DAO.Database
Dim strSQL as String
Set db = CurrentDB()
strSQL = ...
db.Execute strSQL
