kelly mort
Well-known Member
- Joined
- Apr 10, 2017
- Messages
- 2,169
- Office Version
- 2016
- Platform
- Windows
The variables
This part of the code sync to the database:
This part of the code sync from the database:
I have tested the code and it is working very great and I want to implement it into my projects. But I am having a few doubts that I want to clear first before making the big move.
From the test example used, the file on the server was named custData (macro-free workbook) and had just one sheet named custDb. My question now is that can I have multiple sheets in the custData workbook and be able to direct my data to specified sheets or that is beyond the scope of what VBA can do in terms of this kind of jobs?
Thanks for the time to read this and making the effort to assist me.
Code:
Option Explicit
Dim objFSO, objFile As Object
Dim objConnection, objRecordset As Object
Dim LastLocalChange, LastDbUpdate As Date
Dim DbFile, ConnString As String
This part of the code sync to the database:
Code:
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, "SlimSoft Systems"
BrowseForFile
End Sub
This part of the code sync from the database:
Code:
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, "SlimSoft Systems"
BrowseForFile
End Sub
I have tested the code and it is working very great and I want to implement it into my projects. But I am having a few doubts that I want to clear first before making the big move.
From the test example used, the file on the server was named custData (macro-free workbook) and had just one sheet named custDb. My question now is that can I have multiple sheets in the custData workbook and be able to direct my data to specified sheets or that is beyond the scope of what VBA can do in terms of this kind of jobs?
Thanks for the time to read this and making the effort to assist me.