Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
record a macro to update pivot table | Excel Discussion (Misc queries) | |||
Update access DB record when finished running macro | Excel Programming | |||
Update current record in Access using Excel | Excel Programming | |||
Update A Record In Access. Not Adding But Editing | Excel Programming | |||
How can I update an MS Access table record from within Execl works | Excel Discussion (Misc queries) |