Backend data, temp tables using VBA to stop corruption

[redbull]

------------BackStory--------------


I have a front and back end like most people. The backend in my case is a table that collects button clicks from the users on the front end, so everytime a button is clicked on the form an append query goes thru that updates the backend. It has worked very well for quite a long time. Until recently... It started corrupting a ton, no matter how many times I replaced or restored it, it would corrupt again. I started looking for alternatives.

My initial thought was to just change the back end into our SQL server, this quickly came out as a horrible idea because our network is pathetic and we have vpn employees who would notice a 45-60 second delay in the front end while the query was going thru.

I then looked to google forms, I thought with the power of google how could I go wrong. However, the webform that google generates changes the IE elements so my code was not consistent and resulted in a loss of data. Original post is here https://www.accessforums.net/program...ple-47995.html

So now I am looking into making another access database to house all the data. I'm trying to avoid the corruptions I was facing so I started reading online and found that using temp tables is a good way to increase speed and reduce the chance of corruption.
----------Problem------------

I have close to 200 people running append queries to an access table on the shared drive. It corrupts often, I don't know why.

I'm thinking it could be our slow network speed, making the append queries take a long time... causing the jet engine to do something wrong...

----------Actual Question----------

I want to create a temporary table in a separate database using SQL in vba, I want the table name to be unique (machine id). I don't mind having these tables in the same backend file, but I'm kind of confused by something. If I have all the users creating a unique table when they start the application and writing their data in there, how do I push the data into the main table? is that something that gets coded into the front end? Or would I have to do a nightly routine on the backend file to move all the temp data? Or would the temp table be created in the front end, so the data is gathered quickly.. but at what point does that data move to the backend database? I'm so entirely confused at this point.

Does anyone have any code doing this already, anything you can share?

[June7]

If link to backend is the issue, then multiple individual user tables located there probably won't help. "Temp" tables belong in front end. Then have to run a procedure that transfers the data to the 'main' table in backend. Again, if speed of data transfer is cause, probably doesn't solve corruption issue.

[pbaldy]

I suppose you could go the temp table way. If you do, the movement of data is on you, Access isn't going to move it for you. You could create a process that ran every so often, or as the person exited the database, or whatever is appropriate to your situation. In general I think you'd want to "push" from the front ends rather than "pull" from the back end.

I actually would have thought the SQL Server (SS) route would have worked. I have Access/SQL Server applications working in a VPN environment, and the performance is fine (though not near as many users). Are these append queries inserting single records each time, or? I might look at SS again, but using either a pass-through query or an ADO command object with a SS stored procedure to do the insert. Basically you want to minimize what has to go "over the wire", and the connection to the back end. With an Access back end, you have 200 people maintaining constant connections. If you go to SS, you can make it so the user isn't always connected.

[redbull]

Are these append queries inserting single records each time, or?

Yea, for every button click, we get a record of it, we record name, function and date/time. We get around 50 to 60 thousand clicks in a month, so it gets used lol.

Yea on any modern network it should work, however our systems are diesel fueled.

Now I'm trying to figure out what is worse, having the front ends store temp tables that pushes to the backend when the computer powers on in the morning, or just rapid fire like I did before and hoping the backend does not corrupt. I think I'm going to go with rapid fire, time to gamble.

Thanks for the help everyone.

[pbaldy]

200 concurrent users is pushing Access to its limits, perhaps beyond. The state of the network makes me lean even more to SQL Server. Access can corrupt if the connection between front and back drops, which is why it's not recommended on a wireless network. That could be the cause of your corruption.

[redbull]

you know, you could bring me some good news every once in awhile... /s

Yea, I'm thinking of something else now... I'm wondering if there is a way for me to determine what region they are from... IF I can do that, then I can cut the traffic into three different backends... and if I can somehow check to see if they are connecting over vpn, then let them store data locally until the next time they sign on the network...

Now.. if there is only a way to do this without having user involvement, then I will be a happy camper.

[pbaldy]

I thought I did bring the good news that SQL Server might still be a viable and even preferable alternative?

How would you determine their region? I suppose if knowing their network name or something like that would tell you, you could run a relinking routine to link to a given back end. I've heard of trapping for the back end being unavailable and if not using a local table, but haven't used it myself.

[redbull]

Alright, This is what I came up with. I finally found a difference between the 3 regions. The homedrive, so that is what I am using. What this code does is first, deletes the linked table to the backend. Then starts figuring out where to rebuild the link.

So we have 3 regions, each region uses a different homedrive letter. But then again we also have remote employees who work from home. Thankfully those people all use the same vpn client, and I can check for the running process. Here is the code. Lastly, if somehow a user avoids all of these statements, it will create a link to a misfit database as backup. Thoughts? Comments?

Sub BackendLink()

Dim strTerminateThis As String
Dim objWMIcimv2 As Object, objProcess As Object, objList As Object
Dim intError As Integer
Dim oTD As DAO.Database


If TableExists("UseLog") = True Then 
DoCmd.DeleteObject acTable, "UseLog"
End If


Home = Environ("homedrive")




''''''''''''''''''''''''''''''''''Determine if user is on VPN...
strTerminateThis = "vpnui.exe"  ' The cisco vpn client process, if its there they are remote.
Set objWMIcimv2 = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
Set objList = objWMIcimv2.ExecQuery("select * from win32_process where name='" & strTerminateThis & "'")
For Each objProcess In objList
       DoCmd.TransferDatabase acLink, "Microsoft Access", "\\networkpath\BackendDataRemotes.accdb", acTable, "UseLog", "UseLog"
             Exit Sub
            If intError <> 0 Then Exit For
Next
Set objWMIcimv2 = Nothing
Set objList = Nothing
Set objProcess = Nothing
'''''''''''''''''''''''''''''''''
If Home = "P:" Then
  DoCmd.TransferDatabase acLink, "Microsoft Access", "\\networkpath\BackendDataWest.accdb", acTable, "UseLog", "UseLog"
  Exit Sub
End If


If Home = "U:" Then
  DoCmd.TransferDatabase acLink, "Microsoft Access", "\\networkpathBackendDataCentral.accdb", acTable, "UseLog", "UseLog"
  Exit Sub
End If


If Home = "W:" Then
  DoCmd.TransferDatabase acLink, "Microsoft Access", "\\networkpath\BackendDataEast.accdb", acTable, "UseLog", "UseLog"
  Exit Sub
End If


 DoCmd.TransferDatabase acLink, "Microsoft Access", "\\networkpath\BackendDataMisfits.accdb", acTable, "UseLog", "UseLog"


End Sub


Function TableExists(strTableName As String) As Boolean
On Error Resume Next
TableExists = IsObject(CurrentDb.TableDefs(strTableName))
End Function

[pbaldy]

It isn't the way I'd have tried first, but if it resolves your issues then go for it. It is innovative.