Using Google Form entries as Backend Database, couple small issues...

[redbull]

Alright, I have a large amount of people clicking on an app I wrote some years ago. The backend database has always been in access, but due to the large volumn of people and REALLY POOR vpn connections.. I am trying to use google forms to start tracking activity. It seems to be working great, I just have a couple problems.

First, let me show you the form here... feel free to try it out, Once the issue has been marked solved I am going to edit this thread to take the live form URL out.

 Dim oIe As InternetExplorer  Dim User, Pass As String
  Dim strUser, strPass As String
  Dim Uname As String
  Dim ctlCurrentControl As control
  Dim strControlName As String
  Dim db As Database
  
  Set db = CurrentDb
  Uname = Environ("username")
  Set ctlCurrentControl = Screen.ActiveControl
  strControlName = ctlCurrentControl.Name
  
Set oIe = FindIE("Google FOrm URL")


If oIe Is Nothing Then
Set oIe = Login("Google form url", True)
End If


Call SleepIE(oIe)






oIe.Document.all.Item("entry_896438412").Value = Uname


oIe.Document.all.Item("entry.1614432791").Value = strControlName


oIe.Document.all.Item("entry.777398801").Value = Now


oIe.Document.all.Item("entry.925840023").Value = db.Properties("AppTitle").Value


oIe.Document.all.Item("submit").Click


Call SleepIE(oIe)


oIe.Navigate ("Google form url")




End Sub

Okay, the first problem I am having is on the initial load of the form I receive a crazy error that states

Webpage error details

User Agent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/4.0; QAM; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET CLR 1.1.4322; .NET4.0C; .NET4.0E; InfoPath.3; Tablet PC 2.0)
Timestamp: Fri, 31 Oct 2014 14:09:25 UTC




Message: Object doesn't support this property or method
Line: 167
Char: 114
Code: 0
URI: https://docs.google.com/static/forms/client/js/3191719111-formviewer_prd.js

And the second thing I have not figured out is how to capture the Application Title in access. I could just hardcode the text I want, but I plan on rolling this out to 5 or 6 different applications, so to have it dynamic would be the best solution. (Fixxed, found this "CurrentDb.Properties("AppTitle")" and it works just fine!

[June7]

Not seeing a url so you have this all solved?

[redbull]

Not all solved, I have a small group of 20 users pumping data into it now.. I really had high hopes for this thing.. but I don't know if the code is solid enough yet.... If I can get it working 100% I will create a write up on here about how to utilize google forms for a backend database.

[redbull]

Well, This code...

oIe.Document.all.Item("entry_896438412").Value = UnameoIe.Document.all.Item("entry.1614432791").Value = strControlName
oIe.Document.all.Item("entry.777398801").Value = Now
oIe.Document.all.Item("entry.925840023").Value = db.Properties("AppTitle").Value

Isn't going to work, the text box names/id's are not reliable enough. The only unique thing I can find on these text boxes are
aria-label, but I don't know how to separate by them...

[redbull]

So, I've come to the conclusion that google forms can't be used for a backend database...I've found a way to enter data into the the form even if the ID & Name have changed...

oIe.Document.all.tags("input").Item(0).Value = Uname

oIe.Document.all.tags("input").Item(1).Value = strControlName


oIe.Document.all.tags("input").Item(3).Value = Date


oIe.Document.all.tags("input").Item(2).Value = Format(Time, "hh:mm")


oIe.Document.all.tags("input").Item(4).Value = CurrentDb.Properties("AppTitle")


oIe.Document.all.Item("submit").Click

However, the success rate is really low. Kind of bums me out that this could not be a solution, as google docs has a ton of different ways to distribute and sort data. I'm going to mark this thread as solved, but in all reality it's just not a viable option.