Solved Migrate Form to 365, sharepoint or web?

[Garren.Shannon]

Hey all,

Circling back to this. I decided to create a sub-DB with just the trip database but linked with a dbo to the SQL stored master DB. I added an archive flag (Boolean y/n) field to the local trip DB. MY thoughts now are to add a button to the start page to allow the drivers to copy/append the unflagged (no) records to the dbo master, then change the archive flag to yes. I figure this will leave a control database on each drivers Surface Go that I can go back to if there were a problem.

Should I do this in VBA or try a macro?

 

[Garren.Shannon]

As to how that flag gets set, there are a ton of ways. But your comment makes me wonder what it is that you are discussing. I would have suggested that to set the flag once the upload worked, you would set this putative flag where the flag is FALSE and the PK is in the table to which the upload occurred.

However, on further review, I would wonder whether I misunderstood your focus. Is there ever a time when you would NOT download data, and under what circumstances should that happen. Because remember that when you use Access to "model" your business, you have to look at the real-world procedures to decide when to do something specific. Otherwise, you aren't modeling, you are muddling.

Pat... I think I understand what your asking so let me clarify. The master DB resides on our SQL server. Each driver will have a Surface Go with Access on it. They will have a stripped down version of the program with all the DB's and just a form for entry. On occasion with they are at the transportation center (and on the wireless network) they will click a button (that I provide) triggering a copy/append from the local trips table to the dbo_trips table and, assuming the append worked, then set the archive flag to yes for all selected records.

Does that make sense?

I still need to figure out how best to approach this... not sure I can do it via forms. I think the best way would be via code and using a local query. Just not sure how to verify the transfer worked prior to changing the flags.

 

[Pat Hartman]

I would export the modified data and transmit the exported file to be processed. Then create a batch job that processes all the exported files and apply them to the central database. Don't have each driver do this by himself.

 

[Isaac]

to allow the drivers to copy/append the unflagged (no) records to the dbo master

Wait, so you mean all this time the drivers' devices actually DO have connectivity to the SQL database? (I assume that's what you mean by "dbo master" although not really sure what that means)

If they do have connectivity to SQL Server, then you have no issues or problems in the first place, right?!

Confused...

 

[Garren.Shannon]

- code to re-link back end once the person is in range or connected
- code to run an Insert query (insert to linked table)
- code to run an Update query (update local table, same records updated)

or if you want to get more granular , open a recordset and insert each record individually and update it individually there is no chance that way of an uncertain fraction of records being updated / inserted before a failure of some kind.

that's about as specific as I'll get. the forum is a place to ask narrow questions on specific things, one question/thread at a time.
thus is how you balance your effort with forum help as well as how we all maximize a useful repository of specific labelled help for posterity

Hey Isaac,

Thanks for the direction on this. I copied the original DB (prior to migrating it to our SQL server), then removed all forms except the one the drivers will use. This leaves the core of the data individualized as it were. Then I added a dbo link to the SQL trips table. The drivers will be able to connect whenever they are in the office (daily) and I plan to add an "Upload" button so they can start an upload on demand. I don't know if trying to code an automatic upload would be the better way to go so for now, manually when they press the button.

On the local DB table, I added an archive flag and will set the flag as each record is inserted.

Wait, so you mean all this time the drivers' devices actually DO have connectivity to the SQL database? (I assume that's what you mean by "dbo master" although not really sure what that means)

If they do have connectivity to SQL Server, then you have no issues or problems in the first place, right?!

Confused...

Sorry for the confusion Isaac. They sporadically have access. The will have their Surface Go's in hand when they leave the station... they will have it when they return from their routes. The Go's will reside at the station over night for charging and will be carried and mounted in the bus during trips. The will NOT have access when they are on their routes - typically when they are out of cell service on county roads.

Sorry... I am an infant in the Access adult world. DBO is what I see when I link to a table in the SQL so that's what I am called it... but I think its just a linked DB right?

I think the DB drivers will access needs to be somewhat standalone so they can still put in data out on the road and not be required to be in wireless contact to do so. I suspect in normal operations, they will add multiple trips during the day or week and every few days, they will actually click the upload button.

Below is what I see. the file named with dbo prefix are the SQL copies of the tables. The others are local. The local trips table now has an added field called archive (Boolean) with the default set to false.

So to wrap this thread up, it turns out neither 365, sharepoint or web was the answer.

Hope that helps clarify our situation.

 

[Isaac]

when you link from SQL to Access, Access automatically names the tables as being the remote's version of: schema_tablename (after also corrected any illegal names, because names can be in SQL that aren't allowed in Access, to a small degree). those are just local access names at that point - you can right click and rename them at any time, most people do.

as long as the drivers have any routine access to SQL, they can just be instructed to only click the 'upload' button once back at the station, or something like that.

yes, they standalone to input most of their data, then relink when have connectivity. at least they do sometimes.

you could also go totally rogue and do something really different - like if this data is relatively simple, you could have them input it on google sheets (which can be connected to a nifty mobile app for next to no cost using AppSheet), and then you could code other solutions for the IN-office personnel to get the data from google sheets into sql. (I do this by maintaining an Excel file, which is connected to a Google Sheets download/import, which is also linked to an Access database, which them uses an Insert query to insert into a linked SQL table).

many ways to skin a cat, but web rarely is one of them when Access is involved.

 

[Garren.Shannon]

Wait, so you mean all this time the drivers' devices actually DO have connectivity to the SQL database? (I assume that's what you mean by "dbo master" although not really sure what that means)

If they do have connectivity to SQL Server, then you have no issues or problems in the first place, right?!

Confused...

when you link from SQL to Access, Access automatically names the tables as being the remote's version of: schema_tablename (after also corrected any illegal names, because names can be in SQL that aren't allowed in Access, to a small degree). those are just local access names at that point - you can right click and rename them at any time, most people do.

as long as the drivers have any routine access to SQL, they can just be instructed to only click the 'upload' button once back at the station, or something like that.

yes, they standalone to input most of their data, then relink when have connectivity. at least they do sometimes.

you could also go totally rogue and do something really different - like if this data is relatively simple, you could have them input it on google sheets (which can be connected to a nifty mobile app for next to no cost using AppSheet), and then you could code other solutions for the IN-office personnel to get the data from google sheets into sql. (I do this by maintaining an Excel file, which is connected to a Google Sheets download/import, which is also linked to an Access database, which them uses an Insert query to insert into a linked SQL table).

many ways to skin a cat, but web rarely is one of them when Access is involved.

Thanks again Isaac... and thank you for the Recordset primer here in the Info Sharing Center. VERY helpful. Question... I have a transferquery that filters only records with the archive field not set to yes. I am opening that record set and will do while <> eof. I am also opening the linked table as a record set. For each record in the transfer, how do I copy the current record without the extra field? And now that I think about it, how do I ensure that a second driver isn't locking the SQL? Sorry if this is getting long-winded.

 

[Isaac]

The part you need to remove is "I am also opening the linked table as a record set".
Only open the local table.
For each loop iteration, do something like:

CurrentDb.Execute "insert into dbo_tablename (col1_Number, col2_Text, col3_Date) values (" & rs.fields("col1").value & ",'" & rs.fields("col2").value & "',#" & format(rs.fields("col3").value,"mm/dd/yyyy hh:mm:ss") & "#)",dbfailonerror

 

[Isaac]

Or separate it like this for easier troubleshooting until you're used to it:

dim strSQL as string
strSQL = "Currentdb.execute..............." etc
debug.print strSQL (so you can see the result)
Currentdb.execute strSQL, dbfailonerror

You want the final LITERAL executes string to look like this, if you were inserting into 3 columns of those 3 types respectively:

insert into tablename (col1_number, col2_text, col3_date) values (2,'sometext',#01/29/2022 09:15:15#)

 

[Pat Hartman]

You will have better control and recovery options if you simply give the driver an export option that exports the file to a server-side folder and then marks the data as archived. I always use a dateflag and often a batch number for a process like this. That allows me to tax the export file as expDriver_1234_Batch_222_Date_20221207.csv

That way, with a consistently formatted file name, I can be sure that the export happened AND that the file got transferred and then imported. If something goes wrong, I can back out the transferred data. Then go back and export again, etc.

This method also doesn't tie up the driver. He can always export the file and if he can't upload immediately, he can try to email it.

You then create a macro that runs a function. That allows you to create a batch job if you want to. The batch job can be scheduled using Windows scheduler. It starts Access passing in the name of your db plus the name of the macro. The macro runs the function, sends you an email and then closes the database. The code in the function reads through the files in the upload folder. It logs them and creates an error message if a file is missing or duplicated. You have to figure out how to handle these things. Then it copies the file to the archived folder and deletes it from the upload folder. At the end of the process, the upload folder is empty and ready for tomorrow's workload. You also have a pretty good audit trail so you can tell what happened and when.

 

[Garren.Shannon]

Thanks Isaac... so very helpful. If I have a date field, do I have to format the value or can I just do rs.fields("T-Date").value

Or separate it like this for easier troubleshooting until you're used to it:

dim strSQL as string
strSQL = "Currentdb.execute..............." etc
debug.print strSQL (so you can see the result)
Currentdb.execute strSQL, dbfailonerror

You want the final LITERAL executes string to look like this, if you were inserting into 3 columns of those 3 types respectively:

insert into tablename (col1_number, col2_text, col3_date) values (2,'sometext',#01/29/2022 09:15:15#)

this is hard to read but I crafted the insert string. Looks like this and I get the output below. Just learning to crawl here so I am not taking any actual actions here. It looks like I am on the correct record and the data is accurate. Now, if I understand you, I need to add a # in front date value. What about the time values?

Once I have the insert command looking correct, I can add a do while, insert, capture on dbo error and flip the archive Boolean if it succeeded.

 

[Isaac]

Good question, I never use Time by itself, I believe it needs an octothorpe as well. Not just in front, on both sides.
Not sure about your hyphenated column names, they might need brackets, can't remember.

The safest thing is to format the date the way I showed you. if it's only time then try format(value,"HH:MM:SS")
No, do not just use the recordset value by itself, use the format function to get it in the format I showed.
That's my personal recommendation, although you might get away with some other method, that's always been the safest and best for me.

add dao. in front of Database and Recordset. You can get away with not but it involves a prioritization and an implicit operation in Access and since you don't know that stuff yet (and in my opinion always regardless), be explicit - it's dao.database as opposed to ado or another

 

[GPGeorge]

Good question, I never use Time by itself, I believe it needs an octothorpe as well. Not just in front, on both sides.
Not sure about your hyphenated column names, they might need brackets, can't remember.

The safest thing is to format the date the way I showed you. if it's only time then try format(value,"HH:MM:SS")
No, do not just use the recordset value by itself, use the format function to get it in the format I showed.
That's my personal recommendation, although you might get away with some other method, that's always been the safest and best for me.

add dao. in front of Database and Recordset. You can get away with not but it involves a prioritization and an implicit operation in Access and since you don't know that stuff yet (and in my opinion always regardless), be explicit - it's dao.database as opposed to ado or another

IMO, explicit is much more desirable than implicit in the world of databases.

It's probably the opposite in the world of exposing personal relationships on social media, though.

 

[Isaac]

Amen .... and, Amen.

I don't use any social media - I invite my family to send me all the pictures by text, email, and Google Photos sharing (or iCloud sharing) that they jolly well want and it works just as good as it did in 1995 or 2010. I do that because I know my own weaknesses and my enthusiasm for arguing things, I know social media would drag me down and it would beat me before I figured out how to manage it and probably long after.

Tik tok and Instagram have done a lot more harm to the younger generation than guns have ever dreamed of doing - all in the space of 5-7 years.
Destroyed a generation of girls' self esteem - sad stuff.

Ok I'm done and everyone accept my apology for that - on to work, we're getting close to Friday everyone - take heart!!!

 

[Garren.Shannon]

Good question, I never use Time by itself, I believe it needs an octothorpe as well. Not just in front, on both sides.
Not sure about your hyphenated column names, they might need brackets, can't remember.

The safest thing is to format the date the way I showed you. if it's only time then try format(value,"HH:MM:SS")
No, do not just use the recordset value by itself, use the format function to get it in the format I showed.
That's my personal recommendation, although you might get away with some other method, that's always been the safest and best for me.

add dao. in front of Database and Recordset. You can get away with not but it involves a prioritization and an implicit operation in Access and since you don't know that stuff yet (and in my opinion always regardless), be explicit - it's dao.database as opposed to ado or another

Thanks Isaac, I am following your lead. You are the natives in this Access world, I am (currently) the immigrant.

I spent a few hours last night trying to get this to work. With such a long Insert command, I have something in there that isn't coming across right. I don't thing brackets would hurt either way so I will add that.

While I am at it, my SQL tables have a password... Simple but effective enough to cause a challenge that I don't understand. To test access, I added this. "Set rs2 = db.OpenRecordset("dbo_Trips", "MS Access;PWD=password")" But access throws an error.

I thought that might be the issue with the Insert command - Debug says it's an error 128... though I can find little help on that error.

I got to tell you guys, I have not been this deep into programming since I was 25. I am enjoying it immensely.

As an aside, I have several Social Media accounts. I park them but don't understand the fascination. As an adult with ADHD, I see them as a serious time sync and time is my most precious commodity. I don't want to give it away to some thing so useless and I totally agree with your perspective on the harm. Though, I do see hope for the future as my kids (who are adults) are turning away from it as well. But, as someone who works in the public school sector, I see more challenges than benefits.

 

[Garren.Shannon]

Thanks Isaac, I am following your lead. You are the natives in this Access world, I am (currently) the immigrant.

I spent a few hours last night trying to get this to work. With such a long Insert command, I have something in there that isn't coming across right. I don't thing brackets would hurt either way so I will add that.

While I am at it, my SQL tables have a password... Simple but effective enough to cause a challenge that I don't understand. To test access, I added this. "Set rs2 = db.OpenRecordset("dbo_Trips", "MS Access;PWD=password")" But access throws an error.

I thought that might be the issue with the Insert command - Debug says it's an error 128... though I can find little help on that error.

I got to tell you guys, I have not been this deep into programming since I was 25. I am enjoying it immensely.

As an aside, I have several Social Media accounts. I park them but don't understand the fascination. As an adult with ADHD, I see them as a serious time sync and time is my most precious commodity. I don't want to give it away to some thing so useless and I totally agree with your perspective on the harm. Though, I do see hope for the future as my kids (who are adults) are turning away from it as well. But, as someone who works in the public school sector, I see more challenges than benefits.

BTW... not sure why but Debug.Print strSQL does not output anything. I must not understand how to use that function.

 

[Minty]

Your password should be stored in the connection string for the table, assuming it is already linked you shouldn't need to provide it to open a recordset on the same table..

 

[Garren.Shannon]

OK... the Insert Into command is simply not working. I can't see what I am missing. I print the strSQL string out in a message box and it shows accurate data so it is pulling from the local query correctly. I have 16 fields and 16 sets of matching data. I am getting a syntax error in "INSERT INTO statement" as a response. I need a better pair of eyes on this.

-----------------------------
Private Sub Upload_Click()

Dim db As DAO.Database
Dim db2 As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
Set rs = db.OpenRecordset("TransferQuery")
rs.MoveFirst

strSQL = "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 (" _
& "#" & rs.Fields("T-Date").Value & "#" & "," _
& rs.Fields("VehicleID").Value & "," _
& rs.Fields("DriverID").Value & "," _
& rs.Fields("CodeID").Value & "," _
& rs.Fields("Pre-Insp").Value & "," _
& rs.Fields("Post-Insp").Value & "," _
& rs.Fields("DepartTime").Value & "," _
& rs.Fields("ReturnTime").Value & "," _
& rs.Fields("OD_Depart").Value & "," _
& rs.Fields("OD_Return").Value & "," _
& rs.Fields("Basic-Count").Value & "," _
& rs.Fields("Sped-Count").Value & "," _
& rs.Fields("HS-Count").Value & "," _
& rs.Fields("Walk-Count").Value & "," _
& rs.Fields("MaxCount").Value & ",'" & rs.Fields("Desc").Value & "')"
Debug.Print strSQL
MsgBox (strSQL)
CurrentDb.Execute (strSQL), dbFailOnError

 

[Minty]

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

 

[Pat Hartman]

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.