Solved Migrate Form to 365, sharepoint or web?

[Garren.Shannon]

You have missed the vital bit - the debug.print of a filled out version of strSQL.
How are your time fields stored?

I thought that is what the Debug.print strSQL statement did? It right above the MsgBox() statement and that works so not sure why I am not seeing what the CurrentDB.Execute command is getting but it should be this command. BTW... I added #'s around the time fields. They are actually a date/time type, just formatted to show just time. Maybe I need to modify the output to show full date time?

You are making this harder for yourself than it needs to be. Why not just use an append query? Select the rows you want from the drivers table and append them to the permanent table. ONE query handles all the rows. Plus running an append query for each row, especially one you build with VBA is as inefficient as it gets. If you want to use an append query, run ONE that does all the rows at once. If you want to use a VBA loop (always slower), at least open a recordset and use .AddNew to add each row.

 

[Pat Hartman]

Do you see the error in the debug print? It is at the end.

Also, please post the text you want us to examine as TEXT, not as a picture.

 

[Garren.Shannon]

You are making this harder for yourself than it needs to be. Why not just use an append query? Select the rows you want from the drivers table and append them to the permanent table. ONE query handles all the rows. Plus running an append query for each row, especially one you build with VBA is as inefficient as it gets. If you want to use an append query, run ONE that does all the rows at once. If you want to use a VBA loop (always slower), at least open a recordset and use .AddNew to add each row.

Do you see the error in the debug print? It is at the end.

Also, please post the text you want us to examine as TEXT, not as a picture.

Tried to Pat but the Msgbox (which is the only print out I see) does not allow me to highlight and copy text. I totally understand why one would want the actual output.

WHERE should I be seeing the debug.print output? I don't see it anywhere.

Also, I took your lead on the append query. It came out like this.

INSERT INTO dbo_Trip ( [T-Date], VehicleID, DriverID, CodeID, [Pre-Insp], [Post-Insp], DepartTime, ReturnTime, OD_Depart, OD_Return, [Basic-Count], [Sped-Count], [HS-Count], [Walk-Count], MaxCount, [Desc] )
SELECT Trip.[T-Date], Trip.VehicleID, Trip.DriverID, Trip.CodeID, Trip.[Pre-Insp], Trip.[Post-Insp], Trip.DepartTime, Trip.ReturnTime, Trip.OD_Depart, Trip.OD_Return, Trip.[Basic-Count], Trip.[Sped-Count], Trip.[HS-Count], Trip.[Walk-Count], Trip.MaxCount, Trip.Desc
FROM Trip
WHERE (((Trip.UploadedFlag)=False));

This seems like what I am looking to do and your right, it sure is easier to run it this way.

 

[Garren.Shannon]

Tried to Pat but the Msgbox (which is the only print out I see) does not allow me to highlight and copy text. I totally understand why one would want the actual output.

WHERE should I be seeing the debug.print output? I don't see it anywhere.

Also, I took your lead on the append query. It came out like this.

INSERT INTO dbo_Trip ( [T-Date], VehicleID, DriverID, CodeID, [Pre-Insp], [Post-Insp], DepartTime, ReturnTime, OD_Depart, OD_Return, [Basic-Count], [Sped-Count], [HS-Count], [Walk-Count], MaxCount, [Desc] )
SELECT Trip.[T-Date], Trip.VehicleID, Trip.DriverID, Trip.CodeID, Trip.[Pre-Insp], Trip.[Post-Insp], Trip.DepartTime, Trip.ReturnTime, Trip.OD_Depart, Trip.OD_Return, Trip.[Basic-Count], Trip.[Sped-Count], Trip.[HS-Count], Trip.[Walk-Count], Trip.MaxCount, Trip.Desc
FROM Trip
WHERE (((Trip.UploadedFlag)=False));

This seems like what I am looking to do and your right, it sure is easier to run it this way.

OK... I figured out how to turn on the VBA "Locals" window and found what the strSQL string was passing... well, mostly. The window does not show the entire content of the string. There must be a better way to display the actual command and I simply don't know it.

Here's what I can copy out of the window.

"INSERT INTO dbo_Trips ([T-Date],VehicleID,DriverID,CodeID,[Pre-Insp],[Post-Insp],DepartTime,ReturnTime,OD_Depart,OD_Return,[Basic-Count],[Sped-Count],[HS-Count],[Walk-Count],MaxCount,Desc) values (#9/27/2021#,8,9,1,True,True,#7:45:00 AM#,#8:40:00 AM#,75"

Sorry this is such a pain guys. I think the approach I need to do is use the Append Query... but I am super curious as to why the heck this does not work. From what I can see, it should be working.

 

[Pat Hartman]

Debug.print prints to the debug window. That is text. Just copy/paste.
Based on your first paste of the query - The reason your code isn't working is because you are creating a string and that string contains strings which you are not delimiting property.

Insert Into MyTable (txtfld, numfld1, numfld2, dtfld) Values("text value", 3, 45, #12/12/2022#);

Now look at the string printed by your debug.print. Do you see that you are missing the delimiters?

You have merged the select DAO method with the append query. If you really like writing code, then you can use your population statements but instead, use them to populate a second recordset. That way you don't have to worry about delimiters. you are not using a string as the intermediary. You are going from tbl1 fields directly to tbl2 fields. What you are doing with your code is taking the fields from a table and putting the values into a string and then you are running the string as an append query. This is the worst possible method.

mth1 - recordset1 to recordset2 using the DAO open recordset command and a second open recordset with an .AddNew followed by the value statements is OK but still inefficient since VBA loops are by their nature less efficient than append queries.

mth2 - your method. Recordset1 to a string, then running an append query. Has the overhead of the VBA loop PLUS has the overhead of forcing Access to create and run a separate query for every single record. When you create queries in VBA, you pass them to the query entine as a string so it needs to start from scratch by creating an execution plan. It must do this every time the query runs so if you append a 1000 records, it has to create the execution plan 1000 times and run 1000 queries

mth3 - the select/append method (what I recommended). Does not use VBA except to run the single query. The query selects specific records from tbl1 and appends them to tbl2. This query can use arguments so it knows what set of records to select. AND if you need to supply a FK because you are appending child records, you can also pass in an argument to provide the necessary FK. Execution plans are calculated and saved the first time a querydef is executed which is one of the reasons they are more efficient than embedded SQL.

INSERT INTO yourtargettable( fld1, FKfield, fld3)
SELECT yourtable.fld1, Forms!yourform!theFKfield, yourtable.fld3
FROM yourtable
WHERE yourtable.somefield = Forms!yourform!someFKfield

 

[Garren.Shannon]

Debug.print prints to the debug window. That is text. Just copy/paste.
Based on your first paste of the query - The reason your code isn't working is because you are creating a string and that string contains strings which you are not delimiting property.

Insert Into MyTable (txtfld, numfld1, numfld2, dtfld) Values("text value", 3, 45, #12/12/2022#);

Now look at the string printed by your debug.print. Do you see that you are missing the delimiters?

You have merged the select DAO method with the append query. If you really like writing code, then you can use your population statements but instead, use them to populate a second recordset. That way you don't have to worry about delimiters. you are not using a string as the intermediary. You are going from tbl1 fields directly to tbl2 fields. What you are doing with your code is taking the fields from a table and putting the values into a string and then you are running the string as an append query. This is the worst possible method.

mth1 - recordset1 to recordset2 using the DAO open recordset command and a second open recordset with an .AddNew followed by the value statements is OK but still inefficient since VBA loops are by their nature less efficient than append queries.

mth2 - your method. Recordset1 to a string, then running an append query. Has the overhead of the VBA loop PLUS has the overhead of forcing Access to create and run a separate query for every single record. When you create queries in VBA, you pass them to the query entine as a string so it needs to start from scratch by creating an execution plan. It must do this every time the query runs so if you append a 1000 records, it has to create the execution plan 1000 times and run 1000 queries

mth3 - the select/append method (what I recommended). Does not use VBA except to run the single query. The query selects specific records from tbl1 and appends them to tbl2. This query can use arguments so it knows what set of records to select. AND if you need to supply a FK because you are appending child records, you can also pass in an argument to provide the necessary FK. Execution plans are calculated and saved the first time a querydef is executed which is one of the reasons they are more efficient than embedded SQL.

INSERT INTO yourtargettable( fld1, FKfield, fld3)
SELECT yourtable.fld1, Forms!yourform!theFKfield, yourtable.fld3
FROM yourtable
WHERE yourtable.somefield = Forms!yourform!someFKfield

Thanks Pat... you are awesome. Seriously. I did FINALLY find the debug window. I realized I have issues with delimiting the fields... I just couldn't see it. I dropped that approach and followed your suggestions. It working now. I adding some checks and balances now but I used the append query approach which works great and is far more efficient. Then I modify the uploaded flag (I know, bad name) and it cruised through. I also added some feedback for the user.

I do a count prior to upload and post upload to make sure the append happened correctly. I think there is a better way to capture the dbFailOnError. I am sure Access has a Try/Catch command but I am not sure how to implement that in this case. If the append fails... like the bus driver tries to upload when not connected to the network at the station... the dbFailOnError will dump right? I think I need to figure out how to address that situation.

Don't laugh at my over-use of the msgbox but here is the current code.

Private Sub Upload_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim SetArchBit As String
Dim LocalDBCount As Long
Dim SQLDBCount As Long

    FindRecordCount ("dbo_Trip")
    SQLDBCount = UploadCount
    
    FindRecordCount ("TransferQuery")
    LocalDBCount = UploadCount

    MsgBox ("Local =" & LocalDBCount & " SQL = " & SQLDBCount)   

    CurrentDb.Execute ("AppendTripsQuery"), dbFailOnError   

    FindRecordCount ("dbo_Trip")

    MsgBox ("Master DB Count Prior to upload = " & SQLDBCount & " and after upload = " & UploadCount) 

    ' Check that the SQL database has increased from prior to append. implies the append worked.

    If UploadCount > SQLDBCount Then
        MsgBox ("You Successfully Uploaded " & LocalDBCount & " Trips to the Master Database!")
        ' iterate though the local DB and set the Uploaded flag to yes
        Set db = CurrentDb
        Set rs = db.OpenRecordset("TransferQuery", dbOpenDynaset)
        SetArchBit = "Update rs SET [UploadedFlag] = True"

        With rs
            .MoveFirst
            Do Until .EOF
                .Edit
                    ![Trip.UploadedFlag].Value = True
                .Update
                .MoveNext
            Loop
            .Close
        End With

        ' reset the front page records that need to be synchronized now that the records have been uploaded to the master.

        FindRecordCount ("TransferQuery")   

        If UploadCount >= 20 Then
            Me.Syncronize.ForeColor = RGB(255, 255, 0)
            'Me.Syncronize.Caption = "Records to upload = " + CStr(UploadCount)
        Else
            Me.Syncronize.ForeColor = RGB(0, 0, 0)
            'Me.Syncronize.Caption = "Records to upload = " + CStr(UploadCount)
        End If
        Me.Syncronize.Caption = "Records to upload = " + CStr(UploadCount)
    ElseIf LocalDBCount = 0 Then
        ' if the LocalDBCount was already zero - tell the user nothing to do here.

        MsgBox ("There is nothing to upload at this time.")
    Else
        ' If it didn't need to upload but the upload count wasn't zero - something happened.
        MsgBox ("Something Went Wrong. You may not be connected to the District Network. Check your internet connection and try again.")
    End If
End Sub

 

[Pat Hartman]

You're welcome but you're not done yet

Then I modify the uploaded flag (I know, bad name)

Not a bad name per se but the flag isn't giving you any useful information. A date/time has MUCH more informational value. I go even further. I create a log table. I log the name of the file along with its location and date/time appended. When I save the record, it generates a BatchID. I then use that value to populate a field named uploadBatchID in my append query. That gives me a clean way to identify all the rows that were uploaded from any given file. Therefore, if I need to backout the upload, I can do it cleanly and then run the upload again with a new batchID. Don't forget to update the log table to mark the old batch as removed.

I am sure Access has a Try/Catch command

It does not. That is what dbFailOnError is for.

Updating each row of the uploaded data using a VBA loop is as inefficient as it can get.

Rather than loading data using the Transfer method, use that to LINK to the import file. Then use an append query to load the data. This gives you the ability to add the BatchID at the same time and NOT require a second pass through the data. And also allows you to manipulate fields to format them if necessary.

PS - i fixed your code. It is ever so much easier to read if you bother to use the code tool.

 

[Garren.Shannon]

You're welcome but you're not done yet


Not a bad name per se but the flag isn't giving you any useful information. A date/time has MUCH more informational value. I go even further. I create a log table. I log the name of the file along with its location and date/time appended. When I save the record, it generates a BatchID. I then use that value to populate a field named uploadBatchID in my append query. That gives me a clean way to identify all the rows that were uploaded from any given file. Therefore, if I need to backout the upload, I can do it cleanly and then run the upload again with a new batchID. Don't forget to update the log table to mark the old batch as removed.


It does not. That is what dbFailOnError is for.

Updating each row of the uploaded data using a VBA loop is as inefficient as it can get.

Rather than loading data using the Transfer method, use that to LINK to the import file. Then use an append query to load the data. This gives you the ability to add the BatchID at the same time and NOT require a second pass through the data. And also allows you to manipulate fields to format them if necessary.

PS - i fixed your code. It is ever so much easier to read if you bother to use the code tool.

Again Pat... thanks for the guidance with this old novice. What I have right now is an Administrative App and a Driver App. The admin side works directly with the SQL ridership tables. The driver version has a stand-alone table set because they will often be disconnected from the network but still may need to enter data. The driver tables will need to be uploaded/copied to the master and that's what the flag does. It provides a historical record and a way for the drivers to upload when they can. Maybe not today... maybe not tomorrow... but at least once a week.

Now, I am appending straight from the driver trip table to the SQL table. There may be several drivers doing this at the same time so I hope this won't be a challenge for the SQl server. I am not truncating the driver table when I do this so I know it will grow.

Just so I understand what your saying... you are suggesting when I append to the SQL table, I also dump the driver table to a log file? Or are you saying I would add a uploadBatchID field to the SQL table, and append the log file with the unique uploadBatchID?

The upload is actually a single append command. The loop I am using is modifying the local driver table... flipping the archive bit as it were. I don't know any other way to modify all the records in the transfer query.

And thanks for the pro tip on the code display. I didn't even see it here until I went to look for it. It does not have a specific option for Visual Basic or Access. What do you use to display? General?

 

[Pat Hartman]

What I have been suggesting all along is that the two apps be disconnected. The driver's app exports data. The primary app imports data. Having multiple drivers importing at the same time would not stress SQL server but I think it is an inferior design pattern.

VBA loops are the slowest possible way of updating a table. Action queries are ALWAYS more efficient and faster. So, exporting to a .csv and then running an update query that sets a BatchID or even a date/time data type is far superior to a VBA loop that updates a low-information flag. This process gives you both a backup of the driver's data AND an audit trail.

Think about how you could possibly audit your current process. The answer is you can't. I am trying to show you a way to connect the two apps that provides an audit trail. How are you ever going to know if a driver doesn't upload?

 

[Garren.Shannon]

What I have been suggesting all along is that the two apps be disconnected. The driver's app exports data. The primary app imports data. Having multiple drivers importing at the same time would not stress SQL server but I think it is an inferior design pattern.

VBA loops are the slowest possible way of updating a table. Action queries are ALWAYS more efficient and faster. So, exporting to a .csv and then running an update query that sets a BatchID or even a date/time data type is far superior to a VBA loop that updates a low-information flag. This process gives you both a backup of the driver's data AND an audit trail.

Think about how you could possibly audit your current process. The answer is you can't. I am trying to show you a way to connect the two apps that provides an audit trail. How are you ever going to know if a driver doesn't upload?

You make a great point... it has been one of my concerns. If the driver does not do their part, how will the admin team ever know. OK... I will look to implementing this approach. Though, as I think about it, if the driver is not doing the sync part, how would file transfer be accomplished? It would still require some form of action on the part of the driver... maybe that is more a management challenge than a programming challenge.

That said, I will start work on adding an BatchID to this system.

Quick side question. When you add an unbound object to a form like a text box, it has no type to it right? It is essentially null correct? If you bind it to a field of number type, does the bound object now act as a data type number?

 

[Pat Hartman]

The driver still has to initiate the sync. He just does it by exporting a file and then either uploading it or emailing it. Email is easier than a LAN connection and that works great with the method I suggested which is another advantage. So, if you keep a Batch table on the driver's PC, it generates the next available batch number. You might not want to use an autonumber for this because you do want it to be sequential. That way, on the receiving end, you know that if you get batch 1 and then batch 3, you know something is missing. Which leads me to my favorite high school senior day prank. The senior class got three tiny pigs and put signs on them labeled 1, 2, and 4 and let them loose. The staff looked all day for #3 and never found it

Unbound text boxes are not specifically typed UNLESS you apply a format. If you set the format to short date for example, the textbox will now only accept valid dates. With bound textboxes, the data type and validation rules dictate what is allowed. If the data type is some type of number, then only numbers are allowed. If it is defined as small int, that limits the size of the number you can enter. Unless I have a specific need to provide a default, I set most defaults to Null, even for numbers because 0 has a meaning and I usually want to know that the user entered 0 rather than forgot to enter anything. For text data types, it is important to set AllowZLS to No, especially if the field is going to be marked as required. So, if you define LastName as required but forget about changing the default setting for AllowZLS from Yes to No, then the user can enter a character and then backspace and tab out of the control. That leaves a ZLS which is not the same as null and is perfectly valid and considered a character for purposes of the required flag.

 

[Garren.Shannon]

Again, thank you Pat... your input is so very valuable to me and I really do appreciate it... and your time.

I think I am finally getting your point. BatchID's are making sense. As I am thinking through this deeper, I think I need to craft a batch ID that is unique to the device the upload is coming from. That way, I have a way not just to see if say batch 8 & 9 are missing from the 10 uploads... but also what machine the 8 and 9 might be found on. Or come up with some other way to delineate where the missing data might be located when a fault is noted.

I am thinking a simple table to record batch ID events that can be incremented with two fields BatchID and Date would help track local update events. Any suggestions on how to craft a unique Batch ID?

 

[Pat Hartman]

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.

 

[Garren.Shannon]

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?

 

[Minty]

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.

 

[Garren.Shannon]

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?

 

[Pat Hartman]

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.

Here's a sample of how to create a custom ID.

Custom Sequence Numbers

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

www.access-programmers.co.uk

 

[GPGeorge]

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.

Here's a sample of how to create a custom ID.

Custom Sequence Numbers

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

www.access-programmers.co.uk

For this purpose, consider generating the value as a GUID.

 

[Pat Hartman]

All you need is the ability to ensure uniqueness in the master. GUIDs are awkward to work with because they are so long.

 

[Garren.Shannon]

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.

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