Sparky_500
04-07-2017, 05:37 PM
Hello all, let me just say to start i'm very new to doing any work with MS access and it has been years since I've done any serious VBA coding, so this topic may seem very simple to some. For my current application, I am using a machine (PLC) to write to an access data base. My PLC has a built in VBA writer, I have accomplished this with Excel but i'd like to write directly to access now. In a sense I need to:
Open a specific existing Access file path
Open one of multiple tables
Write to multiple cells with in that open table
save and close
quit application
The following is example VBA code to write to an excel file that i have been using successfully doing as an example of what I am trying to accomplish. The following code comes from TIA (Siemens PLC VBA editor) so it may look a little different from standard VBA.
'--------------------------------------
Sub Shift1UpDown()
Dim objExcel, strExcelPath, strSaveAsPath, objSheet, dtmCreated
dtmCreated = Now
strSaveAsPath = "C:\Users\Administrator\Desktop\HMI\ExcelRevs\MasterBook02-17-2017.xlsx"
strExcelPath = "C:\Users\Administrator\Desktop\HMI\ExcelRevs\MasterBook02-17-2017.xlsx"
' Open specified spreadsheet and select the second worksheet.
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open strExcelPath
Set objSheet = objExcel.ActiveWorkbook.Worksheets(2)
objSheet.Cells(2,1).Value = dtmCreated
objSheet.Cells(2,2).Value = SmartTags("UPTIME")
objSheet.Cells(2,3).Value = SmartTags("DOWNTIME")
objSheet.cells(2,4).value = SmartTags("#ofDowntime")
' Save and quit.
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
End Sub
'--------------------------------------
This code essentially opens a specific excel file, sheet number 2, and writes to a few cells, then saves and closes the work.
I need something exactly like this, but for access. The file is now called PLCDatabase.accdb , rather than that " masterbook "
Again, hopefully this is a very simple piece of code and someone could assist me with it.
Thanks
Open a specific existing Access file path
Open one of multiple tables
Write to multiple cells with in that open table
save and close
quit application
The following is example VBA code to write to an excel file that i have been using successfully doing as an example of what I am trying to accomplish. The following code comes from TIA (Siemens PLC VBA editor) so it may look a little different from standard VBA.
'--------------------------------------
Sub Shift1UpDown()
Dim objExcel, strExcelPath, strSaveAsPath, objSheet, dtmCreated
dtmCreated = Now
strSaveAsPath = "C:\Users\Administrator\Desktop\HMI\ExcelRevs\MasterBook02-17-2017.xlsx"
strExcelPath = "C:\Users\Administrator\Desktop\HMI\ExcelRevs\MasterBook02-17-2017.xlsx"
' Open specified spreadsheet and select the second worksheet.
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open strExcelPath
Set objSheet = objExcel.ActiveWorkbook.Worksheets(2)
objSheet.Cells(2,1).Value = dtmCreated
objSheet.Cells(2,2).Value = SmartTags("UPTIME")
objSheet.Cells(2,3).Value = SmartTags("DOWNTIME")
objSheet.cells(2,4).value = SmartTags("#ofDowntime")
' Save and quit.
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
End Sub
'--------------------------------------
This code essentially opens a specific excel file, sheet number 2, and writes to a few cells, then saves and closes the work.
I need something exactly like this, but for access. The file is now called PLCDatabase.accdb , rather than that " masterbook "
Again, hopefully this is a very simple piece of code and someone could assist me with it.
Thanks