Sync excel database with user-form without shares file

ZamfirescuT

New Member
Joined
Jan 30, 2022
Messages
1
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Hello,
Need your help because I did not find any solution yet to my issue. I have a userform used by multiple users. This form input data into a sheet which will save a db file on the network. This syncs based on last local change. If the db file on the network is newer, then it sync into the workbook containing the userform, otherwise does nothing.
I am quite new into VBA, and do not find any solution for the following issue : the code works great, except if from the source file (the db file) sone rows are deleted, it syncs only the number of rows that I have in the source file. Even if the db is newer and sync should happen, it does, but not for the extra rows (if any) in the source file.
Because I have to clean data weekly, if on the db file I reduce the number of rows, the destination file on sync will not delete the extra rows from the destination file(so users will not have the updated data). Bellow is the code for the sync :

Sub SyncToDatabase()
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
DbFile = Worksheets("Form").Range("AN2").Value
LastLocalChange = Worksheets("Form").Range("AM2").Value
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(DbFile)
If objFile.DateLastModified < LastLocalChange Then 'Local Change was made, update Database
Kill (DbFile) 'Delete current database version
Worksheets("DataSh").Visible = True
ThisWorkbook.Worksheets("DataSh").Copy
ActiveWorkbook.SaveAs DbFile, FileFormat:=51
ActiveWorkbook.Close False
Worksheets("DataSh").Visible = xlVeryHidden
Call cmdrefresh_Click
Call cmdReset_Click
Call StartTimer
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End If
End Sub

Sub SyncFromDatabase()
On Error Resume Next
ThisWorkbook.Activate
LastLocalChange = Worksheets("Form").Range("AM2").Value
DbFile = Worksheets("Form").Range("AN2").Value
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile(DbFile)
If objFile.DateLastModified > LastLocalChange Then 'Database change made, update LocalDatabase
Worksheets("DataSh").Visible = True
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Worksheets("Form").Range("AN2").Value & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes:IMEX=0"";"
objRecordset.Open "Select * FROM [DataSh$], objConnection"
Worksheets("DataSh").Range("A2").CopyFromRecordset objRecordset
objRecordset.Close
objConnection.Close
'Worksheets("DataSh").Visible = xlVeryHidden
WIPPS.Hide
WIPPS.lstdisplay.RowSource = "WipPS"
WIPPS.Show
End If
End Sub

Thank you!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top