ExcelBanter - Update Access Table Record

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Update Access Table Record (https://www.excelbanter.com/excel-programming/399465-update-access-table-record.html)

Update Access Table Record

I am trying to use ADO to update existing records in access. I have a unique
cell, TransferLine that will correspond to the field by the same name in
access. How can I then update that record in access? Also, I want to be
able to remove a record in the db too. Thanks

Update Access Table Record

Need a little more detail to provide more tailored code, but this
should get you going in the right direction. Keep in mind, there is
no error handling built into this.
Sub updateDB()
Dim conn As Object, sql As String
Dim databaseFullName As String
databaseFullName = "R:\ACC.mdb"
sql = "UPDATE tblInstructorsTest SET " & _
"tblInstructorsTest.InstructorID = '" & _
Range("A1").Text & "' WHERE " & _
"(((tblInstructorsTest.InstructorID)='WRIG')); "
Set conn = CreateObject("ADODB.Connection")
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & databaseFullName
conn.Execute sql
conn.Close
Set conn = Nothing
End Sub

Sub deleteDB()
Dim conn As Object, sql As String
Dim databaseFullName As String
databaseFullName = "R:\ACC.mdb"
sql = "Delete tblInstructorsTest.InstructorID " & _
"FROM tblInstructorsTest WHERE " & _
"(((tblInstructorsTest.InstructorID)='" & _
Range("A1").Text & "'));"
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & databaseFullName
conn.Execute sql
conn.Close
Set conn = Nothing
End Sub

Also, things such as the table name should really be set to a
variable. If you can provide more detailed information about what you
need done (ie. database name, database location, table name, etc,
etc), then I may be able to provide a more tailored code.
Mike H. wrote:
I am trying to use ADO to update existing records in access. I have a unique
cell, TransferLine that will correspond to the field by the same name in
access. How can I then update that record in access? Also, I want to be
able to remove a record in the db too. Thanks

Update Access Table Record

Location: N:\Accounting\Audit\Mike
mdb name: InvTransfers.mdb
table: Transfers
fields in table:
TransferLine
FromLocation
ToLocation
TransferDate
Item
Quantity
Cost
Extension
TransferredBy
TransferID

Also another table, which is the "header table" for the details in the above
file:
name: TransferHeader
Fields:
TransferredBy
TransferID
TransferDate
FromLocation
ToLocation
NumberRecordsTrans
Status

I would need to change the status of the header file, along with the number
of records trans. In the first file, I would need to change either, item,
quantity or perhaps delete the entire record.

Hope This Helps! (you help me).

Update Access Table Record

Little error in the deleteDB code. You can delete the following line
as it is not needed.
Set rs = CreateObject("ADODB.Recordset")

JW wrote:
Need a little more detail to provide more tailored code, but this
should get you going in the right direction. Keep in mind, there is
no error handling built into this.
Sub updateDB()
Dim conn As Object, sql As String
Dim databaseFullName As String
databaseFullName = "R:\ACC.mdb"
sql = "UPDATE tblInstructorsTest SET " & _
"tblInstructorsTest.InstructorID = '" & _
Range("A1").Text & "' WHERE " & _
"(((tblInstructorsTest.InstructorID)='WRIG')); "
Set conn = CreateObject("ADODB.Connection")
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & databaseFullName
conn.Execute sql
conn.Close
Set conn = Nothing
End Sub

Sub deleteDB()
Dim conn As Object, sql As String
Dim databaseFullName As String
databaseFullName = "R:\ACC.mdb"
sql = "Delete tblInstructorsTest.InstructorID " & _
"FROM tblInstructorsTest WHERE " & _
"(((tblInstructorsTest.InstructorID)='" & _
Range("A1").Text & "'));"
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & databaseFullName
conn.Execute sql
conn.Close
Set conn = Nothing
End Sub

Also, things such as the table name should really be set to a
variable. If you can provide more detailed information about what you
need done (ie. database name, database location, table name, etc,
etc), then I may be able to provide a more tailored code.
Mike H. wrote:
I am trying to use ADO to update existing records in access. I have a unique
cell, TransferLine that will correspond to the field by the same name in
access. How can I then update that record in access? Also, I want to be
able to remove a record in the db too. Thanks

Update Access Table Record