Opening Access, writing to certain cells and then saving and closing file.

[Sparky_500]

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

[jonh]

Databases and spreadsheets are very different things. Databases use tables, fields and rows not cells, so the first thing you need to do is design and create a suitable table to hold the data.

Then you can connect to the database via the database engine and execute SQL to modify the tables and their data. e.g.



with CreateObject("ADODB.Connection")
.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myaccessfile.accdb;"
.open
.execute "insert into table1 (field1,field2, ...etc) values ('" & dtmCreated & "','" & SmartTags("UPTIME") & "' ... etc)"
.close
end with

[Sparky_500]

Thanks for the reply Jonh, based on your feedback my code now looks like this. I should also mention i am using access2007-2013 formatting if that matters at all. This code is being inserted on a runtime application on a computer, and the access file is located on the same computer. When I execute this code however, nothing seems to write to the access data base. The database is already created and ready to go. Is there anything i am missing in this code ?


With CreateObject("ADODB.Connection")
.connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Administrator\Desktop\HMI\ExcelRevs\PLCDataBase.accdb;"
.open
'.execute "insert into table6 PLCDataT1(field2,field3,field4) values ('" & SmartTags("OEEShift")& ",'" & 2 & "','" & SmartTags("UPTIME") & ")"
.execute "insert into table6 (field2,field3,field4,field5,field6,field7,field8,field9,field10,field11,fi eld12,field13,field14,field15,field16,field17) values ('" & SmartTags("OEE_Date")& "','" & SmartTags("OEE_Time") & "','" & SmartTags("OEEShift")& ",'" & 2 & "','" & SmartTags("UPTIME")& "','" & SmartTags("DOWNTIME")& "','" & SmartTags("#ofDowntime")& "','" & SmartTags("OEERecipe")& "','" & SmartTags("OEEPPTime")& "','" & SmartTags("OEEUPTime")& "','" & SmartTags("OEEDOWNTIME")& "','" & SmartTags("OEEGoodParts")& "','" & SmartTags("OEEBadParts")& "','" & SmartTags("OEETotalParts")& "','" & SmartTags("OEE%")& "','" & SmartTags("CycleTimeAverages_AverageTime") & ")"
.close
End With

[jonh]

Do you get an error?
I think you missed the end quote - assuming it's needed (text datatype see below).

I have no idea what PLC is, but assuming you can debug your code, instead of executing the sql directly, assign it to a string.

Then you can either add a line of code that prints that string to the immediate window

dim sql as string
sql = "blah blah blah"
debug.print sql
.execute sql

or set a breakpoint at .execute and type ?sql in the immediate window

If you can't debug the code you would need to write out to a text file instead.

Both above methods print the sql that has been built. Copy the sql into a new query in the database and see if runs.

Things to bear in mind...

You only wrap text values in quotes. 'hello world'
Dates and times are wrapped in hashes # and must be in US format m/d/y. #4/10/2017#
Don't wrap numeric values with anything.

[Sparky_500]

No, there is no error, the VBA editor with in the PLC (Programmable logic controller) has a "check syntax" feature which shows no errors with-in the code. Its also interesting that you say that only text values are wrapped with quotes. In the code
SmartTags("OEE_Time") this is essentially a variable from my PLC which holds an integer, in fact all of my "SmartTags" hold integers and no text. So you're saying all of these should instead have nothing around them and the date to have # # around it ?

[jonh]

No, the SmartTags syntax will be correct. But when you are building your SQL statement you need to format values according to the datatype of the field in the Access table.

But if all of your values are integers then you don't need any quotes around them.

.execute "insert into table6 (field2,field3,field4,...) values (" & SmartTags("OEE_Date") & "," & SmartTags("OEE_Time") & "," & SmartTags("OEEShift") & "," & 2 & "," & ...