Code amendment needed for a workbook sharing macro (vba)

[kelly mort]

Option Explicit
Dim objFSO, objFile As Object
Dim objConnection, objRecordset As Object
Dim LastLocalChange, LastDbUpdate As Date
Dim DbFile, ConnString As String

Sub SyncToDatabase()
    DbFile = Sheet1.Range("M5").Value 'Customer Database Location
    LastLocalChange = Sheet1.Range("B12").Value
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    On Error GoTo FileMissing
    Set objFile = objFSO.GetFile(DbFile)
    If objFile.DateLastModified < LastLocalChange Then 'Local Change was made, update Database
        Kill (DbFile) 'Delete the current database version
        ThisWorkbook.Sheets("CustDb").Copy
        ActiveWorkbook.SaveAs DbFile, FileFormat:=51
        ActiveWorkbook.Close False
    End If
    Exit Sub
FileMissing:
    MsgBox "Please browse for the database file", vbInformation, ""
    BrowseForFile
End Sub

Sub SyncFromDatabase()
    LastLocalChange = Sheet1.Range("B12").Value
    DbFile = Sheet1.Range("M5").Value 'Customer Database Location
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    On Error GoTo FileMissing
    Set objFile = objFSO.GetFile(DbFile)
    If objFile.DateLastModified > LastLocalChange Then 'Database Change was made, update Local Database
        'Check Last Database Update
        Sheet2.Range("A2:G9999").ClearContents 'Clear existing data
        On Error Resume Next
        Set objConnection = CreateObject("ADODB.Connection")
        Set objRecordset = CreateObject("ADODB.Recordset")
        objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _
        Sheet1.Range("M5").Value & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=0"";"
        objRecordset.Open "Select * FROM [CustDb$]", objConnection
        
        Sheet2.Range("A2").CopyFromRecordset objRecordset
        
        objRecordset.Close
        objConnection.Close
        On Error GoTo 0
    End If

    Exit Sub
FileMissing:
    MsgBox "Please browse for the database file", vbInformation, ""
    BrowseForFile
End Sub

This code is currently used to sync data to and from the database workbook (CustData). The CustData has just one sheet "CustDb" and if the local data is fresher than the data in the database, we update the database and vice versa.

I do not entirely understand what the various lines are doing - making it very difficult for me to do any vital amendments. I believe that if I able to explain exactly what I want to do or achieve, some great mind here could get it fixed ASAP for me.

So instead of having just one sheet in the database, I will be having multiple sheets. Say "CustDb1", "CustDb2" and so on. And the same sheets will be available in the local file as well.

My primary goal here is to be able to sync data to and from the corresponding sheets between the two workbooks. That is, if I am interacting with the "CustDb1" sheet from the local file (workbook), then the data should be synced to and/or from the "CustDb1" sheet in the database.

The database file is a macro-free workbook. I hope someone could fix this for me. Thanks in advance.

 

[kelly mort]

The SharedWorkbook.xlsm file has two sheets while the CustData.xlsx file has just one sheet.

B12 contains the last changes made date and M5 contains the link to the CustData.xlsx file as the database

 

[xenou]

I don't see any particular use made of the last changes made date. Do you know of any reason why that exists?

Never mind - I see it. Everything stops if the "database" has changes. Seems like that is used for reverse changes. Don't know how you really handle this if you have changes on both sides at the same time ...

 

[kelly mort]

Don't know how you really handle this if you have changes on both sides at the same time ...

That's a very valid concern.

But at the moment I think it's very rare to have same timestamp at both sides.
Maybe that will be worried about later - but not now.

Better still if there is a way to avoid the timestamp and get the data to be synced to and from the database, I am open to that as well.

Thanks

 

[xenou]

What's happening on the other end ... who is using the "database file"?

 

[kelly mort]

What's happening on the other end ... who is using the "database file"?

No one will use it. It is only there for data to be synced to and from.

 

[xenou]

Well, let's put the question another way...who is syncing to and from this file? If its just the workbook we are working with now then its really just a "backup". Are you really saying that nobody and nothing is using the other file?

 

[kelly mort]

Oh okay I get it now.

users on the network will be using it.

 

[kelly mort]

Bump.

I Still need help with this please.

Can someone help me out?